jump to navigation

Identifying unique indexes that aren’t defined as unique 2011/05/16

Posted by DB Dave in Uncategorized.
Tags: , ,
trackback

Confused? So am I, but after changing the title of this post about a dozen times this is what we’re left with… 🙂

I was in the process of examining some indexes on our test Denali server (see previous posts) when I decided to check how many of our non-unique, non-clustered indexes are actually made up of a unique column (or a unique combination of columns in the case of a compound index). In other words, which non-clustered indexes could I convert to UNIQUE non-clustered indexes? I whipped up a script to do this and thought I’d share.

If you’re wondering why I might want to do this; I’m interested to see what impact it has on index storage, as well as read performance. I’m by no means saying you should go and recreate all of your NC indexes as unique – remember that this script will return all indexes where the *current* data in the table is distinct. There are no guarantees that future inserts & updates won’t cause the data to become non-unique (and break your application).

Here’s the script. It’s pretty straight forward, and simply returns a list of tables/indexes at the moment, but could easily be change to actually output the CREATE / DROP_EXISTING commands instead.

SET NOCOUNT ON;
DECLARE @i INT = 1,
        @rc INT,
        @cmd NVARCHAR(2000),
        @tablename NVARCHAR(128),
        @indexname NVARCHAR(128),
        @collist NVARCHAR(1000),
        @rowcount INT,
        @rowcount_d INT;

DECLARE @tt_ncidxs TABLE (rowid INT IDENTITY, [object_id] INT, [object_name] VARCHAR(128), [index_id] INT, index_name VARCHAR(128), [index_columns] VARCHAR(1000), is_unique BIT);

WITH IndexColumns AS 
(
    SELECT  DISTINCT 
            i.[object_id],
            QUOTENAME(OBJECT_NAME(i.[object_id])) AS [object_name],
            i.[index_id],
            QUOTENAME(i.[name]) AS [index_name],
            STUFF(  
                    (
                    SELECT  ', ' + QUOTENAME(COL_NAME([object_id], column_id)) AS 'data()'
                    FROM    sys.index_columns AS t2  
                    WHERE   c.[object_id] = t2.[object_id]  
                    AND     c.index_id = t2.index_id 
                    FOR     XML PATH ('')
                    ), 1, 2, ''
                ) AS 'index_columns'
    FROM    sys.indexes AS i  
    JOIN    sys.index_columns AS c ON c.[index_id] = i.[index_id] AND c.[object_id] = i.[object_id]
    JOIN    sys.objects AS so ON i.[object_id] = so.[object_id] 
    WHERE   i.is_unique = 0
    AND     i.is_unique_constraint = 0
    AND     so.is_ms_shipped = 0
    AND     so.[type] = 'U'
)
INSERT  @tt_ncidxs ([object_id], [object_name], [index_id], [index_name], [index_columns])
SELECT  [object_id],
        [object_name],
        [index_id],
        [index_name],
        [index_columns]
FROM    IndexColumns 

--SELECT * FROM @tt_ncidxs
SELECT @rc = @@ROWCOUNT;

WHILE @i <= @rc
BEGIN
    SELECT @cmd = NULL, @rowcount = NULL, @rowcount_d = NULL, @tablename = NULL, @collist = NULL;
    
    SELECT  @tablename = [object_name],
            @indexname = index_name,
            @collist = [index_columns]
    FROM    @tt_ncidxs
    WHERE   rowid = @i;

    RAISERROR('Table %s column list: %s', 10, 1, @tablename, @collist) WITH NOWAIT;

    SET @cmd = 'SELECT @x_rowcount = COUNT(*) FROM ' + @tablename + ' WITH (NOLOCK);'
    EXEC sp_executesql @cmd, N'@x_rowcount INT OUTPUT', @x_rowcount = @rowcount OUTPUT;

    SET @cmd = 'SELECT @x_rowcount_d = COUNT(*) FROM (SELECT DISTINCT ' + @collist + ' FROM ' + @tablename + ' WITH (NOLOCK) ) x;'
    EXEC sp_executesql @cmd, N'@x_rowcount_d INT OUTPUT', @x_rowcount_d = @rowcount_d OUTPUT;

    RAISERROR('Index %s on table %s selectivity = %i/%i', 10, 1, @indexname, @tablename, @rowcount_d, @rowcount) WITH NOWAIT;

    IF (ISNULL(@rowcount, 1) = ISNULL(@rowcount_d, 2)) -- i.e. column values are (currently) distinct/unique
        UPDATE  @tt_ncidxs
        SET     is_unique = 1
        WHERE   rowid = @i;

    SET @i += 1;
END

-- Get a list of non-unique nonclustered indexes whose index columns are currently distinct.
-- These *may* be candidates for conversion to UNIQUE nonclustered indexes instead.
-- NB: PLEASE MAKE 100% SURE THAT THE COLUMNS ARE ALWAYS GOING TO BE UNIQUE BEFORE CREATING A UNIQUE INDEX
SELECT  *
FROM    @tt_ncidxs
WHERE   is_unique = 1
ORDER   BY [object_name], [index_id]

Cheers
DB Dave

Advertisements
%d bloggers like this: