Sql-server – Create an index without redundant code

indexsql serversql-server-2016t-sql

I need a simple index creation script in a stored procedure as part of my database project.

  • If the index in question already exists, I want to recreate it, to ensure that no other process has changed the index in question.
  • I want to use CREATE INDEX with the WITH (DROP_EXISTING = ON) option to recreate the existing index; so I can take advantage of the performance benefits Microsoft mentions.
  • If the index doesn't exist, I want to create it.
  • Using WITH (DROP_EXISTING = ON) when the index in question doesn't exist fails, so I cannot simply include that option without a check if the index exists or not.
  • All that said, I do not want a solution that requires the command to create the index be repeated; that could result in maintenance issues (someone edits the first version of the command but not the second, and I get different indexes depending on whether it already existed or not).

Here is some sample code. Ideally, this would be the code needed; however, it fails if the index does not exist:

CREATE NONCLUSTERED INDEX [IX_a]
                ON [dbo].[animals]([BioNr] ASC)
                INCLUDE([ID], [Currency]) WITH (DROP_EXISTING = ON);                 

This code works if the index doesn't exist, but doesn't replace the index if it does exist:

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
    CREATE NONCLUSTERED INDEX [IX_a]
                ON [dbo].[animals]([BioNr] ASC)
                INCLUDE([ID], [Currency]);      

This code works, but requires that the index creation code be in two places; that allows a future edit to break the intent of the script, resulting in a different index depending on whether it already existed or not:

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
BEGIN
        CREATE NONCLUSTERED INDEX [IX_a]
                ON [dbo].[animals]([BioNr] ASC)
                INCLUDE([ID], [Currency])  WITH (DROP_EXISTING = ON);      
END
ELSE
BEGIN
        CREATE NONCLUSTERED INDEX [IX_a]
                ON [dbo].[animals]([BioNr] ASC)
                INCLUDE([ID], [Currency]);      
END

Are there other options?

Best Answer

In SQL Server 2016, this is very easy, you just need to make a choice between having simple scripts or enjoying whatever performance you've actually observed from DROP_EXISTING (is this quantifiable? Have you tested it?).

CREATE TABLE dbo.what(i int, INDEX x(i));
GO

DROP INDEX IF EXISTS dbo.what.x;
GO

CREATE INDEX x ON dbo.what(i DESC);
GO