Included indexes are a type of non-clustered index in SQL Server that include columns that are not part of the index key. These included columns allow the index to cover more queries and reduce the need for additional index lookups.
Here are a few ways to determine included indexes in SQL Server:
Using sys.index_columns: You can check the included columns of an index by querying the sys.index_columns system catalog view. The included columns will have a value of 1 in the “is_included_column” column.
SELECT name, column_id, is_included_column
FROM sys.index_columns
WHERE object_id = OBJECT_ID('mytable')
Using sys.indexes: You can also check the included columns of an index by querying the sys.indexes system catalog view. The included columns will have a non-NULL value in the “filter_definition” column.
SELECT name, filter_definition
FROM sys.indexes
WHERE object_id = OBJECT_ID('mytable')
Using sp_helpindex: You can use the sp_helpindex stored procedure to check the included columns of an index. The included columns will be listed after the key columns in the “Columns” column.
EXEC sp_helpindex 'mytable'Using SQL Server Management Studio: You can also check the included columns of an index by opening the table in SQL Server Management Studio, right-clicking on the index, and selecting “Design”. The included columns will be listed under the “Included Columns” section.
It’s worth noting that, included columns are only used when the indexed columns are not enough to cover a query. So, it’s important to monitor the query performance and adjust the indexes accordingly.
