Sql-server – How to know if a table has a non-unique clustered index

indexsql server

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

Thanks

Best Answer

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.

Related Question