Reindexing with Primary Key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Reindexing with Primary Key

Author

Topic

Why9999
Starting Member

10 Posts

Posted - 2011-03-08 : 10:15:30

I have a SS 2005 question. I've got a pretty big table that has a clustered primary key and six nonclustered indexes. I have to change one of the columns in the PK from int to bigint. This means I will have to drop the PK, do the change and add the PK back in.

And that leads to my question. After I drop the PK, I need to reindex all the nonclustereds, right? Is the pattern below the fastest and best way to do this?

1. Drop the PK
2. Change the column from into to big into
3. Add the PK
4. Reindex all the nonclustereds

I'm assuming I don't have to update stats.

Any help is much appreciated...

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 11:09:32

Since there the PK is referenced, it will not allow you to drop PK first and you will have to drop foreign keys first and then Primary key.

Since again you need to create foreign key, I think update stats is not required at that time.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-03-08 : 12:33:48

Keep in mind, when you run a query from this table against other tables and if your FK in other tables is still int, SQL Server will do an implicit conversion of int to bigint on all the other tables being joined.. and depending on how big those tables are it can severely affect performance.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Why9999
Starting Member

10 Posts

Posted - 2011-03-08 : 12:46:17

Thx guys. I don't have any FK's on this table.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 13:17:11

quote:
Originally posted by Why9999

Thx guys. I don't have any FK's on this table.

oops.. I didn't read the post correctly. Your requirement is primary key and nonclustered index. I have given idea for Primary key and foreign key.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-09 : 21:26:56

quote:
Originally posted by Why9999

1. Drop the PK
2. Change the column from into to big into
3. Add the PK
4. Reindex all the nonclustereds

I'm assuming I don't have to update stats.

No no no.

1. Drop all Non-clustered indexes.
2. Drop PK
3. Modify the column
4. Recreate PK
5. Recreate all non-clustered indexes.

Your way will cause all the non-clustered indexes to be rebuilt 3 times each. The way I show will cause them to be rebuilt once. MUCH faster.

You're correct that you do not need to update stats.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-09 : 23:32:39

quote:
Originally posted by russell

Your way will cause all the non-clustered indexes to be rebuilt 3 times each. The way I show will cause them to be rebuilt once. MUCH faster.

Hi Russell,

Out of curiosity, I would like to learn how the indexes are build 3 times for OP’s method.

1st time when PK is dropped
2nd time when PK is added
3rd time when Reindex is done

or something else which I am not aware of

Thanks,
Bohra

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-03-10 : 00:12:25

It would probably be faster to:
1. Create a new, empty table with the correct datatypes and clustered primary key.
2. Insert the data into the new table from the old table using SSIS with batch size set at a million rows or less to prevent huge transaction log growth.
3. Create non-clustered indexes on the new table.
4. Rename the tables.
5. Drop old table.

CODO ERGO SUM

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-10 : 08:40:36

quote:
Originally posted by Michael Valentine Jones

It would probably be faster to:
1. Create a new, empty table with the correct datatypes and clustered primary key.
2. Insert the data into the new table from the old table using SSIS with batch size set at a million rows or less to prevent huge transaction log growth.
3. Create non-clustered indexes on the new table.
4. Rename the tables.
5. Drop old table.
6. Grant any necessary permissions on new table

CODO ERGO SUM

quote:
Originally posted by pk_bohra

quote:
Originally posted by russell

Your way will cause all the non-clustered indexes to be rebuilt 3 times each. The way I show will cause them to be rebuilt once. MUCH faster.

Hi Russell,

Out of curiosity, I would like to learn how the indexes are build 3 times for OP’s method.

1st time when PK is dropped
2nd time when PK is added
3rd time when Reindex is done

or something else which I am not aware of

Thanks,
Bohra

yes. exactly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources