#0350 – SQL Server – CREATE INDEX – DROP_EXISTING option and Msg 7999


Normally, when we need to perform maintenance operations on any database object as part of a database deployment, we would first check for it’s existence. If the object exists, the query would be required to first drop the object and then recreate it (or simply modify it).

For indexes, this would result in a script that is similar to the one shown below:

--Creating Indexes (the traditional/common way)
IF EXISTS (SELECT * 
           FROM sys.indexes AS si 
           WHERE si.name = 'ncidx_ProductName')
    DROP INDEX ncidx_ProductName ON dbo.Product;
GO

CREATE UNIQUE NONCLUSTERED INDEX ncidx_ProductName
       ON dbo.Product (ProductName);
GO

However, as I was reading the Books On Line the other day, I noticed an interesting option in the CREATE INDEX statement – the DROP_EXISTING option. Here’s an example demonstrating the usage of this option:

--Creating Indexes using the DROP_EXISTING option
CREATE UNIQUE NONCLUSTERED INDEX ncidx_ProductName 
    ON dbo.Product (ProductName) WITH (DROP_EXISTING = ON);
GO

DROP_EXISTING allows us to request the database engine to drop and rebuild the index with the new definition. However, the index must exist before the DROP_EXISTING can be used. If DROP_EXISTING is used with the CREATE INDEX statement for an index that does not exist, the following error is seen.

Msg 7999, Level 16, State 9, Line 2
Could not find any index named ‘ncidx_ProductName’ for table ‘dbo.Product’.

Summary

Although I am yet to use the DROP_EXISTING option in my maintenance operations, it does appear to be lucrative due to the reduced number of lines involved in recreating the index. A couple of points need to be kept in mind, though:

  • The index must be existing when the DROP_EXISTING option is used
  • We can use DROP_EXISTING when changing the definition of the index
  • Index type (clustered to non-clustered or vice-versa) cannot be changed when DROP_EXISTING is used

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

One thought on “#0350 – SQL Server – CREATE INDEX – DROP_EXISTING option and Msg 7999

  1. Jay Sanati

    IMHO, DROP_EXISTING should try to drop the index if it exists. If the index is not there it should just ignore it. This is another strange thing in Microsoft products.

    Liked by 1 person

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s