Index with include

Included columns are useful because they can be built on data types which cannot be used as index keys. Furthermore, included columns are NOT counted towards the 900 byte limitation for index keys. So if you have two VARCHAR(500) columns which are commonly searched together you could build an index with one of them used as the key column and second one as the included column. You can include columns with any data type except TEXT, NTEXT and IMAGE. Computed columns can also be used as included columns.

Each index (whether clustered or non-clustered) can have up to 16 index keys; included columns are not counted towards this limit, although having indexes with this many keys would be a rare occurrence. SQL Server allows up-to 1023 included columns per non-clustered index. Regardless of how many included columns you add to the index it must have at least one key column.Since included columns are only stored on the leaf level of an index, such indexes are typically leaner.On the other hand, you should choose which columns are included in each index judiciously. SQL Server has to maintain indexes to reflect data changes resulting from INSERT, UPDATE and DELETE statements. Each key column and included column adds the overhead required for maintaining indexes.

The maximum number of the columns comprising the index is 16 and the maximum size of all non-variable length columns comprising the index is 900 bytes. If you include variable length columns in your index SQL Server will allow creation of the index that has a maximum key size greater than 900 bytes. However, if you exceed that limit through an INSERT or UPDATE statement, the statement will fail.

USE pubs  GO

DBCC SHOW_STATISTICS ('authors', 'aunmind')

Note

Key lookups occur when you have an index seek against a table, but your query requires additional columns that are not in that index. This causes SQL Server to have to go back and retrieve those extra columns. 

The SORT_IN_TEMPDB option directs the index creation processing to the tempdb database, instead of the database in use. This option may increase rebuild performance for large tables on a high activity environment, if the tempdb system database is located on a separate disk. Although the index creation time may decrease, the disk space requirements increase while using this option.

Note: Make sure a dedicated disk is allotted to the tempdb database and it has sufficient disk space. When using the SORT_IN_TEMPDB option, DBA’s usually calculate the disk space requirement while doing capacity planning.