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

Included Columns Expand / Collapse
Author
Message
Posted Sunday, August 02, 2009 8:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 09, 2014 6:56 AM
Points: 14, Visits: 118
In my 70-430 book, I just read through the indexes section. The only new topic for me was included columns. Although I have used them before, I didn't fully understand what they were for until now. As my book outlined it, they can be used to stay under the 900 byte limit of an index and to prevent bookmarked look ups. With that said, why wouldn't you include every column in your table as an included column in every index on that table? As I understand it, this would be space prohibitive, but are there any other reasons not to do this?
Post #763855
Posted Sunday, August 02, 2009 8:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
I haven't actually done a measurement but I would say that a good reason NOT to do it would be performance just like doing a SELECT * causes performance problems when you only need to return 1 or 2 columns on a 100 column table.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #763863
Posted Wednesday, August 05, 2009 12:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
If you include all the table columns as include in every index then you're incuring major overhead in two places.
1) Space. Each index will be the same size as the table so if you have 5 nonclustered indexes on a table, that table will occupy 6* the space it would have otherwise (table itself and 5 indexes). Space is cheap, but not that cheap especially if you're working in the TB range.

2) When a column is updated it will have to be changes in the base table and in every single index. If you have 5 indexes each with every single column in them, then every change has tlo be done in 6 places. Maybe OK if the DB is read only.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #765749
Posted Wednesday, August 05, 2009 12:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 09, 2014 6:56 AM
Points: 14, Visits: 118
Thank you both for your responses. That helps clear things up immensely.
Post #765758
Posted Friday, August 07, 2009 5:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
I would just add that a nonclustered index that INCLUDEs every non-key column in the object is pretty close to being another clustered index. This can be an optimization in some very edge-cases, but for 99.999999% of us, it is to be avoided:

One of the main advantages of a non-clustered index is that the average bytes per row is much smaller than for the clustered index. This means that many more rows fit on an index page compared to a data page. So, when the server reads a page it might get ten rows from a data page or several thousand or more from a single index page.

Fattening the n-c index up removes that advantage completely.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #766831
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse