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’.
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
CREATE INDEX statement: http://msdn.microsoft.com/en-us/library/ms188783.aspx
Until we meet next time,
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.
LikeLiked by 1 person