Create Indexes with Included Columns

By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:

• They can be data types not allowed as index key columns.
• They are not considered by the Database Engine when calculating the number of index key columns or index key size.

An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

When an index contains all the columns referenced by a query it is typically referred to as covering the query.

An index with included columns can greatly improve query performance because all columns in the query are included in the index; The query optimizer can locate all columns values within the index without accessing table or clustered index resulting in fewer disk I/O operations.

Design Recommendations

• Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.
• Include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 32 key columns and a maximum index key size of 1,700 bytes (16 key columns and 900 bytes prior to SQL Server 2016 (13.x)). The Database Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

Limitations and Restrictions

• Nonkey columns can only be defined on nonclustered indexes.
• All data types except text, ntext, and image can be used as nonkey columns.
• Computed columns that are deterministic and either precise or imprecise can be nonkey columns. For more information, see Indexes on Computed Columns.
• Computed columns derived from image, ntext, and text data types can be nonkey columns as long as the computed column data type is allowed as a nonkey index column.
• Nonkey columns cannot be dropped from a table unless that table’s index is dropped first.
• Nonkey columns cannot be changed, except to do the following:
  • Change the nullability of the column from NOT NULL to NULL.
  • Increase the length of varchar, nvarchar, or varbinary columns.

Example:

-- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.   
-- index key column is PostalCode and the nonkey columns are  
-- AddressLine1, AddressLine2, City, and StateProvinceID.  
CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
GO  

CREATE NONCLUSTERED INDEX [idx_tbTable01_Col01_Col02] ON [dbo].[tbTable01]
(
	[Col01] ASC,
	[Col02] ASC
)
INCLUDE (Col03, Col04) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [fgGroup01]
GO

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-ver15

https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes

SQL Server Indexes with Included Columns