How to Check if an Index Exists on a Table in SQL Server

counting-indexes-dragon

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

index-being-burned-by-dragon-fire

Sometimes you just 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.

Previous Post
How to Fix Lopsided Partitioned Tables
Next Post
Sliding Window Table Partitioning: What to Decide Before You Automate

Related Posts

19 Comments. Leave new

Kendra, great post (again).

I have a question, It’s a bit off topic (if you think that this discussion doesn’t belong here, feel free to remove my comment, no heart feelings).

From time 2 time I need to deal with indexes created by Tuning Advisor “experts”, that don’t bother to look if there indexes already covering those columns.

Here is the question: any chance of a post about overlapping indexes (different indexes covering the same columns) and how they can affect performance?

Reply

Hey Kendra, I am one of those Accidental DBAs so this may be common knowledge but, how would you determine the existence of an index if you do not know the name of the index? I would want to know whether an index exists on a table and the name of it.

Leonard

Reply

    Great question!

    For a super quick and simple way to query the index names on a table in TSQL, this query will do it:

    SELECT
    si.index_id,
    si.name as index_name
    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 */;
    GO

    I’ll publish a post later this week in a few weeks with more detail on ways to script out the index definitions and sizes, including gotchas about filters, compressions, and partition settings that might be sneaking around in there.

    Reply

[…] Kendra Little talks about index discovery: […]

Reply

Hi Kendra

Nice post… Another drawback for DROP_EXISTING is that if you cancel the query that creates the index, the existing one (assuming one existed), is dropped.

Reply

IF INDEXPROPERTY(OBJECT_ID(‘[dbo].[T_RECON_DIFF]’), ‘NCI_T_RECON_DIFF_ReconDate’, ‘IndexID’) IS NULL
BEGIN
CREATE NONCLUSTERED INDEX [NCI_T_RECON_DIFF_ReconDate] ON dbo.T_RECON_DIFF(ReconDate);
END
GO

Reply

Like the website and like the Dinosaur Roar storybook reference!!

Reply

Bookmarked this page a long time ago, but now have a Blocking Situation that I wonder if OBJECT_ID() is responsible for…

This is on SQL 2016 Standard SP1 CU2, in case that matters…

We run a 5-wide reader-process from our App Server (each session being autonomous, firing at-will from the App to the DAL to SQL with a payload request).

We’re noticing a bunch of blocking in an SP that doesn’t have SELECT…INTO #Temp… or ALTER TABLE #Temp, but it has an object-existence IF statement that always runs:

