Can I change my clustered index from a primary key to an Identity field?

Question

Post reply

Can I change my clustered index from a primary key to an Identity field?

dcadler

SSChasing Mays

Points: 613
More actions
December 8, 2014 at 11:00 pm

#292696

I have a fairly large table of about 17 million rows and the people who originally designed the table used the primary key value for the clustered index key. The primary key is a varchar(30) and for each new record added the primary key field is not guaranteed to be greater than the last so it will not always be appended to the end of the clustered index.
I have read that a clustered index key should be an int (or bigint of the table size could potentially be very large), unique, non-nullable, fixed-width, static and sequentially increasing in value so it is always appended to the end of the index.
So I was wondering if it would make sense to...
1. Copy the table data to a second, identically structured, table.
2. Drop the original table
3. Re-create the original table, adding an identity field, as bigint, to the table structure.
4. Sort the second copy of the table data by the former primary key field
5. Insert the sorted data into the newly re-created table
6. Create a clustered index based on the new Identity field
7. Re-create each non-clustered index that was used with the original table.
Or is there a better way to do this so that I can have a bigint value as the clustered index key and as a result, reduce the non-clustered index sizes while improving index performance?
Are there any major potential problems with this approach?
Thanks in advance for any help you can provide.
Dave

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

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

Shafat Husain SSCrazy Points: 2754 More actions · Answer 1

Hi Dave,

Few clarifications:

1. Do you want to move your clustered index only or the primary key as well.

2. I am also getting a feeling that MAYBE you want to change the data type of the primary key to int/Bigint, Please correct me if i am wrong.

Kindly share a snapshot of the sample data that you have in your table.

Regards

Shafat Husain

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Dave

Here's what I'd do.

(1) Script all indexes and constraints and then drop them

(2) Add the new column to the table. You should only need int - it will allow 4 billion values so long as you seed your identity at -2147483648

(3) Create a unique clustered index on the new column

(4) Create a non-clustered primary key on the old PK column

(5) Recreate any other indexes and constraints

Bear in mind that if you have any foreign key constraints on other tables that reference this one, you've got a bigger job on your hands, since you'll have to make changes to those referencing tables as well. Also, if you are replicating this table to another server, there'll be extra preparation work for you to do.

Do this on a test server before you do it on live, and when you do it in live, make sure there are no other connections to the database.

John

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 3

Well, before you rearrange your table structure, are you hitting a problem? I recognize that the existing cluster is not an optimal choice for the clustered index, but, is that actually causing your problems? Are you seeing waits, unusual numbers of page splits, severe fragmentation, or anything that would indicate that you need to start rearchitecting the system? If not, I'm not sure I'd mess with it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

dcadler SSChasing Mays Points: 613 More actions · Answer 4

Grant - I am trying to improve performance. Our system has grown nearly 40% this year and there is a noticeable delay in stored procedure performance. We anticipate as much or more growth next year Changing the clustered index key as I would like should improve performance from reduced index size (currently 16 GB) and improve insert times, correct?

Thanks,

Dave

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 5

Probably, but not definitely. Or, more accurately, maybe not as much as you want. It's best to measure your performance to understand what's going wrong and why. Then make changes.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

dcadler SSChasing Mays Points: 613 More actions · Answer 6

Shafat - I no not want to change the field used as the primary key at all. In fact, I will create a new non-clustered index for that field. I just want to change the clustered index key since that value is copied to all of the other non-clustered indexes and I want the clustered index to be smaller in size, sequential and always increasing in value to ensure any inserts are appended to the end of the clustered index.

Thanks,

Dave