Why use 'Included' columns in SQL Server 2005
Recently I was working on an application where I got a lot of mileage out of using a new feature of SQL Server 2005 called included columns. When you add a column as an “included” column, it gets stored at the leaf level of the index and it is not part of the index key. This only works for non-clustered indexes.
The main advantages of included columns are
1) You can exceed the 900 byte limitation of the index key, any columns that would push you over that limit could instead be added as included rather than key columns
2) You can include datatypes that are not allowed as key columns, such as varchar(max), nvarchar(max) or XML. Note – you still cannot use the old datatypes such as text or ntext.
3) You can greatly expand you options for creating covering index.
4) You can reduce the size of the index to make it more efficient by including only lookup columns in the key, and then adding other query covering columns as included columns
As always, you should use some judgment. If you include four varchar(max) columns and they contain a lot of data, the size of your index will be huge. Also, since each column will be maintained in the table and in the index, updating large columns will slow down the application somewhat. Below is a sample CREATE INDEX script that indexes the Title column and includes a varchar(max) column called Content:
CREATE NONCLUSTERED INDEX [IX_MyTable] ON [dbo].[MyTable]
(
[Title] ASC
)
INCLUDE ( [Content])
Cross-posted from SQLBlog! -
http://www.sqlblog.com