Improve Performance with SQL Server 2005 Covering Index Enhancements
Written By: Edgewood Solutions Engineers -- 9/29/2006
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
