May 19, 2010 at 12:21 am
create nonclustered index IX_ID on tbl1(col1,col2,col3)
and
create nonclustered index IX_ID on tbl1(col1) include (col2,col3)
I would like to know the differences between the above two. And also when do I go for a composite index and when for include columns?
Of what I know when I create a composite index, the keys (col1, col2, col3) are stored together and sorted. When I include columns, the values of the included columns will be copied beside the key column(s).
I should go for a composite index when i do a lot of joins (or seeks) on all the three columns (ie col1, col2, col3). And I should include columns when i have a statement like
select col1, col2, col3 from tbl1 where col1 = 67
A little knowledge is very dangerous 🙂 I don't want to be doing the wrong thing here. Please correct me if I am wrong.
- arjun
https://sqlroadie.com/
May 19, 2010 at 1:30 am
You should only use the composite version if you actually perform seeks or joins on all three columns just as you said.
In all other cases you should use include.
Include is much more efficient mainly for two reasons:
1) When an included column is updated it is just updated in-place in the index. When a column that is part of a key is updated the old index entry needs to be deleted and a new entry needs to be inserted in a new place. This new insert might even cause a page split. This makes updating a key-column very much more costly than updating an included column
2) included columns are only stored in leaf pages of the index. higher levels in the tree only stores the key columns. This means that it is possible to fit more rows on the higher levels of large indexes which means that they take less space and are faster to search.
/SG
May 19, 2010 at 1:37 am
Thanks a lot Stefan. I didn't know about the update scenario.
- arjun
https://sqlroadie.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply