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

• CREATE INDEX statement: http://msdn.microsoft.com/en-us/library/ms188783.aspx

Until we meet next time,

Be courteous. Drive responsibly.