Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Non Cllustered Index - Composite vs Include Columns Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2010 12:21 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:39 AM
Points: 406, Visits: 772
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



Post #924069
Posted Wednesday, May 19, 2010 1:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:40 AM
Points: 316, Visits: 908
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
Post #924094
Posted Wednesday, May 19, 2010 1:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:39 AM
Points: 406, Visits: 772
Thanks a lot Stefan. I didn't know about the update scenario.

- arjun
Post #924096
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse