February 14, 2009 at 10:16 am
CREATE TABLE Emptab (emp_id int,
emp_nmae varchar(10),
sal int,
emp_sales int)
Assume that I have clustered index clus_indx1(emp_id) on Emptab and two non clustered indexes non_clu1 (sal), non_clu2(emp_sales).
Now I created a clustered index on Table1 with DROP_EXISTING option as below
CREATE CLUSTERED INDEX clus_indx1 ON Emptab(emp_id)
WITH FILLFACTOR = '10' , DROP_EXISTING
Note : I am not chating the clustered index columns or index name
Here my question is
1) When I create a clustered index with DROP_EXISTING option, Do I need to recreate all the non clustered indexes on the table.
2) When I create a clustered index with DROP_EXISTING option, Do I need to recreate all the non clustered indexes only when the clustered index columns changed.
BOL Says as below
DROP_EXISTING
Specifies that the named, preexisting clustered or nonclustered index should be dropped and rebuilt. The index name specified must be the same as a currently existing index. Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account.
The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes. The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.
If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted.
Rajesh Kasturi
February 16, 2009 at 1:49 am
1. no you don't need to recreate secondary indexes - I'd assume you'd have in place a routine to update stats, rebuild as required ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply