Why use INCLUDE in a SQL index
- by StarLite
I recently encountered an index in a database I maintain that was of the form:
CREATE INDEX [IX_Foo] ON [Foo]
( Id ASC )
INCLUDE
( SubId )
In this particular case, the performance problem that I was encountering (a slow SELECT filtering on both Id and SubId) could be fixed by simply moving the SubId column into the index proper rather than as an included column.
This got me thinking however that I don't understand the reasoning behind included columns at all, when generally, they could simply be a part of the index itself. Even if I don't particularly care about the items being in the index itself is there any downside to having column in the index rather than simply being included.
After some research, I am aware that there are a number of restrictions on what can go into an indexed column (maximum width of the index, and some column types that can't be indexed like 'image'). In these cases I can see that you would be forced to include the column in the index page data.
The only thing I can think of is that if there are updates on SubId, the row will not need to be relocated if the column is included (though the value in the index would need to be changed). Is there something else that I'm missing?
I'm considering going through the other indexes in the database and shifting included columns in the index proper where possible. Would this be a mistake?
I'm primarily interested in MS SQL Server, but information on other DB engines is welcome also.
