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
So, your requirements are:
- Create all indexes on the list, dropping and replacing existing indexes.
- If the index does exist, use
WITH (DROP_EXISTING = ON).
- No repetition of the code to create the index.
I only see two options that would do this:
Option 1: Dynamic SQL
Build the basic
CREATE INDEX command; then, if the index exists, tack on the
DECLARE @stmt NVARCHAR(MAX); SET @stmt = N' CREATE NONCLUSTERED INDEX [IX_a] ON [dbo].[animals]([BioNr] ASC) INCLUDE([ID], [Currency])' + CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a') THEN N' WITH (DROP_EXISTING = ON)' ELSE N'' END + N';' ; EXECUTE sp_executesql @stmt;
Obviously, this solution has some issues of its own. In general, you have to maintain the statements as strings. The biggest issues here are that you have to remember to double any single quotes that should be in the statement. As the sample statement has none, this may not turn out to be an issue, but it must be noted. In particular, the statement must be altered if need other
WITH options set. Again, in this example, that’s not an issue. If all statements did have existing
WITH options to include, it would be a huge issue either (though the statement would need tweaked:
SET @stmt = N' CREATE NONCLUSTERED INDEX [IX_a] ON [dbo].[animals]([BioNr] ASC) INCLUDE([ID], [Currency]) WITH (DATA_COMPRESSION = PAGE' + CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a') THEN N', DROP_EXISTING = ON' ELSE N'' END + N');' ;
Option 2: create a “dummy” index
Note that I wouldn’t recommend this; it’s actually at least as hard to maintain as simply having two copies of the
CREATE INDEX statement, and causes extra work on the system. However, if you would expect that the index would almost always be there, it might be useful.
First, you create an index with the name you want (the columns don’t matter); then, replace it with the index you actually want.
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); CREATE NONCLUSTERED INDEX [IX_a] ON [dbo].[animals]([BioNr] ASC) INCLUDE([ID], [Currency]) WITH (DROP_EXISTING = ON);
As I said, I wouldn’t recommend it, but it would (technically, at least) meet your requirements, so I thought I should mention it.
It’s probably worth posting a
CONNECT item, requesting that
DROP_EXISTING = ON be allowed whether the index exists or not (if you can’t find an existing one, at least). It seems entirely reasonable for the option to simply be ignored if the index doesn’t exist.
I didn’t have a simple approach to this; instead I wrote a “master” program [BuildIndexes] which takes a table name and an index/statistic name term (default ‘%’), then for the table calls procedure [BuildIndexes_NameOfThisTable] which contains all the CREATE statements for indexes and statistics on t able [NameOfThisTable]. Well, likely to be table [B1_001].[dbo].[NameOfThisTable_01_2016], which is more worthwhile.
My program (which is currently private) finds and drops any matching-name indexes on the table first; an alternative for you would be to include a comment-type token /wde/ inside the index statement, which of course is contained in a string called using [sp_executesql], and if the index exists then – optionally – replace /wde/ with ‘WITH (DROP_EXISTING = ON)’. In my case, a reason to not do that (maybe?) was that I might have decided to change the index definition, then run the procedure to apply the change.