6

I have a table which has a clustered index on 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 clustered index PK and add a clustered index like follows and add a primary key constraint using a 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 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 What happens when I drop a clustered primary key in SQL 2005), which I don't think should be necessary. The table is knocking 1 TB, so I really want to avoid any unnecessary reordering.

My question is, what is the best way to go from the existing structure to the desired structure?

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.

Community
  • 1
  • 1
Mr. Flibble
  • 26,564
  • 23
  • 69
  • 100

4 Answers4

12

This isn't a complete answer to your question, but make sure that if you have any other indexes on the table that you drop those first. Otherwise SQL Server will have to rebuild them all when you remove the clustered index then rebuild them all again when you add back a new clustered index. The usual steps are:

  1. Remove all non-clustered indexes
  2. Remove clustered index
  3. Add new clustered index
  4. Add back all non-clustered indexes
Tom H
  • 46,766
  • 14
  • 87
  • 128
  • Well, that was my initial plan, but the 2nd step of removing the clustered index causes all the data to be moved about. I was hoping to merge step 2 and 3 somehow, to avoid an unnecessary movement of data. – Mr. Flibble Apr 01 '09 at 14:54
8

If your table is getting up to 1 TB in size and probably has LOTS of rows in it, I would strongly recommend NOT making the clustered index that much fatter!

First of all, dropping and recreating the clustered index will shuffle around ALL your data at least once - that alone will take ages.

Secondly, the big compound clustered index you're trying to create will significantly increase the size of all your non-clustered indices (since they contain the whole clustered index value on each leaf node, for the bookmark lookups).

The question is more: why are you trying to do this?? Couldn't you just add another non-clustered index with those columns, to potentially cover your queries? Why does this have to be the clustered index?? I don't see any advantage in that....

For more information on indexing and especially the clustered index debate, see Kimberly Tripp's blog on SQL Server indexes - very helpful!

Marc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
4

  1. Create a new table:

    CREATE TABLE newtable (colA INT, colB INT)

    • Insert all 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'

    • Build the indexes:

      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]

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • That isn't an option. I have a 1TB table on a 1.5TB RAID. – Mr. Flibble Apr 01 '09 at 14:37
  • SQL Server will need space both for the reordering and for moving. If you're out of space, you may use MOVE TO option in DROP INDEX to create your table on a temporary storage, and then recreate it again on your RAID. – Quassnoi Apr 01 '09 at 14:52
  • You could transfer rows over in chunks, deleting them from the source as you go. That would probably be a very slow process though. – Tom H Apr 01 '09 at 14:52
  • Okay....why would creating a temp table be better than dropping the clustered index and allowing the unnecessary resorting? Seems like it would be just as slow and more work. – Mr. Flibble Apr 01 '09 at 15:45
  • @Quassnoi. Do you happen to know how much space it would need? Is 400GB free likely to be enough to drop/create the indexes? – Mr. Flibble Apr 01 '09 at 15:46
  • Creating a temporary table will not result in reordering. It will be faster, as it will not need to update existing indexes. 400 Gb may or may not be enough, depending on your table structure and data sparseness – Quassnoi Apr 01 '09 at 16:10
  • 1
    See here how to estimate your new table size: http://msdn.microsoft.com/en-us/library/ms187445.aspx – Quassnoi Apr 01 '09 at 16:21
-3

Clustered indexes don't actually change the physical order of the data being stored in the table it self. It hasn't been this way since SQL 6.5.

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

mrdenny
  • 4,990
  • 2
  • 21
  • 28