When re-building a table's clustered index using a DROP INDEX command followed by a CREATE INDEX command, if a table has non-clustered indexes then all of non-clustered indexes on the table may be automatically rebuilt twice by the SQL Server engine. Once when the clustered index is dropped and a second time when the index is created again, depending on the new clustered index definition. Is there some way we can eliminate the system resource overhead of rebuilding all of the table's non-clustered indexes twice?
Solution
To efficiently rebuild a clustered index, the CREATE INDEX command provides the DROP_EXISTING option. This option can rebuild the clustered index in a single atomic step and re-creates the non-clustered indexes of the table only once depending on the index definition. This sets each index leaf level page pointer to the clustered index key while eliminating the overhead of setting the index page leaf pointer to a direct address first. On very large tables, the resource savings can be immense.
As a point of reference, each index leaf level page of a non-clustered index contains a pointer to where the data requested resides. This pointer can be one of two values. For tables without a clustered index, this pointer is a direct address to a data page. For tables with a clustered index, this value is a pointer to the clustered index key related to the data. When a table's clustered index is dropped using a DROP command and then re-added using a CREATE command, each non-clustered index on the table can be re-created twice. When the DROP is initiated, all non-clustered indexes on the table are re-created to reset its index page pointers to directly address a data page. When the CREATE is then executed, the non-clustered indexes are re-created again, this time with a pointer set back to a clustered index key.
As is the case with many features related to SQL Server 2005, they have been enhanced over the SQL Server 2000 functionality. Let's dive into some of the small details related to the DROP_EXISTING option of the CREATE INDEX command between SQL Server 2000 and 2005:
The DROP_EXISTING option can also be used in the following scenarios:
SQL Server 2000
SQL Server 2005
Rebuild a preexisting clustered index
Rebuild a preexisting non-clustered index
Rebuild a non-clustered index as a clustered index if a clustered index is not already defined
Rebuild an index and add or remove the keys
Rebuild an index to add or remove uniqueness
Rebuild a preexisting clustered index
Rebuild a preexisting non-clustered index
Rebuild a non-clustered index as a clustered index if a clustered index is not already defined
Rebuild an index and add or remove the keys
Rebuild an index to add or remove uniqueness
Rebuild an index to modify options
Rebuild an index to modify sort order
Rebuild an index to modify the partition scheme or filegroup
If an index enforces a PRIMARY KEY or UNIQUE constraint and the definition is not alerted, then the index can be dropped and re-created
If the index definition has the same name, key and partition columns, uniqueness attributes and sort order, then the non-clustered indexes are not rebuilt
Limitations to using DROP_EXISTING option:
SQL Server 2000
SQL Server 2005
The index must already exist; using this option will not allow creation of an index from scratch
You cannot use it to rebuild a clustered index to be non-clustered
If rebuilding an index tied to a constraint, you cannot change the index's definition
The index must already exist; using this option will not allow creation of an index from scratch
You cannot use it to rebuild a clustered index to be non-clustered
If rebuilding an index tied to a constraint, you cannot change the index's definition
The non-clustered indexes will always remain in the original file group or partition scheme with the original partition functions even if the clustered index is rebuilt to another file group or partition
If an index enforces a PRIMARY KEY or UNIQUE constraint and the definition is alerted, then drop the constraint and add the constraint with the new definition
Here is an example of the DROP_EXISTING option of the CREATE INDEX command in action:
Sample DROP_EXISTING Code
SQL Server 2000
SQL Server 2005
CREATE CLUSTERED INDEX au_id_clidx ON Authors (au_id) WITH DROP_EXISTING GO
CREATE CLUSTERED INDEX au_id_clidx ON dbo.Authors (au_id) WITH (DROP_EXISTING = ON); GO
Next Steps
Examine your scripts to see where you can benefit from using the DROP_EXISTING option over DROP/CREATE statements.
To answer the old question posed by Mushtaq back on March 21, 2013, which basically asked how to "create a Primary Key Clustered Iwith DROP EXISTING" (and is a great question), please refer to the following Microsoft Documentation link...
Remember first that contraints like Primary Keys always cause an index to materialize to enforce the constraint. Then, check the link I posted and find where it states...
"If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint."
In plain English, you can't create a new Primary Key constraint using CREATE INDEX and that means that you can't use WITH DROP EXISTING when you create a constraint like a Primary Key. You CAN, however, drop and rebuild the index that supports such a constraint with CREATE INDEX WITH DROP EXISTING (remember that a PK is unique and cannot be null and the index must be recreated just like that).
Also be aware that you cannot actually redefine an index that enforces a constraint. You must first drop the constraint and then rebuild the constraint. That's also documented in the same paragraph in the article.
As you mentioned, the drop_existing can be used for : If an index enforces a PRIMARY KEY or UNIQUE constraint and the definition is not alerted, then the index can be dropped and re-created. I wonder what would be the syntax to 'Create a primary key clustered index WITH DROP_EXISTING'
As we have to you Alter table to add or remove primarky key and Drop_existing cannot be used with Alter table. How can i use Drop_existing when primary key is involved.
Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.