Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?


  • 2
    Are you using Microsoft SQL Server or some other product? If using SQL Server, then a uniquifier is added to insure that the clustered index is unique even if the definition is non-unique.
    – RLF
    Oct 31, 2015 at 3:59
  • 2
    You really need to specify what product you are using (and probably which version).
    – Andriy M
    Oct 31, 2015 at 4:58

2 Answers 2


Create a simple table with a non-unique clustered index:

CREATE TABLE dbo.TestCluster
    TestClusterID INT NOT NULL

CREATE CLUSTERED INDEX CX_TestCluster --note there is no "UNIQUE" keyword here
ON dbo.TestCluster(TestClusterID);

This query shows if the given index is unique or not:

SELECT SchemaName = s.name
    , TableName = o.name
    , IndexName = i.name
    , IndexType = i.type_desc
    , IsUnique = i.is_unique
FROM sys.indexes i
    INNER JOIN sys.objects o ON i.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name = 'TestCluster'
    AND i.type_desc = 'CLUSTERED';

It's always vitally important that you specify/show the schema, as I have done above, since it very easy that objects with the same name might exist in multiple schemas.


Assuming you are running SQLServer:

SELECT  OBJECT_NAME(object_id) AS TableName ,
    name AS IndexName ,
    type_desc AS IndexType ,
    CASE WHEN is_unique = '1' THEN 'UNIQUE'
         WHEN is_unique = '0' THEN 'NON UNIQUE'
    END AS Index_Desc
FROM    sys.indexes
WHERE   type_desc = 'CLUSTERED'
    AND OBJECT_NAME(object_id) = 'MyTable';

Just one other method, but not better than Max Vernon's code, which gives the schema name too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.