Covering indexes in SQL Server are powerful tools that can significantly improve query performance by covering all the columns required by a query. This means the index includes all the columns referenced in the query, either as key columns or as included columns, so the query can be resolved without accessing the table or clustered index. In this blog, we will explore how to list all covering indexes and the columns they cover in a SQL Server database.

Understanding Covering Indexes

A covering index is an index that includes all the columns needed to satisfy a particular query. By having a covering index, SQL Server can retrieve the necessary data directly from the index without having to access the base table, which can greatly enhance query performance.

Listing Covering Indexes and Columns

To list all covering indexes and the columns they cover, including the included columns, you can use SQL Server’s system catalog views. The relevant views include sys.indexes, sys.index_columns, sys.columns, and sys.tables.

Here is a detailed query to achieve this:

WITH KeyColumns AS (
    SELECT 
        i.object_id,
        i.index_id,
        t.name AS TableName,
        i.name AS IndexName,
        i.type_desc AS IndexType,
        i.is_unique AS IsUnique,
        i.is_primary_key AS IsPrimaryKey,
        STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns
    FROM 
        sys.indexes AS i
        INNER JOIN sys.tables AS t
            ON i.object_id = t.object_id
        LEFT JOIN sys.index_columns AS ic
            ON i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND ic.is_included_column = 0
        LEFT JOIN sys.columns AS c
            ON ic.object_id = c.object_id
            AND ic.column_id = c.column_id
    WHERE 
        i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
    GROUP BY 
        i.object_id, i.index_id, t.name, i.name, i.type_desc, i.is_unique, i.is_primary_key
),
IncludedColumns AS (
    SELECT 
        i.object_id,
        i.index_id,
        STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.index_column_id) AS IncludedColumns
    FROM 
        sys.indexes AS i
        LEFT JOIN sys.index_columns AS ic
            ON i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND ic.is_included_column = 1
        LEFT JOIN sys.columns AS c
            ON ic.object_id = c.object_id
            AND ic.column_id = c.column_id
    WHERE 
        i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
    GROUP BY 
        i.object_id, i.index_id
)
SELECT 
    kc.TableName,
    kc.IndexName,
    kc.IndexType,
    kc.IsUnique,
    kc.IsPrimaryKey,
    kc.KeyColumns,
    ic.IncludedColumns
FROM 
    KeyColumns AS kc
    LEFT JOIN IncludedColumns AS ic
        ON kc.object_id = ic.object_id
        AND kc.index_id = ic.index_id
ORDER BY 
    kc.TableName, kc.IndexName;

Using SQL Server Management Studio (SSMS)

If you prefer a graphical interface, SQL Server Management Studio (SSMS) provides a way to view indexes and their columns:

1. Open SSMS and connect to your database.
2. Expand the database in the Object Explorer.
3. Navigate to the “Tables” folder and expand it.
4. Expand the specific table you are interested in.
5. Expand the “Indexes” folder under the table to see all indexes.
6. Right-click on an index and select “Properties”.
7. In the Index Properties dialog, go to the “Included Columns” page to see the included columns.

Conclusion

Listing all covering indexes and the columns they cover in a SQL Server database can be accomplished through querying system catalog views or using SQL Server Management Studio. Understanding and managing covering indexes are crucial for optimizing query performance and ensuring efficient data retrieval.

For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.