Why does SQL Server 2008 suggest I have the primary key column as an included column in an index

Question

SQL Server 2008 is telling me to include the Primary Key in an index in the Included Column field. I thought the PK was automagically returned with all indexes?

Answer 1

No - not the primary key - the clustering key is.

By default, that's the same in SQL Server - but it doesn't have to be. It could well be that in this table, the PK is different from the clustering key. In that case, the index won't have any knowledge of the PK.

The primary key is a logical construct to uniquely and clearly identify each row in your table. You use it for FK constraints etc.

The clustering key on the other hand is the physical index - it's the "street address" or the page number where your actual, physical data is stored. Because of that, it's included in each and every non-clustered index of that same table - it serves to lookup the actual data row, once you have found an entry in a non-clustered index.

UPDATE: okay, so I couldn't resist asking "The Queen of Indexing", Kimberly Tripp, about what she thinks on this topic - here's her answer:

If the PK is in fact the CL key then you’re right… it will automagically be in there. As for “where” he’s being told – I’m not sure (DMVs, DTA, somewhere???). Regardless, I’m sure that whatever tool he’s using is just going by what’s needed for the query. Meaning, the tool just didn’t even look to see if it was the CL key… adding it explicitly doesn’t add it twice so it’s not really a big deal at all.

Does that help?