When creating non-clustered index there is a option for including columns to extend its functionality:
With including other columns we can create non-clustered indexes that cover more queries.
Included columns can be:
- datatypes not allowed as a index key
- not considered by the Database Engine when calculating the number of index key columns or index key size
Included columns are used if columns are not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause.
How to create it in Management Studio:
1. Choose the Table in Object Explorer where you want to create covered index
2. Right Click on Indexes Folder -> New index
3. New Index window opens.
4. Go under Included Columns tab and click Add ellipse button to choose the included columns
Real life Example
Lets use Person.Contact table from AdventureWorks database.
I wilI perform select on a simple query:
WHERE FirstName = ‘Gustavo’ AND LastName=’Achong’
Firstly the table will have only clustered index, the execution plan looks like this:
Which clearly says it has performed Clustered Index scan.
Next I will add Non-clustered index only covering FirstName and LastName:
After this execution plan changed and now we can see that non-clustered index is used but also Key Lookup operation is performed.
Finally, after adding the EmailAddress, Phone and Title columns in the Included Columns I got improvement, the Key Lookup operation is not present any more and Index Scan is changed into Index Seek.
Whit this the query is potentially much faster.
Downsides of Included Columns
- The downside of INCLUDE columns are increased disk-space need by non-clustered indexes.
- With creating larger indexes fewer rows can fit on a page which can lead to increasing disk I/O.
- Index maintenance is increased for data modifications, potentially hurting performance if non key columns are large and the database experiences a high level of data modifications.