Databases
StackOverflow2013
Postsdbo
POBest way to change clustered index (PK) in SQL 2005
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest way to change clustered index (PK) in SQL 2005
    text
    copied!<p>I have a table which has a clustered index on two columns - the primary key for the table. It is defined as follows:</p> <pre><code>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] </code></pre> <p>I want to remove this clustered index PK and add a clustered index like follows and add a primary key constraint using a non-clustered index, also shown below.</p> <pre><code>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] </code></pre> <p>I was going to just drop the PK clustered index, then add the new clustered index and then add the non-clustered primary key index, but I learned that dropping the existing clustered index would cause the table data to be reordered (see answer here <a href="https://stackoverflow.com/questions/705504/what-happens-when-i-drop-a-clustered-primary-key-in-sql-2005">What happens when I drop a clustered primary key in SQL 2005</a>), which I don't think should be necessary. The table is knocking 1 TB, so I really want to avoid any unnecessary reordering.</p> <p>My question is, what is the best way to go from the existing structure to the desired structure?</p> <p>EDIT: Just want to clarify. The table is 1TB and I unfortunately do not have space to create a temporary table. If there is a way to do it without creating a temp table then please let me know.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload