Moving Clustered Index on large table 1

[gradley]

Hi,

I'm looking for the most efficient method for moving a clustered index from the primary key (single IDENTITY field) to an existing index containing Date, Store and Associate. The table currently has about 91 million rows and takes up about 12 Gig of disk space.

Here are my questions:

1. Should I copy the data out to a work table using an ORDEY BY of the (NEW) clustered index - then copy back with the new indexes?
2. Recreate the indexes over the existing data?
3. Export the data to disk files and import using BCP?

Not sure what the best practice is for this type of change (besides designing it correctly in the first place).

Any feekback would be greatly appreciated.

Windows 2000 Advanced Server
SQL Server 2000 Enterprise Edition

 

[mrdenny]

Your best bet will be to simply drop and recreate the indexes on the existing tables. This will give you the least fragmentation on the indexes. Be sure to set your fill factor correctly when creating the new indexes.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[gradley]

Thanks mrdenny!

I will follow your recommendation.

Just a follow-up question on the fill factor. Because this transaction table has a single point of INSERTS and no UPDATES, If I continue inserting the records in the order of the clustered index (Date, Store, Associate), I shouldn't need much of a fill factor -- correct?

 

[mrdenny]

Correct, if you are inserting only then the fill factor on your clustered index can be low in this case.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)