INDEXES

Question

Post reply

INDEXES

mroja_2005

SSC-Addicted

Points: 474
More actions
September 22, 2009 at 3:43 am

#96323

What makes the difference between the below two create index statements????
In my scenario, the second create index statement is giving me better performance.
Conceptually, what is difference??? Why do we go for INCLUDE clause in the CREATE INDEX statement
create index on (c1,c2,c3,c4,c5)
create index on (c1,c2,c3)
include (c4,c5)
Regards,
Roja

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Please see BOL

http://msdn.microsoft.com/en-us/library/ms190806.aspx

Feel free to post back with any further queries

Clear Sky SQL
My Blog[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

In general, the narrower the index, the more keys that get packed on to a page, the quicker the seeks. So, with the INCLUDE, you're taking two columns out of the index key, that probably aren't needed for filtering, thereby reducing the size of the key, but you're still geting a covering index.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 3

mroja_2005 (9/22/2009)
What makes the difference between the below two create index statements????
In my scenario, the second create index statement is giving me better performance.
Conceptually, what is difference??? Why do we go for INCLUDE clause in the CREATE INDEX statement
create index on
(c1,c2,c3,c4,c5)
create index on
(c1,c2,c3)
include (c4,c5)
Regards,
Roja

Hi,

Here u r using covering index. I hope the included columns are non indexed, i.e. no non clustered indexes are created on these column but they are useing for index seek column. Covering index in SQL 2005 introduced this included index feature which will work like u have the index on c4 and c5 also, hence its faster.

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 4

Hmmmmmm....

Let me explain it a bit more clearly for you.

Indexes are effectively like cards in a library. You might have a library card set that is sorted by author. You might have another sorted by topic. The information on the card tells you where to go and look in the library for the book that you want.

That's roughly the equivalent of an index.

In actual fact, indexes are kind of like trees. In a clustered index, you have the 'root node' (basically the table) which points to several 'intermediate' nodes, which in turn point to 'leaf' nodes. The leaf nodes in a clustered index actually contain the data of the row, and the intermediate nodes contain information relating to the clustered index keys. So - referring back to the example above, if you have a clustered index on 'author' then the intermediate nodes contain a list of authors, which can be quickly looked through, pointing to the relevant leaf node which is where the data can be found.

In a non-clustered index you have the 'root node' (which is basically the index) and then you go straight to leaf nodes. However, in a non-clustered index, the leaf nodes do not contain the data from the row, they just contain a pointer to where to find the data. This is where INCLUDE comes in. INCLUDE stores the data from specified columns on the leaf nodes themselves. Meaning that if you want to return data from the table, and you only want to get the data from the INCLUDEd columns, then you don't need to actually need to go near the table's leaf nodes at all, because the data is right there for you.

As a way to demonstrate it - imagine again the library card box sorted by 'author'. On each library card, the location of the book in the library is printed. However, you want to know the publisher of the book. If the publisher of the book was printed right there on the library card, then you wouldn't need to walk over to the book and look at it to see who the publisher was. So, printing the publisher on the library card is like an INCLUDEd column - it doesn't change the order of the data, but it does provide you additional data about what you have found so that you don't then need to refer back to the original.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone