SQL Server 2008 R2 Missing Index

I am performing index maintenance on a database. I have run SQL script that uses DMVs to provides a list of indexes SQL Server believes should be created. I am not creating indexes just because the index is listed as missing. My question, Why would the primary key column be listed as a missing index? The table is arranged in primary key order, correct? Why would SQL Server beleive a noclustered index should be created on the primary key?

Thanks in advance.

The primary key in SQL Server is by default also the clustering key - and the data is physically ordered by the clustering key in a table.

So if this primary key is listed, maybe it's because it is a non-clustered primary key - something that you have to do explicitly - but it's entirely possible.

The primary key is a logical construct - it's job is to make sure each row in a table can be uniquely and reliably identified. It has no impact whatsoever on the physical structuring of the table.

The clustering key (and thus the clustered index) is what determines the physical ordering of the data in a SQL Server table. The table's primary key is the clustering key by default - but it doesn't have to be - you can have two totally separate sets of columns for the primary key and the clustering key.

But all these things aside: the DMV's that identify missing indexes aren't perfect - do take their recommendations with a grain of salt! Don't just blindly implement everything that DMV recommends - sometimes it recommends stuff that's already in place, or some of its recommendations are even contra-productive! Don't blindly trust it - it's a helping tool - but it's not perfect!

StackExchange Database Administrators Q#61183, answer score: 2