Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) Mar 13 2020 14:53:45 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
I am trying to understand the behavior of CREATE INDEX WITH (DROP_EXISTING = ON).
I currently have a very active table with approximately 45 million rows. There is NO clustered index on this table.
There is a non-clustered index that currently looks like this:
CREATE NONCLUSTERED INDEX IX_NC_01 on my_table (Col1);
I would like to modify the index by adding an include clause:
CREATE NONCLUSTERED INDEX IX_NC_01 on my_table (Col1) Include (Col2) WITH (DROP_EXISTING = ON, ONLINE = ON)
I want this action to be as non-intrusive as possible.
I have read this snip from the documentation:
The DROP_EXISTING clause will not sort the data again if the same index key columns are used in the same order and with the same ascending or descending order, unless the index statement specifies a nonclustered index and the ONLINE option is set to OFF. If the clustered index is disabled, the CREATE INDEX WITH DROP_EXISTING operation must be performed with ONLINE set to OFF. If a nonclustered index is disabled and is not associated with a disabled clustered index, the CREATE INDEX WITH DROP_EXISTING operation can be performed with ONLINE set to OFF or ON.
Will the old version of the index be available while the statement is running?
Please explain the interaction between ONLINE=ON and DROP_EXISTING=ON. My understanding of this is that I should include both clauses.
Will I increase locking/blocking while the statement is running?
Thank you for your help.