SQL Index Creation Using DROP EXISTING ON

When you are making changes to an existing Non-Clustered index SQL Server provides a wide variety of options. One of the more frequently used methods is DROP EXISTING; in this post you will learn all about that option. This option automatically drops an existing index after recreating it, without the index being explicitly dropped. Let us take a moment understand the behavior of this choice.

 

DROP EXSITING=ON

 

Which is my preferred method, will DROP the current index only after it finishes creating and building the index with the new definition. The pitfall is that if the index does not exist, you will get an error and must create it without the option or set it to OFF. However, the more important benefit of using this one is all about performance. The index will still be used by active queries until it is rebuilt with the new definition.

CREATE NONCLUSTERED INDEX [dcacIDX_ServiceType] ON [dbo].[Accounts]
(
   [ServiceType] ASC
)
INCLUDE([AccountId]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = ON, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

If index does not exist, you will get a 7999 error.

 

Msg 7999, Level 16, State 9, Line 1

 

Could not find any index named 'dcacIDX_ServiceType' for table 'dbo.Accounts'.

 

There are a few exceptions to keep in mind per docs.microsoft.com.

 

With DROP_EXISTING, you can change,

• A nonclustered rowstore index to a clustered rowstore index.

With DROP_EXISTING, you cannot change,

• A clustered rowstore index to a nonclustered rowstore index.
• A clustered columnstore index to any type of rowstore index.

DROP and CREATE

 

This option is a cleaner and wont error if the index doesn’t already exist. However, I caution you when using this especially when it is a large table. Using this option drops the index before it creates the new, leaving your system without the previous index definition. This can create a huge performance issue while the system waits for the new index to be created. I know this firsthand, as I did this with a client a few years ago, during the day while trying to fix a performance issue. I created a worse issue while the waiting for the new one to be created. It took 45 mins to create the new index with the new definition which caused CPU to spike to 100% while active queries were trying to come through. Which sadly, in turn, slowed down the new index creation.

DROP INDEX IF EXISTS [dcacIDX_ServiceType] ON [dbo].[Accounts]
GO
CREATE NONCLUSTERED INDEX [dcacIDX_ServiceType] ON [dbo].[Accounts]
(
   [ServiceType] ASC
)
INCLUDE([AccountId] WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Now I should also note that the DROP_EXISITING method is also faster when you must modify a Clustered index. Every Non-Clustered index refers to the Clustered index using what is called a clustering key, essentially, a pointer to the row in the clustered index. When a clustered index is dropped and re-created, SQL Server must rebuild the Non-Clustered indexes on that table. In fact, it gets done twice by actually rebuilding them upon drop and rebuild again on the create of the Clustered index. Using DROP_EXISTING=ON prevents you from having to rebuild all these indexes as their keys will stay the same, thus making it significantly faster.

 

The reason I took the time to write this quick blog is to remind those to consider using the DROP EXSITING=ON rather than using the DROP and CREATE method when possible. Do not introduce a performance issue when you can avoid it and you can more efficiently make the desire changes you need. Just a friendly reminder.