Non-unique indexes that should be unique


Defining the appropriate primary key and unique constraints is fundamental for a good database design.

unique

One thing that I often see overlooked is that all the indexes with a key that includes completely another UNIQUE index’s key should in turn be created as UNIQUE. You could argue that such an index has probably been created by mistake, but it’s not always the case.

If you want to check your database for indexes that can be safely made UNIQUE, you can use the following script:

SELECT OBJECT_SCHEMA_NAME(uq.object_id) AS [schema_name],
    OBJECT_NAME(uq.object_id) AS table_name,
    uq.name AS unique_index_name,
    nui.name AS non_unique_index_name
FROM sys.indexes AS uq
CROSS APPLY (
    SELECT name, object_id, index_id
    FROM sys.indexes AS nui
    WHERE nui.object_id = uq.object_id
        AND nui.index_id <> uq.index_id
        AND nui.is_unique = 0
        AND nui.has_filter = 0
        AND nui.is_hypothetical = 0
) AS nui
WHERE is_unique = 1
    AND has_filter = 0
    AND is_hypothetical = 0
    AND uq.object_id IN (
        SELECT object_id
        FROM sys.tables
    )
    AND NOT EXISTS (

        SELECT column_id
        FROM sys.index_columns AS uc
        WHERE uc.object_id = uq.object_id
            AND uc.index_id = uq.index_id
            AND uc.is_included_column = 0

        EXCEPT

        SELECT column_id
        FROM sys.index_columns AS nuic
        WHERE nuic.object_id = nui.object_id
            AND nuic.index_id = nui.index_id
            AND nuic.is_included_column = 0

    )
ORDER BY [schema_name], table_name, unique_index_name

You may wonder why you should bother making those indexes UNIQUE.

The answer is that constraints help the optimizer building better execution plans. Marking an index as UNIQUE tells the optimizer that one and just one row can be found for each key value: it’s a valuable information that can actually help estimating the correct cardinality.

Does the script return any rows? Make those indexes UNIQUE, you’ll thank me later.

About these ads

Posted on January 29, 2014, in SQL Server and tagged , , , , , , . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 521 other followers

%d bloggers like this: