How To Recreate an Existing Index in MS SQL Server?
Submitted by: AdministratorIf 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 ggl_links_url with a change to index columns:
USE GlobalGuideLineDatabase;
GO
CREATE INDEX ggl_links_url ON ggl_links_indexed (url, counts)
WITH (DROP_EXISTING = ON);
GO
SP_HELP ggl_links_indexed;
GO
<pre>index_name index_description index_keys
---------------- -------------------------------- ------------
ggl_links_counts nonclustered located on PRIMARY counts
ggl_links_url nonclustered located on PRIMARY url, counts</pre>
Submitted by: Administrator
But you can also combine those two statements into one:
CREATE INDEX ... WITH (DROP_EXISTING = ON)
The tutorial exercise below recreates ggl_links_url with a change to index columns:
USE GlobalGuideLineDatabase;
GO
CREATE INDEX ggl_links_url ON ggl_links_indexed (url, counts)
WITH (DROP_EXISTING = ON);
GO
SP_HELP ggl_links_indexed;
GO
<pre>index_name index_description index_keys
---------------- -------------------------------- ------------
ggl_links_counts nonclustered located on PRIMARY counts
ggl_links_url nonclustered located on PRIMARY url, counts</pre>
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Execute the Cursor Queries with "OPEN" Statements? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | How To Convert a Numeric Expression from One Data Type to Another? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Create a Large Table with Random Data for Index Testing in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |