CODEWITHSUNDEEP
sqlsql-servert-sqlindexing
super9
super9

Reputation: 30121

A covered index formed by a composite index or index with included columns

Are there any differences between these two covered indexes?

  1. A composite index with both FriendID and UserID
  2. A index with FriendID and UserID as an included column

Separate MS SQL Server related point. If the answer to the above question is 'No difference', is it me or does the Database Engine Tuning Advisor (DTA) always go crazy on included columns?

Upvotes: 1

Views: 852

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294317

Yes, they are different.

  • An index on (A, B) will contain the A,B combination as the index key, meaning it shows up on every non-leaf page.
  • An index on (A) include (B) will contain only A as the index key, so B will be present only on leaf pages, while non-leaf pages will contain only A.

The difference implies that indexes on (A,B) are wider that indexes on (A) include (B) so they contain more non-leaf pages, generating a bigger index (more pages), more IO and hence less efficient.

However, an index (A,B) will always cover an index (A) include (B). So if you already have indexes on (A,B) and the Tunning Advisor recommends a new index on (A) include (B), somehting is fishy. Either with the DTA recommendation, either with your analysis of existing indexes. No offense, but on situations like this I tend to trust the DTA over the human analysis, so ensure that your indeed have indexes that already cover the DTA recommendation, including order and filters (for SQL 2008).

Upvotes: 9

Related Questions