Post reply

Rebuild non-clustered index into clustered

  • July 31, 2007 at 7:52 am

    #117969

    How can I rebuild an index that is non-clustered into a clustered?

    Can I use alter index? I didn't any option for that there?

    The index is the primary key index.

    Thanks.

  • July 31, 2007 at 8:05 am

    #723579

    drop any clustered indexes on the table

    drop the PK

    create a new PK specifying clustered

  • July 31, 2007 at 8:11 am

    #723590

    I knew I can do that. I was wondering if there is a way to do this differently, perheps using alter index.

  • July 31, 2007 at 8:41 am

    #723604

    Andrew

    From Books Online:

    ALTER INDEX cannot be used to repartition an index or move it to a different filegroup. This statement cannot be used to modify the index definition, such as adding or deleting columns or changing the column order. Use CREATE INDEX with the DROP_EXISTING clause to perform these operations.

    So I think the closest you're going to get to what you're trying to do is by using DROP_EXISTING.

    Hope that helps

    John

  • July 31, 2007 at 8:45 am

    #723606

    how big is the table? on a HP DL 380 G5 with 22GB RAM and 64 bit software it took us close to a day to drop a non-clustered index, clustered PK, recreate as non-clustered PK and then create a new clustered index on a column that used to be a non-clustered index.

    this table was around 170 million rows

  • July 31, 2007 at 8:48 am

    #723611

    wow. that's a long time.

    The table is about 50M rows. we're running DL385 with 16GB ram.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply