SQL Server – Find Unique Index Without Unique Constraint

dmvsql serversql-server-2012sql-server-2016

I need to find list of unique indices that does not have unique constraint on that column. How to achieve this?

I know, following query will give me unique constraints.

SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE='UNIQUE'

Note: I am using SQL Server 2012.

Best Answer

sys.indexes includes columns that identify if the index is unique, supports a unique constraint, or supports a primary key constraint. The query below lists unique indexes that do not support a constraint:

SELECT *
FROM sys.indexes
WHERE is_unique = 1 AND is_unique_constraint = 0 AND is_primary_key = 0;

Related Solutions

Sql-server – Index physical stats DMV – Object ID/table not found

You are in the wrong database context.

Try:

select avg_fragmentation_in_percent 
from databasename.sys.dm_db_index_physical_stats(DB_ID('databasename'), OBJECT_ID('tablename'), 1, NULL, 'limited')

Alternatively you can just throw a

USE Databasename

on the first line.

I'm guessing that it's executing in the context of master or something similar, which won't have the OBJECTID you are looking for.

Sql-server – Unexpected Unique Constraint Violation

Why not just collate that column as case-sensitive? I don't think you need to resort to binary. This seems to work fine, just note that it may affect sorting, comparisons, unions etc:

CREATE TABLE [dbo].[Unit]
(
    [id]                SMALLINT       NOT NULL IDENTITY(1,1),
    [name]              VARCHAR(100)   COLLATE Latin1_General_CS_AS NOT NULL ,

    CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ([id]),
    CONSTRAINT [UQ_Unit_Name] UNIQUE ([name])
);
GO

INSERT INTO dbo.Unit (Name) VALUES('e³m³/day') 
INSERT INTO dbo.Unit (Name) VALUES('e3m3/day')

Results:

1 row(s) affected.
1 row(s) affected.

Now, this works for the example in your question, but whether it works for your entire set of requirements, is hard to say. You may have characters that are different to you but aren't treated differently in terms of case-sensitivity, such as:

INSERT INTO dbo.Unit (Name) VALUES('ß')
INSERT INTO dbo.Unit (Name) VALUES('ss')

Results:

1 row(s) affected.

And then:

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ_Unit_Name'. Cannot insert duplicate key in object 'dbo.Unit'. The duplicate key value is (ss).
The statement has been terminated.

In that case you may need to use binary after all.

Best Answer

Related Solutions

Sql-server – Index physical stats DMV – Object ID/table not found

Sql-server – Unexpected Unique Constraint Violation

Related Question