Covered index formed by a composite index or an index with columns included

Question

Are there differences between the two covered indexes?

• Composite index with FriendID and UserID
• Index with FriendID and UserID as an included column

Split the point of MS SQL Server. If the answer to the aforementioned question is “No difference”, is it me or the Database Engine Tuning Advisor (DTA) always going crazy about the included columns?

Answer 1

Yes, they are different.

• The index on (A, B) will contain a combination of A, B as the key dictionary, which means that it is displayed on each non-leaf page.
• The index on (A) includes (B) will contain only A as an index key, so B will be present only on leaf pages, and pages with non-leaf contents will contain only A.

The difference means that the indices on (A, B) are wider than the indices on (A) include (B), therefore they contain more non-leaf pages, generating a larger index (more pages), more IO and, therefore, less efficient.

However, index (A, B) will always cover index (A) include (B). Therefore, if you already have indexes on (A, B) and a Tunning advisor, it is recommended that you include the new index in (A) (B), some of which are suspicious. Either with a DTA recommendation or with an analysis of existing indices. Don’t be offended, but in such situations I tend to trust DTA over human analysis, so make sure you really have indexes that already cover the DTA recommendation, including order and filters (for SQL 2008).