General MySQL Oracle SQL Server

DROP_EXISTING - Recreating an Existing Index in SQL Server

Q

How To Recreate an Existing Index in SQL Server?

✍: FYIcenter.com

A

If you want to change the definition of an existing index, you can use the "DROP INDEX" statement to drop the index first. Then use the "CREATE INDEX" statement to create it again with the new definition.

But you can also combine those two statements into one: 

CREATE INDEX ... WITH (DROP_EXISTING = ON)

The tutorial exercise below recreates fyi_links_url with a change to index columns: 

USE FyiCenterData;
GO

CREATE INDEX fyi_links_url ON fyi_links_indexed (url, counts)
   WITH (DROP_EXISTING = ON);
GO

SP_HELP fyi_links_indexed;
GO
index_name        index_description                index_keys 
---------------- -------------------------------- ------------
fyi_links_counts  nonclustered located on PRIMARY  counts
fyi_links_url     nonclustered located on PRIMARY  url, counts

 

⇒ Understanding and Managing Views in SQL Server

⇐ Rebuilding All Indexes on One Table in SQL Server

⇑ Understanding and Managing Indexes in SQL Server

⇑⇑ SQL Server Database Tutorials

2016-11-08, 2261👍, 0💬

General MySQL Oracle SQL Server