IF OBJECT_ID(‘tempdb..#FullChainInventory’) IS NULL

Given the target table is the same name across all 5 threads, could SQL’s temp object caching be getting in the way due to the Shared Schema Lock you spoke of? Given a bit of latitude, I can try to persuade the powers that be to allow me to replace them with the schema-objects or even a schema-tables JOIN, but there’s always red-tape…

Your thoughts?

Thanks!

Reply

    Hi Stephen,

    Why check for the object existence at all? My guess is that the temp table may also be dropped at the end of the procedure — this just results in a renaming dance in tempdb behind the scenes. More info in the “CREATE and DROP, Don’t” section here: http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

    How long is the duration of the blocking when it happens?

    Reply
      Stephen Anslow
      October 27, 2017 11:11 am

      Thanks for taking the time to respond to an old thread, Kendra.

      These are two temp tables created by the calling Proc, so instead of a SQL nasty red message or a TRY-CATCH block, the code checks for the existence of each one, generating its own nasty-red message via RAISERROR with State 17 – typical of this Solution for the last 15 years.

      Blocking this time was 4 seconds. Although not long in some folks’ eyes, the wait type was LCK_M_IX and the lead blocker and blocked were all the same procedure. The only schema-level operations in the code are 2 OBJECT_ID() checks for existence. Schema-level blocking concerns me especially, because the process is one of the most time-critical, run hundreds, if not thousands, of times per Daily Run, so blocking-elimination is really high on the hit-list.

      I’ve hunted for other potential blocking constructs in the code, but we only use temp tables as targets of INSERT/UPDATE and persisted tables are read-only. There are no Global temp tables either.

      The database uses read_committed_snapshot at the DB level to obviate the reader/writer-blocking issue pre SQL 2005 (acknowledged there are a few nuances to RCS but we haven’t hit them as far as we know…)

      Hence the grasping at straws type question re temp object caching…

      Reply

        No worries. Thanks for the interesting case!

        The long blocking duration is a little puzzling to me. Setting up a very simple attempt at repro, I can generate some blocking if I make the procedure execute very very quickly — like 1,200 executions per sec from each session. But it’s just occasional 1ms or 2ms LCK_M_X and LCK_M_S waits– and the predominant waits are PAGELATCH_ types.

        As soon as I add in some data access and the execution goes to around 30/sec on each session, then these lock waits don’t appear.

        So there’s something I’m missing in the repro.

        How long does your calling proc take to run on average? Is it inserting into the temp table in both the outer/calling proc and the inner proc? Are there updates or deletes against the temp tables? Are there indexes created on the temp tables at any point (and if so, when)?

        Reply
          Stephen Anslow
          October 27, 2017 1:31 pm

          One of the tables is pre-loaded in the caller with sometimes 1, or up to 4 of the 6 columns populated. Usually no Indexes on that table… The called proc does a bit of sanity DELETE based on a 2-column match, then goes and tries to set 2 “key” columns (SiteID and ItemMasterID) because SOME callers haven’t set them (though they could).There are additional INSERTs and UPDATEs, but no indexes are created WITHIN the called proc. Just one of the calling procs defines the temp table’s InventoryID as a PK as part of the CREATE TABLE itself. It happens that Solarwinds informs of the calling Proc for the blocked SPIDs, but it doesn’t give me the caller for the lead blocker! (rats!). As I know the workload, it is highly possible that the “Initialize” proc, that creates WITH the PK, is the lead blocker.

          The 2nd one is also a HEAP that sustains CUD actions. None of the callers, or the called, creates any index, before, during or after the called proc EXEC.

          Duration is somewhat variable! It’s dependent upon how the calling App (a C# Replenishment “Engine”) is configured. The item count can vary from 500 to 5000 or more, and the supply chain rules are also configurable… So how long is my piece of string? Depends on configurations and the sheer power of the SQL Server! I wish I could answer the “how long” question! There’s no logging to tell me, so I’d have to find a “gimme average duration for this proc” T-SQL query. I will readily admit that due to the 20 Production servers we look at via Solarwinds, we are “exception” driven, and tool-reliant. If the blocked proc doesn’t figure as a top-40 entrant, we don’t see its metrics in Solarwinds, and rarely have time to go back to basics with targeted T-SQL. (Confessions of a 1-person “performance monitor” here endeth!) As suspected, unless I can get to the actual blocked STATEMENT in the Proc, Solarwinds gives me precious little to work with.

          Thanks again for your willingness to delve… I think we can only surmise what may have occurred as the precise resource the LCK_M_IX is waiting for isn’t available.

          Until next time…

          Do you have the overall wait stats from the time period it occurred, and just before and after?

          Like you’re saying, it would be great to figure out the wait stats for the block leader. It sounds like you don’t have the granular data for the block leader, but if you have the overall server level wait stats and there’s a wait in there like THREADPOOL or RESOURCE SEMAPHORE, then that information could also help lead to an educated guess about what might be stalling the lead of the blocking chain.

          Stephen Anslow
          October 30, 2017 5:42 pm

          Zero THREADPOOL or RESOURCE_SEMAPHORE for the timeslice, which in Solarwinds DPA is just the 10 minutes, on a minutely slice, at the time the blocking occurred.

          That “exact resource” that blocking is caused by is so elusive… Neither SQL Monitor nor Solarwinds DPA do a decent job of revealing that information.

          Aside from memory/CPU (which is good, at 1h16m – 4600 seconds), all I have are:

          FT_IFTSHC_MUTEX, HADR_FILESTREAM_IOMGR_IOCOMPLETI (Odd, because there’s ZERO HA configured!), SQLTRACE_INCREMENTAL_FLUSH_SLEEP, WAIT_XTP_HOST_WAIT – each of these 4 incurred 600~ seconds for the 10 minute slice.

          Other wait types then drop to:
          350 seconds for CXPACKET
          300 for BROKER_TO_FLUSH
          250 for EC (Seems that Microsoft is using this for the Buffer Pool Extension, which we have configured)
          200 for PREEMPTIVE_OS_GETPROCADDRESS
          and then precipitously
          PAGEIOLATCH_SH at a whopping 32 seconds, _EX at 26 seconds
          PAGELATCH_UP 11
          WRITELOG 10
          and our target LCK_M_X (there are a mixture of _X and _IX but _X gets the 5 seconds nod).

          Not much of anything to go on, it seems.

          Chalking it up to “experience”. May go with a TRY-CATCH around the first operation on the temp input table, just for an experiment…

          Cheers!

Dylan Nicholson
July 3, 2019 5:37 pm

DROP INDEX IF EXISTS agg.FirstNameByYear.ix_halp sounds expensive though – if the index is already there and potentially would take a long time to rebuild, why would you drop and recreate?

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu