| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2010-04-15 : 08:40:27
|
| I am trying to understand the benefits of the Included Columns for Indexes.The only reasons I can see for using them, as opposed to making the Included columns, as part of the index itself:- Takes up a little less memory since these are only in the leafs- Index itself has a max size so sometimes not all the included columns can fit in the index itselfOtherwise, wouldn't putting the columns in the Included part be slower (in most situations) with Selects as opposed to putting those Columns in the index itself? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-15 : 08:59:26
|
Following example:selectOrderdatefrom Orderswhere OrderNo=4711OrderNo is indexed1. Orderdate isn't an included columnSQL searches in index, has a hit and then retrieves Orderdate in a second step from the data using the information from the index.2. Orderdate is an inluded columnSQL searches in index, has a hit and take the Orderdate without retrieving it in a second step because it is stored with the index.Hope this clear enough because in my poor english I can't describe better...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-04-15 : 09:08:05
|
quote:
Originally posted by webfred
Following example:selectOrderdatefrom Orderswhere OrderNo=4711OrderNo is indexed1. Orderdate isn't an included columnSQL searches in index, has a hit and then retrieves Orderdate in a second step from the data using the information from the index.2. Orderdate is an inluded columnSQL searches in index, has a hit and take the Orderdate without retrieving it in a second step because it is stored with the index.Hope this clear enough because in my poor english I can't describe better...
No, you're never too old to Yak'n'Roll if you're too young to die.
Thankyou.But my query relates to comparing the included column vs. having that column as part of the index itself.So in your example, why not just make the index OrderNo, OrderDate? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-04-15 : 09:52:16
|
quote:
Originally posted by webfred
I am not the best mate to make it more clear but here is a good page for that:http://www.sqlteam.com/article/included-columns-sql-server-2005
No, you're never too old to Yak'n'Roll if you're too young to die.
The only advantage that article mentions (in putting the Column in the Include rather than in the index itself, when you can) is saving memory. In their example it saved only 5%. Unless I missed something. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-15 : 11:05:17
|
well -- one big benefit is that you can include Large data types like VARCHAR(MAX) in the index with INCLUDE. but that you can't make them part of a normal index.Not sure how beneficial that will be for you but it's good for delivering large content. (we use indexes like that for translation mostly)For example here:CREATE TABLE #foo ( [a] INT , [b] INT , [c] VARCHAR(MAX) PRIMARY KEY ([a]) )CREATE INDEX IX_FOO_B_C ON #foo ([b]) INCLUDE ([c])-- CREATE INDEX IX_FOO_B_C2 ON #foo ([b], [c]) The first INDEX will create but the comment out one will throw an error.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-15 : 11:19:17
|
I agree its somewhat minimal, although I guess Microsoft must have had reasons for adding the feature. Maybe its more to do with doing things that were not possible before (although I can't see that adding a VARCHAR(MAX) to an Index (as an INCLUDE column) would be high on my list of "essential performance boosting changes"  However, in many of our "covering indexes" (where we included the additional, covering, columns as keys in SQL 2000 as INCLUDE wasn't available then) the covering columns have no benefit in the non-leaf pages.So a little space is saved in the non-leaf pages ...... however, [I assume] that means that more rows are stored in non-leaf pages, because the INCLUDE columns are no longer part of the index keys, and thus SQL has less pages to search to get to the data.That part could be significant.I also assume that fewer [real] keys may reduce the complexity of what SQL has to calculate etc., and that may help (only a small improvement, I'm sure, but every little bit adds up ...)We've already started moving "non key columns" in indexes to be INCLUDE columns, although I haven't done any before/after performance testing - perhaps I should! |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-04-15 : 11:25:43
|
| hmmm, that's interesting.With Insert/Update/Delete operations, is the Included Column a little quicker than having that column inside the Index itself? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-15 : 11:48:05
|
| Interesting point, and potentially very significant.An UPDATE to the Included column, if there was no change to the Key columns, would only need the LEAF page to be changed. Whereas a change to the KEY columns means that the original key has to be deleted (on all pages in the index tree) and a new entry inserted at the correct point (on all levels of the index tree).Assuming SQL is smart enough to realise that the KEY columns have not changed, and take on action on non-leaf pages.Might also mean that adding a high-turnover INCLUDE column is viable ("update cost" to just the LEAF page is acceptable) - where it might deliberately have not been included as a KEY column under previous SQL versions. |
|
|
|
|
Included column