Improve SQL Server Performance with Covering Index Enhancements

By: Edgewood Solutions   |   Read Comments (1)   |   Related Tips: More > Indexing

Problem
The concept of index creation has always been a tricky orchestration.  To maximize performance and indexes by the optimizer, queries should be covered by an index-that is, the index should include all columns requested in the query.  There are limitations to creating indexes that make covering queries difficult:

• A 900-byte size limit on indexes
• A maximum of 16 columns can be named in the index
• Columns with data types like nvarchar(max), text, and ntext cannot be used in indexes

Solution
A new type of index was developed in SQL Server 2005 that assists in covering queries: Indexes With Included Columns.  Indexes with Included Columns are nonclustered indexes that have the following benefits:

• Columns defined in the include statement, called non-key columns, are not counted in the number of columns by the Database Engine. 
• Columns that previously could not be used in queries, like nvarchar(max), can be included as a non-key column
• A maximum of 1023 additional columns can be used as non-key columns

As with traditional non-clustered indexes, the non-key columns are added to the leaf level of the index, meaning these indexes have the same affect on disk space, I/O (due to index maintenance), and cache efficiency.  This should be taken into account when contemplating index creation using included columns. 

How do I create an Index with Included Columns?

Using Microsoft SQL Server Management Studio

Navigate to the index to be modified.  Right-click the index and choose Properties:

Click on Included Columns:

Check the columns you wish to include and click OK:

Next Steps

• Review your queries when preparing to upgrade to SQL Server 2005
• Review your indexes used in queries to see if it makes sense to use Indexes with Included Columns
• Review the information on Index with Included Columns and Creating Indexes with Included Columns in SQL Server Books Online

About the author

Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code