Learn more about SQL Server tools

   
   






























































Latest from MSSQLTips














Improve SQL Server Performance with Covering Index Enhancements

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



> > Next Free Webcast - SQL Server Database Security and Compliance


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





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools


Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



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




 
Sponsor Information







 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.