Included Columns In SQL 2005

Non-clustered indexes in SQL Server contain only the values for the columns they index, and then a ‘pointer’ to the row with the rest of the data. This is similar to the index in a book: the index contains only the key word, and a page reference you can turn to for the rest of the information. Generally, the database will have to follow pointers from the index to other pages (known as bookmark lookups) to gather all the information required for a query.

If, however, the index contains all of the columns requested in a query, the savings in disk IO can be tremendous - the query engine doesn’t have to follow pointers around and seek out other pages on disk. We call these types of queries covered queries, because all of the columns needed for the query are covered by the index.

Covering a query with an index can be a significant performance boost, and in the BI applications I work with we often use this optimization. The caveat to this optimization is that the larger the index key, the more work the database has to do to compare keys. Measurements must be taken to ensure the proper balance. Also, index keys are limited to a maximum size of 900 bytes and 16 columns in SQL Server.

SQL 2005 introduces a nice little feature: included columns. Included columns will allow us to pack data values into a non-clustered index without increasing the key size of the index. Included columns also don’t count towards the 900 byte key limit or the 16 maximum columns. Example:

The above index has a small key size (just the PostalCode column), but will be able to cover any query using the other address fields. We will still pay a price in disk space, but it is now possible to design more effecient non-clustered indexes in SQL 2005.