SQL Index Include

SQL Index Include

The drawback of covered queries is that you can only include upto 16 columns or upto 900 bytes for an index key.

One solution to this problem is the INCLUDE keyword, which allows you to add up to 1023 non-key columns to the nonclustered index, helping you improve query performance by creating a covered index. These non-key columns are not stored at each level of the index, but instead are only found in the leaf level of the nonclustered index. The syntax for using INCLUDE with CREATE NONCLUSTERED INDEX is as follows:

CREATE NONCLUSTERED INDEX index_name
ON table_or_view_name ( column [ ASC | Desc ] [ , ...n ])
INCLUDE ( column [ ,... n ])

Whereas the first column list is for key index columns, the column list after INCLUDE is for non-key columns. [SQL Server 2008 Transact-SQL Recipes: A Problem-Solution Approach By Joseph Sack]

Why does this matter?, SQL Server stops working as soon as it has what it actually needs. So, if while traversing the index, it can find all the data that it needs without continuing on to the actual data row, then it won’t bother going to the data row (what would be the point?). By including a particular column in the index, you may "cover" a query that utilizes that particular index at the leaf level and save the I/O associated with using that index pointer to go to the data page.

Careful not to abuse this one! When you INCLUDE columns, you are enlarging the size of the leaf level of your index pages. That means fewer rows will fit per page, and, therefore, more I/O may be required to see the same number of rows. The result may be that your effort to speed up one query may slow down others. To quote an old film from the eighties, "Balance Danielson — balance!" Think about the effects on all parts of your system, not just the particular query you’re working on that moment. [Beginning Microsoft Sql Server 2008 Programming By Robert Vieira]