Included Columns

Question

Included Columns

david_okeefe

Old Hand

Points: 396
More actions
August 2, 2009 at 8:09 pm

#208863

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?

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, MVP, M.Sc (Comp Sci)
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

david_okeefe Old Hand Points: 396 More actions · Answer 3

Thank you both for your responses. That helps clear things up immensely.

Paul White SSC Guru Points: 150467 More actions · Answer 4

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi