Identifying unique indexes that aren’t defined as unique

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

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top