create nonclustered index IX_ID on tbl1(col1,col2,col3)
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.