solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Improve SQL Server Performance with Covering Index Enhancements

MSSQLTips author Edgewood Solutions By:   |   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:


Using Transact-SQL:

Next Steps



Last Update: 9/29/2006


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Comments and Feedback:
Wednesday, March 11, 2009 - 10:49:46 PM - arbalu Read The Tip

thanks for this post.  i found that it was very useful tips to me.

 balu

http://developerskb.blogspot.com



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
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.