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

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
OBJECT_SCHEMA_NAME(so.[object_id]) AS SchemaName,
so.name AS TableName,
si.name AS IndexName,
si.type_desc AS IndexType,
sys.indexes si
JOIN sys.tables so ON si.[object_id] = so.[object_id]
si.type IN (0, 2)
AND si.is_primary_key=1

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.

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)

, , ,
Previous Post
SQL SERVER – Updating Data into ColumnStore Index with SQL Server 2012
Next Post
SQL Server – InMemory OLTP Hash Collisions Performance Overhead

Related Posts

8 Comments. Leave new

  • Well that’s depends, if you have identity columns as PK :-( and think use that column as CI you are wasting both, PK and CI! and that is the common sceneries… at least on this side of the world. and that is useless!. there a lot sceneries for both cases PK = CI and PK CI. the first case is greats when the table have Natural Key and put in order the columns thinking on get benefits of density and selectivity… if don’t use selectivity and density will waste a Clustered index.

    but any way is a nice post. ;)

  • Performance. If I’m mostly accessing a table via a FK, then I cluster on that so all of the records are together.

  • Please I have a table (marks) with about 586065 rows; when you try to insert records of about 1,200 rows into the table (marks) it takes about 140 seconds to complete insertion.
    When you try to select from the table (marks), it takes about 10 seconds to fetch records.
    Is there a way to minimize the time taken to insert or search significantly?

    I’m using stored procedure in SQL Server 2008 r2
    below is the stored procedure:

    USE [MyDB]


    @claEID varchar(50) = null,
    @stID varchar(50) = null,
    @SeCode varchar(10) = null,
    @exID varchar(5) = null,
    @dTaken smalldatetime = null,
    @mark float = null

    [CleID] = @claEID,
    [StID] = @stID,
    [sCode] = @SeCode,
    [ExamID] = @exID,
    [DateTaken] = @dTaken,
    [Marks] = @mark
    [CleID] = @claEID
    [StID] = @stID
    [sCode] = @SeCode

  • Snehal damor
    May 10, 2016 11:07 am

    I have fired below Select Statement and i got Error. Any one Help me.

    select top 100 MenuID,MenuGroup,MenuName,ObjectName,ObjectTitle from tblMenuMaster
    where ApplicationID=3 and recStatus=’A’

    Error Message.

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.)

  • Advay Pandya
    May 23, 2016 11:02 am

    Yes, both are clearly different. Primary key stands for uniqueness of column where Clustered Index stands to maintain order of data to store inside the table.
    So, if we have a requirement where we will need to maintain one column with unique records, but need to maintain order of the data based on another column then your scenario is useful.
    Of course this is very rare scenario.

    Please guide me if any correction needed from my comment. :)

    Btw, nice information sharing @Pinal

  • Vaibhav Survase
    June 20, 2016 10:41 am

    This is connection level error. Connect to database again and check.

  • FYI, If you have tables without PK and Clustered Index defined then below SQL identifies those tables (mostly in DW you see this kind of tables).

    — Identify table (with NO PK) and has a clustered index.
    OBJECT_SCHEMA_NAME(so.[object_id]) AS SchemaName,
    so.name AS TableName,
    si.name AS IndexName,
    si.type_desc AS IndexType,
    FROM sys.indexes si
    JOIN sys.tables so ON si.[object_id] = so.[object_id]
    WHERE si.type = 1 and si.is_primary_key = 0 — si.type IN (0, 2) — AND si.is_primary_key=1
    ORDER BY so.name


Leave a Reply Cancel reply

Exit mobile version