CODEWITHSUNDEEP
sql-serverindexing
Johnno Nolan
Johnno Nolan

Reputation: 29659

Indexes with included columns or composite indexes

Looking at the missing index DMVs on SQLServer it suggests I add the following index:

CREATE INDEX [IXFoo] ON [a].[b].[MyTable] ([BarFlag]) INCLUDE ([BazID])

There's two things that confuse me.

I guess I'm not understanding the INCLUDE keyword properly. I've looked at msdn for an explanation but I'm still unclear.

Can someone explain why this index is suggested over a composite and explain the INCLUDE keyword to me?

Upvotes: 3

Views: 2702

Answers (2)

Justin
Justin

Reputation: 86729

The INCLUDE keyword just means that the value of the included columns should be stored in the index itself so that for queries like the following:

SELECT BazID FROM MyTable WHERE BarFlag = @SomeValue

It isn't necessary to do an additional lookup on the table itself in order to find the value of BazID after doing an index seek.

Upvotes: 3

marc_s
marc_s

Reputation: 754428

The main difference is this:

  • if you create a composite index on (BarFlag, BazID), then your index will contain both values on all levels of the index b-tree; this means, the query analyzer will also have the chance to use both values when making decisions, and this can support queries that specify both columns in a WHERE clause

  • if you create an index on (BarFlag) and only include (BazID), then your index will contain only BarFlag values on all levels of the index b-tree, and only on the leaf level, the "last" level, there will also be the values of BazID included. The BazID values cannot be used in selecting the data - they're just present at the index leaf level for lookup.

Just for an INT and a BIT that isn't much of a concern, but if you're dealing with a VARCHAR(2000) column, you cannot add that to the actual index (max. is 900 byte per entry) - but you can include it.

Having a column included in an index can be useful if you select for these two values - then if SQL Server finds a match for BarFlag, it can look up the corresponding BazID value in the leaf-level node of the index itself and it can save itself a trip back to the actual data page (a "bookmark lookup") to go grab that value from the data pages. This can be a massive boost for performance

And you're right - having an index just on BarFlag (BIT) really doesn't make sense - then again, that DMV only suggests indices - you're not supposed to blindly follow all its recommendations - you still need to think and consider if those are good recommendations (or not).

Upvotes: 5

Related Questions