SQL Server 2005: INCLUDE argument with Nonclustered Indexes.


There is a new INCLUDE argument available in SQL Server 2005 when creating nonclustered indexes. According to the MSDN documentation, INCLUDE is used to: Specify the nonkey columns to be added to the leaf level of the nonclustered index. In a nutshell, this new clause allows us to better target columns we need in a key and satisfy more covering index scenarios. The result of using this argument should be less IO (which is good!). I think it’s important to point out the Design Recommendation for this new argument

“Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query included nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s