SQL Server Pro
Connect With Us
Home > Blogs > SQL Server Questions Answered > Why SQL Server Lets You Create Redundant Indexes
SQL Server Questions Answered

Why SQL Server Lets You Create Redundant Indexes

Oct. 18, 2010 by Paul S. Randal in SQL Server Questions Answered

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 <em>table</em> WITH (INDEX (<em>indexname</em>))
   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

sleakbug
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.
russellb
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.

paulrandal
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.
russellb
on Nov 6, 2010
If one wishes to disallow this, a database trigger would do the trick:
<br/>CREATE TRIGGER test ON DATABASE<br/>FOR CREATE_INDEX<br/>AS<br/><br/>SET NOCOUNT ON;<br/><br/>DECLARE @xmlEventData XML <br/>SET @xmlEventData = eventdata() <br/><br/>Declare @table sysname<br/>Declare @indexColumns table (col sysname)<br/>Declare @cmdText varchar(512)<br/>Declare @tempStr varchar(512)<br/>Declare @newIxColCount int<br/>Declare @oldIxColCount int<br/><br/>select @table =     Convert(sysname, @xmlEventData.query('data(/EVENT_INSTANCE/TargetObjectName)'))<br/>select @cmdText = Convert(varchar(512), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand)'))<br/><br/>--print @cmdText<br/><br/>SELECT @tempStr =<br/> LTRIM(RTRIM(REPLACE(REPLACE(<br/>       SUBSTRING(@cmdText,<br/>                charindex(@table, @cmdText) + len(@table),<br/>         charindex(')', @cmdText, charindex(@table, @cmdText) + len(@table))<br/>        ),<br/> '(', ''),<br/>  ')', '')<br/>   ));<br/>        <br/>WITH indexColumns(n, start, stop) AS (<br/>        SELECT 1, 1, CHARINDEX(',', @tempStr)<br/>      UNION ALL<br/>  SELECT n + 1, stop + 1, CHARINDEX(',', @tempStr, stop + 1)<br/>FROM     indexColumns<br/>WHERE  stop > 0<br/>)<br/>INSERT       @indexColumns<br/>SELECT LTRIM(RTRIM(SUBSTRING(@tempStr, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END))) FROM indexColumns<br/><br/>SELECT    @newIxColCount = @@ROWCOUNT<br/><br/>--select * from sys.columns WHERE object_id = object_id(@table)<br/>--SELECT * FROM sys.index_columns WHERE object_id = object_id(@table)<br/>--select * from @indexColumns<br/><br/>SELECT        @oldIxColCount = count(*)<br/>FROM      sys.columns c<br/>JOIN  sys.index_columns ic<br/>On     c.column_id = ic.column_id<br/>And      c.object_id = ic.object_id<br/>FULL OUTER JOIN<br/>     @indexColumns new<br/>On        new.col = c.name<br/>WHERE      c.object_id = object_id(@table);<br/><br/><br/>IF @newIxColCount * 2 = @oldIxColCount<br/>BEGIN<br/>    Declare @err varchar(312)<br/>  SET     @err = 'Index covering exact columns on [' + @table + '] already exists. Index creation failed'<br/>    RaisError (@err, 16, 1)<br/>    Rollback<br/>END<br/><br/>--print @oldIxColCount<br/>--print @newIxColCount<br/>GO<br/>

Please or Register to post comments.

Related Articles
What's SQL Server Questions Answered?

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

Contributors

Paul S. Randal

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

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...
View all Contributors
Blog Archive
SQLMag.com
Related Sites
Copyright © 2013 Penton Media, Inc