Thursday 11 March 2010

Included Columns - Index Sizes

When viewing an execution plan in Managment Studio it sometimes suggests an index you could add. From what I've seen, these suggestions frequently include adding adding every column as an 'included column'.

Doing this has the storage affect of duplicating the table. Not exactly ideal for storage, even if you do eliminate your Bookmark Lookups.

In the screenshot above >
ix_1 - this is the clustered index , i.e. the table itself.
ix_3 - is a non clustered index on 2 columns (bigint, datetime2)
ix_4 - is another non clustered index on the same 2 columns (with all 10 included columns as per Management Studio's suggestion)

Obviously this is an extreme example (on a schema screaming for some normalization), but I thought it noteworthy anyway.

r

No comments: