Why SQL Server Lets You Create Redundant Indexes

Q: Why does SQL Server let me create the same (i.e., completely identical) index more than once?

A: I agree, this behavior is annoying, and if it were completely up to me, I wouldn't allow it. At a minimum, I wish there were a configuration option that you could turn on that would prevent this behavior from happening. However, I do understand the backward compatibility reason for this behavior.

If an index is explicitly named in a hint like

   SELECT … 
   FROM table WITH (INDEX (indexname)) 
   WHERE …

the query would fail if the index were dropped, which could break existing applications. I know that index hints aren't generally recommended, but they still must be supported and work. Therefore, SQL Server can’t drop an existing (and duplicate) index. But, why does SQL Server allow new (and duplicate) indexes to be created? Again, you could make the argument that scripts could have these problems in them already and code could be written to use these index "hints." So, ultimately, it’s been a bad practice in the past and to make sure that no applications are broken, the bad practice needs to be supported now. Don’t get me wrong, I don’t think the practice should be allowed, but that’s the reason it is.

Discuss this Blog Entry 5

on Dec 30, 2010
Is there any other mechanism that can be exploited in SQL 2008 (not R2 specifically) that could be used to prevent dupes? I regularly use a script to add missing indexes and would like to "lock down" the index creation to eliminate unintentional index and statistic dupes from tuning and manual operations by the DBA and developer teams?
Thanks! You folks are my SQL heroes!

Robert L Davis (not verified)
on Oct 21, 2010
Perhaps adding a warning would be an improvement.
on Nov 6, 2010
Right. Thanks Paul. That's something I forgot about until I realized that my rowcounts for sys.index_columns was twice what I'd expected.

Naturally I tested on an empty table. The overhead of creating and rolling back the index might not be acceptable on large tables.

on Nov 6, 2010
The problem with using a DDL trigger is that it is an AFTER trigger, not an INSTEAD OF trigger. So although a trigger would 'prevent' this, the index would still be created, and then it would be rolled-back.
on Nov 6, 2010
If one wishes to disallow this, a database trigger would do the trick:


DECLARE @xmlEventData XML
SET @xmlEventData = eventdata()

Declare @table sysname
Declare @indexColumns table (col sysname)
Declare @cmdText varchar(512)
Declare @tempStr varchar(512)
Declare @newIxColCount int
Declare @oldIxColCount int

select @table = Convert(sysname, @xmlEventData.query('data(/EVENT_INSTANCE/TargetObjectName)'))
select @cmdText = Convert(varchar(512), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand)'))

--print @cmdText

SELECT @tempStr =
charindex(@table, @cmdText) + len(@table),
charindex(')', @cmdText, charindex(@table, @cmdText) + len(@table))
'(', ''),
')', '')

WITH indexColumns(n, start, stop) AS (
SELECT 1, 1, CHARINDEX(',', @tempStr)
SELECT n + 1, stop + 1, CHARINDEX(',', @tempStr, stop + 1)
FROM indexColumns
WHERE stop > 0
INSERT @indexColumns
SELECT LTRIM(RTRIM(SUBSTRING(@tempStr, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END))) FROM indexColumns

SELECT @newIxColCount = @@ROWCOUNT

--select * from sys.columns WHERE object_id = object_id(@table)
--SELECT * FROM sys.index_columns WHERE object_id = object_id(@table)
--select * from @indexColumns

SELECT @oldIxColCount = count(*)
FROM sys.columns c
JOIN sys.index_columns ic
On c.column_id = ic.column_id
And c.object_id = ic.object_id
@indexColumns new
On new.col = c.name
WHERE c.object_id = object_id(@table);

IF @newIxColCount * 2 = @oldIxColCount
Declare @err varchar(312)
SET @err = 'Index covering exact columns on [' + @table + '] already exists. Index creation failed'
RaisError (@err, 16, 1)

--print @oldIxColCount
--print @newIxColCount

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.


Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×