I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space (150gb) in the ldf file to grow in case of index rebuild (with tempdb OFF).

Please help me with an efficient way to rebuild the indexes on new Partition function/scheme. Which of the two approaches would be an efficient and less resource consuming way (OR recommend any other approach)?


  • Drop existing CIX and drop all N-CIX's
  • Create CIX on PScheme
  • Create aligned/non-aligned indexes on PScheme


  • Drop only CIX
  • Create CIX on PScheme
  • Create aligned/non-aligned indexes (with DROP_EXISTING) ON


  • 4
    Does the database need to stay online? Will it be processing inserts/updates during this change? Does it need to respond to queries? Mar 29, 2013 at 22:03
  • 1
    Well, the second scheme is probably the most inefficient I can think of. Consider rebuilding the CI WITH (DROP_EXISTING = ON) instead of dropping down to a heap table.
    – usr
    Mar 29, 2013 at 22:28
  • 1
    There will be no connections to the DB/Table during this time
    – Amam
    Mar 29, 2013 at 22:32
  • @usr - I m not sure if we can build CIX with DROP_EXISTING ON , since the new CIX will have DATE instead of ID column. So both cases CIX will be dropped first and then create CIX with drop_exiting OFF
    – Amam
    Mar 29, 2013 at 22:39

2 Answers 2


Now, the best way to answer this is to set up a smaller test database of a few gigabytes. It will accurately predict the best technique.

Your technique (2) is horrible because step 1 leads to all NCI's being rebuilt to work together with the resulting heap table. Don't do that.

The good news is that DROP_EXISTING works with changing the index definition including columns and partition scheme:

The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options.

And this is the reason why this feature exists. It is not equivalent to first dropping, then creating.

Here are the basic techniques (all of yours are horrible due to the intermediate heap table):

  1. Drop all NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
  2. Drop all non-aligned NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
  3. Rebuild the CI WITH (DROP_EXISTING = ON)

(1) is very light on the transaction log. It is not maximally efficient. (3) is the simplest - it will cause all indexes to be rebuild as aligned indexes. This is not so good because you will have to rebuild those that you don't want to align.

(2) is probably best in all cases. I can't think of any case where it might be worse-off. It has the advantage of reusing existing indexes for a more efficient rebuild (this is possible because you didn't just drop all NCIs). NCIs which you want to be non-aligned don't benefit from this method and would be rebuild needlessly. So get rid of them before starting the procedure.

All of this actually works online although you said you don't need that.

Again, just try it out on a sample database. Certainly you shouldn't entrust the availability of your database to my advice. I suggest you take the ideas from this post and try them out.

  • Thanks, when CIX is rebuild WITH (DROP_EXISTING = ON) on a PScheme, then other NCIX get rebuild only on the same FG that they were on before the CIX rebuild and hence do not get aligned . So even after CIX build on PScheme, the NCIX will have to be rebuild on the same PScheme to ensure indexes are aligned. So in the above, looks like (2) and (3) dont hold good. Pls correct me if i m wrong...
    – Amam
    Mar 30, 2013 at 4:45
  • 1
    ID - column is currently primary key clustered index and hence CIX rebuild WIth (DROP_EXISTING = ON) on DATE column will not work because of the existing PK constraint on ID and throw error msg 'The new index definition does not match the constraint being enforced by the existing index'
    – Amam
    Mar 30, 2013 at 7:00

First of all, the high level strategy is mirgrating your data from your existing table to new partitioned table, not partiion directly on your existing table.

Second, you don't need to drop any existing index on your existing table, since what you do is migrating data.

Follow the below steps:

  1. Create your new partition function
  2. Create your new partition schema
  3. Create your new table, partitioned on the partition column. Create more partitioned in advance, because when you migrate data, you old table is probably still online, there will be new data you need to migrate later
  4. Basing on your partition column and range, partially migrate your data into new table.
  5. Suggest you to use select .. into .. to get each partition data into a new temp table
  6. After you get data in temp table, build indices you need, add constraint
  7. Switch your temp table to corresponding partition
  8. Drop your temp table
  9. Only after you migrate all data, drop your old table (if you don't have enough space, you may consider deleting some data first from your old table and perform shrink if needed. )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.