Improve SQL Server Performance with Covering Index Enhancements

By:   |   Updated: 2006-09-29   |   Comments (1)   |   Related: More > Indexing

   Free MSSQLTips whitepaper - "Understanding Windows Server Cluster Quorum Options"


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

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:

object explorer

Click on Included Columns:

included columns

Check the columns you wish to include and click OK:

postal code

Using Transact-SQL:

sql query
Next Steps

Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights

get scripts

next tip button

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

View all my tips

Article Last Updated: 2006-09-29

Comments For This Article

Wednesday, March 11, 2009 - 10:49:46 PM - arbalu Back To Top (2971)

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



get free sql tips
agree to terms