It seems like this should be easy. But it’s not.

Code Should be Rerunnable - So You Need to Check if Indexes Exist

Whenever you set up a script to create or drop an index, you want a safety check in there. Drop the index if it exists. Create the index if it doesn’t.

Or do something else programmatically. Checking if an index exists is a pretty frequent task. But there’s no simple function to test if an index exists in SQL Server.

Here’s what I’ll show you in this post:

• Example code to check if an index exists using OBJECT_ID. The code is simpler, but it requires a shared schema lock on the table you’re checking.
• Example code to check if an index exists just using joins. This uses less locking, but is wordier code.
• Examples of why simpler code doesn’t do the trick.
• Why the DROP_EXISTING=ON clause in CREATE INDEX doesn’t make this any simpler, either.
• New syntax in SQL Server 2016 simplifies code for dropping indexes, but we’re still on our own when it comes to checking before creating an index, or for writing reports.

Our Example Index: ix_halp

We want to know if an index named ix_halp exists on the table agg.FirstNameByYear in the SQLIndexWorkbook database– now renamed to BabbyNames. It does! It was created with this code:

CREATE NONCLUSTERED INDEX ix_halp
    ON agg.FirstNameByYear (ReportYear)
INCLUDE (FirstNameId, Gender, NameCount);
GO

Let’s say we’re running code that is going to create the index if it does NOT exist. If we just re-run the create statement, we get the error:

Msg 1913, Level 16, State 1, Line 1 The operation failed because an index or statistics with name ‘ix_halp’ already exists on table ‘agg.FirstNameByYear’.

We have to check if the index exists, or our code will blow up.

Sometimes you have to go all Game of Thrones on an index.

Option 1: Query sys.indexes with the OBJECT_ID() Function

This method functions well, but it uses the OBJECT_ID() function. OBJECT_ID() will take out a shared schema lock (SCH_S) on the table we pass into it. Shared schema locks are very lightweight, but it is kind of a bummer to have to lock the table itself if we’re just querying metadata. It’s not always an issue, but I’ve seen scripts cause problematic blocking by using OBJECT_ID too much.

Here’s two flavors of the code. First, using COUNT:

IF 1 = (SELECT COUNT(*) as index_count
    FROM sys.indexes 
    WHERE object_id = OBJECT_ID('agg.FirstNameByYear')
    AND name='ix_halp')
PRINT 'it exists!'
ELSE PRINT 'nope';
GO

Second, a simple existence check:

IF EXISTS (
    SELECT 'foo'
    FROM sys.indexes 
    WHERE object_id = OBJECT_ID('agg.FirstNameByYear')
    AND name='ix_halp')
PRINT 'it exists!'
ELSE PRINT 'nope';
GO

These both do 2 logical reads and are very fast.

Option 2: Query sys.indexes, sys.objects, and sys.schemas (Fewer Locks)

If you want to avoid the SCH_S lock against the table whose indexes you’re checking for some reason, you just need a couple more joins.

Here’s an option using COUNT:

IF 1 = (SELECT COUNT(*) as index_count
    FROM sys.indexes AS si
    JOIN sys.objects AS so on si.object_id=so.object_id
    JOIN sys.schemas AS sc on so.schema_id=sc.schema_id
    WHERE 
        sc.name='agg' /* Schema */
        AND so.name ='FirstNameByYear' /* Table */
        AND si.name='ix_halp' /* Index */)
PRINT 'it exists!'
ELSE PRINT 'nope';
GO

Same thing, but using EXISTS:

IF EXISTS (
    SELECT 'foo'
    FROM sys.indexes AS si
    JOIN sys.objects AS so on si.object_id=so.object_id
    JOIN sys.schemas AS sc on so.schema_id=sc.schema_id
    WHERE 
        sc.name='agg' /* Schema */
        AND so.name ='FirstNameByYear' /* Table */
        AND si.name='ix_halp' /* Index */)
PRINT 'it exists!'
ELSE PRINT 'nope';
GO

These do 11 or 12 logical reads against the internal metadata tables.

Don’t Try This: OBJECT_ID() Doesn’t Work

It seems like an index might be considered an object in SQL Server. But it’s not.

We might try this simple code, but it won’t work:

SELECT OBJECT_ID(agg.FirstNameByYear.ix_halp);

This throws an error, because it doesn’t  understand schema.table.indexname:

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier “agg.FirstNameByYear.ix_halp” could not be bound.

Similarly, this code won’t be accurate:

SELECT COUNT(*)
FROM sys.objects
WHERE name='ix_halp';
GO

Even if the index exists, it will return a count of 0. The index ‘ix_halp’ doesn’t have a row in sys.objects.

 Bummer: CREATE INDEX WITH (DROP_EXISTING = ON) Fails if the Index Doesn’t Exist

It seems like there’s an easy shortcut if you’re creating indexes. There isn’t. If our index does not exist yet and we run this:

CREATE NONCLUSTERED INDEX ix_halp
    ON agg.FirstNameByYear (ReportYear)
INCLUDE (FirstNameId, Gender, NameCount)
WITH (DROP_EXISTING = ON);
GO

Our create will fail sadly with this error:

Msg 7999, Level 16, State 9, Line 1 Could not find any index named ‘ix_halp’ for table ‘agg.FirstNameByYear’.

The DROP_EXISTS clause is only for changing the definition of an index that already exists.  This has NOT changed in SQL Server 2016.

SQL Server 2016 Gives Us ‘DROP INDEX IF EXISTS’

At least index cleanup gets syntactically easier in SQL Server 2016: DROP INDEX gets a new conditional clause to check for existence.

Behold:

DROP INDEX IF EXISTS agg.FirstNameByYear.ix_halp;
GO

Whee! At least that’s nice and easy.