# 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?

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));