Have you ever noticed a difference in the indexes shown in SQL Server Management Studio (SSMS) compared to the ones queried using T-SQL? This discrepancy might have left you wondering about the reason behind it. In this blog post, we will explore the concept of hypothetical indexes in SQL Server and shed light on this interesting phenomenon.

Firstly, it’s important to note that hypothetical indexes are not a new feature for performance improvement in the SQL Server Engine. They are used for costing evaluation of query plans in conjunction with commands like “DBCC AUTOPILOT” and “SET AUTOPILOT ON”. However, they are not physically stored in any filegroup or file, making them invisible in SSMS’s Object Explorer.

To better understand hypothetical indexes, let’s create a sample table and index using the following script:

USE MASTER
GO

IF DB_ID('HypotheticalIndex') IS NOT NULL
BEGIN
    ALTER DATABASE HypotheticalIndex SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE HypotheticalIndex
END

CREATE DATABASE HypotheticalIndex
GO

USE HypotheticalIndex
GO

CREATE TABLE [Alumni] (
    [SSN] [INT] IDENTITY(1, 1) NOT NULL,
    [StudentName] [CHAR](200) NULL,
    [GradDate] [DATETIME] NULL
)
GO

SET NOCOUNT ON
GO

INSERT INTO Alumni (StudentName, GradDate)
VALUES (RAND() * 1000, DATEADD(dd, RAND() * 100, GETDATE()))
GO 60000

SET NOCOUNT OFF
GO

CREATE INDEX Idx_Alumni_GradDate ON Alumni (GradDate)

Now, let’s create a hypothetical index on the same column using the following command:

CREATE INDEX Alumni_hyp_1 ON Alumni (GradDate) WITH STATISTICS_ONLY = 1
GO

The keyword to note here is the undocumented extension “WITH STATISTICS_ONLY” which is available with the “CREATE INDEX” command. This extension allows us to create a hypothetical index that has metadata and associated statistics, but no physical storage.

To view the hypothetical index, we can query the “sys.indexes” catalog view:

SELECT name, index_id, type_desc, data_space_id, is_hypothetical
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('Alumni') AND type_desc <> 'HEAP'

By running the above query, you will notice that the hypothetical index has a data_space_id of zero, indicating that it has no physical storage.

Additionally, we can view the statistics associated with the hypothetical index using the “DBCC SHOW_STATISTICS” command:

DBCC SHOW_STATISTICS (Alumni, Alumni_hyp_1) WITH STAT_HEADER

Now that we understand how to create and view hypothetical indexes, let’s discuss their significance. Hypothetical indexes are primarily used by the Database Engine Tuning Advisor (DTA) to evaluate the cost of an index by creating them hypothetically. These indexes are typically named with the prefix “_dta_index”. However, it’s important to note that not all indexes with this prefix are hypothetical; we need to use the “is_hypothetical” column in the “sys.indexes” view to filter them.

If you encounter hypothetical indexes in your database and no longer require them, you can safely drop them using the “DROP INDEX” command.

In conclusion, understanding hypothetical indexes in SQL Server can help you make sense of the differences between the indexes shown in SSMS and those queried using T-SQL. These indexes play a crucial role in the evaluation of query plans and can be managed effectively once their purpose is understood.

Learning never stops when working with SQL Server, and capturing insights from tools like the DTA can lead to interesting discoveries. Stay curious and keep exploring!