Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dropping/Recreating Clustered Index Expand / Collapse
Author
Message
Posted Tuesday, October 01, 2013 12:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:29 PM
Points: 63, Visits: 216
Hi SSC,

I've got a table with a clustered index which needs to be dropped and recreated to add an additional column to it. The table also has three non-clustered indices on it. I seem to recall reading somewhere that to do that, I should either drop or disable the non-clustered indices beforehand, but I can't remember exactly why or which one. Can anyone shed some light on the best practice here?


Executive Junior Cowboy Developer, Esq.
Post #1500544
Posted Tuesday, October 01, 2013 12:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 11,933, Visits: 10,970
Gabe T. (10/1/2013)
Hi SSC,

I've got a table with a clustered index which needs to be dropped and recreated to add an additional column to it. The table also has three non-clustered indices on it. I seem to recall reading somewhere that to do that, I should either drop or disable the non-clustered indices beforehand, but I can't remember exactly why or which one. Can anyone shed some light on the best practice here?


Read about it here. http://technet.microsoft.com/en-us/library/ms190691%28v=sql.105%29.aspx

The section at the top under clustered indexes explains the performance implications of dropping indexes.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1500547
Posted Tuesday, October 01, 2013 12:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:29 PM
Points: 63, Visits: 216
Ah, that's right. All the non-clustered indexes would end up being re-built essentially twice; once after the drop, and once after the re-creation, rather than just once if you drop them before hand. Thanks!

Executive Junior Cowboy Developer, Esq.
Post #1500549
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse