Using Included Columns in SQL 2005

Roman Rehak has got a quick and great explanation on the feature of using "INCLUDE" columns at: http://sqljunkies.com/WebLog/roman/archive/2007/05/03/32225.aspx

The purpose of Included columns is to help improve query performance yet at the same time the dB engine will not consider these columns when calculating the number of index key columns or size.
http://msdn2.microsoft.com/en-us/library/ms190806.aspx

A quick way to list out the included columns of indexes on a table, you can use the query below:

declare @table_name varchar(256)
set @table_name = ‘<Table Name>’
select i.[name] as IndexName, i.[Type_Desc] as IndexType, i.[Is_Unique] as IsIndexUnique, c.[name] as ColumnName, c.[column_id], ic.[Is_Included_Column]
from sys.index_columns ic
inner join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
inner join sys.indexes i
on i.object_id = ic.object_id
and ic.index_id = i.index_id
where ic.object_id = (
select object_id from sys.objects where [name] = @table_name
)

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