title: Best way to change clustered index (PK) in SQL 2005
body: <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>