SQL SERVER – Identify Table Where Clustered Index is Not a Primary Key – DMV

April 26, 2016

Let us first see script which will return us tablename with schema where clustered index is not a primary key.

-- Identify table where clustered index is not a primary key
SELECT
OBJECT_SCHEMA_NAME(so.[object_id]) AS SchemaName,
so.name AS TableName,
si.name AS IndexName,
si.type_desc AS IndexType,
si.is_primary_key
FROM
sys.indexes si
JOIN sys.tables so ON si.[object_id] = so.[object_id]
WHERE
si.type IN (0, 2)
AND si.is_primary_key=1
ORDER BY
so.name

When you execute the script, it will list all the tables in the database where there are clustered index on the table but they are not a primary key.

SQL SERVER - Identify Table Where Clustered Index is Not a Primary Key - DMV clpk3

Default Behavior of SQL Server

The default behavior of SQL Server is to create automatically Clustered Index (CI) on the table when we defined Primary Key (PK). This has created many confusions and plenty of developers still believe that Primary Key and Clustered Index is the same thing. In the last survey of 100’s people who attended my user group presentation, you may find it surprising that only 8 people knew that they are different.

There are four different scenarios:

Scenario 1: PK will default to CI
Scenario 2: PK is defined as a Non-CI
Scenario 3: PK defaults to Non-CI with another column defined as a CI
Scenario 4: PK defaults to CI with other index defaults to Non-CI

I strongly suggest you to read my earlier blog post where I explained in detail each of these scenarios. Primary Key and Non-clustered Index in Simple Words

Clustered Index – Not a Primary Key

The real question is that why do we have to find all the tables where we have clustered index on the table but it is not a primary key. What is the reason for behind the script? The reason is pretty simple.

Primary Key (PK) can be Clustered (CI) or Non-clustered (Non-CI) but it is a common best practice to create a Primary Key (PK) as Clustered Index (CI).

It is considered as the best practice when we Clustered Index on the same table as a Primary Key and that is why SQL Server has defaulted to this behavior (which has generated the confusion). In my career of performance tuning expert I have observed only once where PK and CI are different.

Now here is a question for you – I want you to execute the above script on your server and see if you find any record as a result set. If you see rows in the resultset, I would like you to do figure out what is the business reason behind the table in the reseltset that it has to have different PK and CI. Once you find out the result, I would like to request you to post your answer as a comment. I will be happy to highlight your answer with due credit in the future blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)