“Including” Columns in an Index
A neat feature in SQL Server 2005 is the ability to "include" columns in an index. These included columns are not in the main part of the index, but are additional information in the index.
For example, lets say you have the following SELECT statement:
SELECT Url FROM Site WHERE Category = 'News';
You might be tempted to create a covering index with Category as the first column, and Url as the second column. Since URLs can be 2083 characters, you can't put the URL column in an index since it would exceed 900 bytes. However, the query above would benefit from the following index where the URL is "included" in the index and therefore isn't restricted to 900 bytes.
CREATE INDEX IX_Site ON dbo.Site (Category) INCLUDE (Url);
The main part of the index only contains the category column. The index also stores the Url, but the Url can't be efficiently used for filtering. In the SELECT statement above, this is OK, since the Url is only returned, not filtered.
Another benefit is that the main part of the index is smaller, so it is faster to find records in the index.