A look at Hypothetical Indexes in SQL Server

It is a common scenario in SQL Server that you want to create indexes on columns in tables with voluminous data to cut down the query processing times. But there is no guarantee that the index we are planning to create would be used by the Query optimizer when the query is run against the table. Also, we must keep in mind that any index needs additional resources for its maintenance like disk space, additional overheads incurred with read, updates and delete to the base table data. Additionally creating an index on a table with millions of rows could take much longer time than we think and it might not be a good idea to do it on a production server when the server is under heavy usage. So is there any way we can create an index without encountering all the issues listed above and check if it will be used the Query optimizer when a query is run? Yes, this is where Hypothetical indexes come into the picture.
When Hypothetical Indexes are created, it contains the metadata of the index and creates a statistic associated with the index, but does not create the actual index itself.
Suppose I have a table dbo.CustomerSales with the following schema:

You could see that a query against this table filtering upon the OrderDate column leads to a clustered index scan on the primary key as we do not have any indexes on the OrderDate column.

 
Let’s create a hypothetical index on this column in the table.
CREATE INDEX IDX_OrderDate_CustomerSales ON dbo.CustomerSales(OrderDate) WITH STATISTICS_ONLY = -1;
As we can see there is an optional clause added to the routine index definition statement WITH STATISTICS_ONLY = -1. This is an undocumented feature which creates statistics for the index but the index is not considered created/used by the query optimizer unless query is run in AUTOPILOT mode.
To force a query to be run in AUTOPILOT mode, we need to make use of command DBCC AUTOPILOT along with SET AUTOPILOT ON command. Before that, let’s retrieve some index specific information that will be needed for our scenario.

 
Let’s again run the SELECT query filtering on the OrderDate column in AUTOPILOT mode. Please note that I’m setting database id, object id and index id values in the DBCC AUTOPILOT command to enable the usage of index in AUTO PILOT mode.

 
We can now see that Query analyzer shows an estimated execution plan that considers all indexes including the hypothetical indexes turned on by the DBCC AUTOPILOT command.
Since we are dealing with an undocumented piece of code here, I would advise caution against using this code in any production environment unless it is backed by Microsoft in the near future.
Hope you have learnt few things about general index usage and Hypothetical indexes by reading this article.