By default when a primary key constrained is created on a table/view, SQL Server automatically creates a unique clustered in order to enforce it. And since a table can only have one clustered index, all the subsequent or any previous indexes created before that are created as a non-clustered index.
That works best in most cases and is the recommended best practice.
And decision to have clustered index on what columns affects everything about everyday working of an application. And also as a general best practice every table should have a clustered index, but its not required and there are cases where its best not to.
So now imagine a scenario where a table has the PK but the clustered index is on non PK columns. I am going to assume that there must be well thought-out index strategy for that particular table at the design time.
But over time the usage patterns may evolve and/or through endless enhancements, bug fixes etc. now that index may not be optimal. Of course that could be true for any index but the consequences are more severe if that's the case for a clustered index.
Now you are supporting that database in production mode. Users report that the query performance has gotten extremely slow and you also notice that the index optimization job is taking much longer to complete.
As part of your research and troubleshooting this issue, one of things you decide to check is index strategy already in place and you check 1) Are there any missing indexes 2) are the indexes of correct type (unique, clustered, non-clustered etc.), fill factor etc. 3) whether the clustered index is created on right columns etc....
The query that I have below is to find out if clustered index is on non-pk columns. I have consciously decided to exclude tables that have either no clustered index, no primary key or there is clustered as well as non-clustered index created on primary key columns.
WITH cte_indexes
AS (SELECT db_name() db_name,
schema_name(o.schema_id) schema_name,
object_name(i.object_id) object_name,
o.type_desc object_type,
i.NAME index_name,
i.type_desc index_type,
i.is_primary_key,
o.object_id object_id,
pk_index_id = (SELECT index_id FROM sys.indexes c WHERE c.object_id = o.object_id AND c.is_primary_key = 1),
pk_index_name = (SELECT name FROM sys.indexes c WHERE c.object_id = o.object_id AND c.is_primary_key = 1),
clustered_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max))
FROM sys.index_columns ic
INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ( ic.object_id = o.object_id AND ic.index_id = i.index_id)
FOR xml path ('')), 1, 1, '') ), ''),
pk_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max))
FROM sys.index_columns ic
INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ( ic.object_id = o.object_id AND ii.is_primary_key = 1)
FOR xml path ('')), 1, 1, '') ), '')
FROM sys.objects o
INNER JOIN sys.indexes i ON o.object_id = i.object_id
WHERE Objectproperty(o.object_id, 'ismsshipped') = 0)
SELECT db_name,
schema_name,
object_type,
object_name,
index_name non_pk_clustered_index_name,
pk_index_name,
clustered_index_columns,
pk_index_columns
FROM cte_indexes
WHERE 1 = 1
AND index_type = 'CLUSTERED'
AND pk_index_id ! = 1
AND clustered_index_columns != pk_index_columns
ORDER BY object_name,
index_name
Caveat: I only considered the traditional index types (clustered, non-clustered, unique, non-unique etc.).
I have tested this on SQL Server versions 2008 R2 and above.