robert.nesta123 (1/9/2013)
PK is written into all NON Clustered index.
No it's not. The clustered index key is what is in all nonclustered indexes, not the primary key.
So changing from NON CLUST to CLUST will not affect Query Optimiser and my current NON Clustered indexes should be used in the same way.
Err, no I didn't say that.
Are you saying that although my Clustered index was created successfully my Non Clustered indexes were still rebuilding in the back ground causing blocks.
No. Everything, including the rebuild of the nonclustered indexes is part of the single operation
While the index is being changed from nonclustered to clustered, the entire table is locked exclusively and hence any query that wants to use that table will be blocked until the operation completes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability