SQL Server Include Columns

I've just been refreshing my memory on the basics of indexes after my daily email from SQL Server Central arrived, advertising this excellent beginners guide to SQL Server Indexes from Gail Shaw.

I think it's a great article, covering all the essentials in a clear and precis manner. My only criticism was that she skipped over the value of INCLUDE COLUMNS, without introducing them in the first place.

I'm a well travelled Database professional having started on DB2, moved to IMS, then Teradata before arriving at SQL Server. I'm fortunate to have been able to pick-up SQL Server very quickly through my good friends James Rowland-Jones and Jamie Thomson (aka SSIS Junkie), both SQL Server MVP's. (and if your in the UK, James is speaking at SQLBits, check him out)

I recognised INCLUDE COLUMNS from my Teradata days, and whilst maybe not exactly the same, the Teradata Join Index and even more impressive Aggregated Join Index are similar in that they allow you to specify columns as part odf the index (not the index key), that allows the query to be satisfied from the index alone, without resort to the base table.

Because Gail didn't really cover Include Columns, I did some research on the web and was a bit surprised that the majority of the SQL Server community considered Include columns as a way of getting around the 16 column and 900 byte limitation on a sql server non-clustered index. Despite the fact that almost everyone agreed that these limits are highly unlikely to be reached in normal usage, something that with my 15 years of experience, I'd agree with.

The real benefit of include columns is being able to elevate a few select columns from the table to the index, such that the index read alone can satisfay the query. I work in retail (retailmonster get it!) so for example you may have some individual basket data, ie everyitem an individual customer buys in a single trip. This is retail gold dust but also incredibaly space and CPU heavy. You might split this into a basket header record, with an index of storeid/calendardate, but since a lot of people might want the total retail value of the basket, or the number of items in the basket, if you specify totalbasketretailvalue and basketitemcount fields as include columns, then a query asking for the index columns and only these fields, would be satisfied by the index read and not have to go to the table.

James Rowland-Jones has just authored a new book, SQL Server 2008 Internals, in which he covers the benfits of include columns for SQL server.