Index Included Columns

By Philip Miller on 23 Apr
It would be nice to be able to specify included columns in an index for MSSQL databases.
Why can't you do it now? You can add multiple columns to the index? Is there something I'm missing (I'm not an index expert)?

J.Ja
What I mean by included columns is this. They would not go into the b-tree structure of the index, so no overhead on writing to the database, but would be included in the linked list at the bottom so you could access that information without having to do a table read.

As an example. Say you have a table that shows Name and description and a combo box to select a filter on something like a type. You could create an index on type and have name and description as included columns. The execution plan would select that index on type to scan since its part of the where clause of the query. Name and description would be included in the leaf nodes of that index so the query could return those two columns without having to access anything other than the index.

If there was just the type index with no included columns, the database server would get to the bottom of the index b-tree and have a bunch of rowids which it would use to read the database rows to get the name and description values. Depending on how the database is clustered it could involve mutliple read operations because it can't get everything at once.
Philip -

Got it!

I think this should all be part of the bigger "we need more advanced indexing options" discussion that's been going on:
http://www.outsystems.com/ideas/1683/more-index-options

J.Ja