Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2011-02-07 : 13:57:08
|
| hello,I have a 22 million row table and I need to drop and recreate the PK.Does SQL Server need to acquire an exclusive lock on the data to complete the build of the clustered index?Thanks, John |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-07 : 14:04:00
|
Yes. The physical table is actually stored in the order of the index. My understanding of it in simple terms is that it is the equivalent of dropping the table, recreating and populating the table without the clustered index. It would then do the same when recreating the clustered index (drop table with no index, repopulate it and store it in the order of the Clust index). It is an expensive operation to do in memory/temp table on that many records. Your tempdb would need to have at least 125% available of the physical space used by the database table to do the operation.better bet:Create new table with clustered index. insert into new table from old in the order of the index so they are properly inserted. Drop old table,use sp_rename to rename the new table and index to the old names.That way your downtime for the table in use is minimal.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-02-07 : 14:09:04
|
| Thanks for the idea, Dataguru1971. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-02-07 : 14:48:22
|
If you are using SQL Server 2005 (or 2008) Enterprise Edition, you have the option to do an online index rebuild:ALTER INDEX PK_MyTable ON dbo.MyTable REBUILD WITH ( ONLINE = ON ); This allows users to access and use the table while the rebuild is running.CODO ERGO SUM |
|
|
|
|
|
|
Create primary Key