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
