Best way to modify a clustered index (PK) in SQL 2005

Question

I have a table with a clustered index in two columns - the primary key for the table. It is defined as follows:

ALTER TABLE Table ADD CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ( [ColA] ASC, [ColB] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] 

I want to remove this PK clustered index and add a clustered index, for example, as follows, and add a primary key constraint using the non-clustered index, also shown below.

 CREATE CLUSTERED INDEX [IX_Clustered] ON [Table] ( [ColC] ASC, [ColA] ASC, [ColD] ASC, [ColE] ASC, [ColF] ASC, [ColG] ASC )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] ALTER TABLE Table ADD CONSTRAINT PK_Table PRIMARY KEY NONCLUSTERED ( ColA, ColB ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 

I was going to just delete the PK clustered index, then add a new clustered index and then add a non-clustered primary key index, but I found out that deleting the existing cluster index will reorder the table data (see answer here What happens when I drop the cluster primary key in SQL 2005 ), which I think is not needed. The table knocks down 1 TB, so I really want to avoid unnecessary reordering.

My question is, what is the best way to move from an existing structure to a desired structure?

EDIT: Just want to clarify. Table 1TB, and I, unfortunately, have no place to create a temporary table. If there is a way to do this without creating a temporary table, then please let me know.

Answer 1

If your table gets a size of up to 1 TB and probably has many rows in it, I highly recommend NOT to make the clustered index much thicker!

First of all, resetting and rebuilding a clustered index will shuffle around ALL of your data at least once - this alone will take age.

Secondly, the large complex cluster index you are trying to create will significantly increase the size of all your nonclustered indexes (since they contain the entire cluster index value for each node sheet for bookmark searches).

The question is more: why are you trying to do this? Could you just add another non-clustered index with these columns to potentially cover your queries? Why should it be a clustered index? I do not see any advantage in this ....

For more information on indexing, and especially discussion on a clustered index, see the Kimberly Tripp Blog on SQL Server Indexes - very useful!

Mark

Answer 2

This is not a complete answer to your question, but make sure that if you have any other indexes on the table that you drop first. Otherwise, SQL Server will have to rebuild them all when you remove the clustered index, and then rebuild them again when you add the new clustered index. Common steps:

• Delete all nonclustered indexes
• Delete Clustered Index
• Add New Clustered Index
• Add All Nonclustered Indexes

Answer 3

• Create a new table:

   CREATE TABLE newtable (colA INT, colB INT) 

  • Paste all the values ​​from the old table into the new table:

    INSERT INTO newtable SELECT * FROM table

  • Drop the old table:

    DROP TABLE table

  • Rename the new table to the old table

    EXEC sp_rename 'newtable', 'table'

  • Create indexes:

    Table ALTER TABLE ADD CONSTRUCT PK_Table PRIMARY KEY UNSpecified (COLA, ColB) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIM

Answer 4

Clustered indexes do not actually change the physical order of the data stored in the table. This was not the case with SQL 6.5.

The data on the pages is stored in the correct order. Pages can be saved to disk in any physical order.