2

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

Thanks

2
  • 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 '15 at 3:59
  • 2
    You really need to specify what product you are using (and probably which version).
    – Andriy M
    Oct 31 '15 at 4:58
4

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.

0

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, privacy policy and cookie policy

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