CODEWITHSUNDEEP
sqlsql-serverindexingguid
Kevin Hanson
Kevin Hanson

Reputation: 25

Replacing GUID PK with an INT IDENTITY PK

I've got a legacy app that, for whatever reason, has GUID PKs throughout. Unfortunately, one of the tables has 3.5+ million rows and performance is beginning to suffer. I'm pretty sure it's because of the GUID being PK and being part of a clustered index.

I want to change that table to have an INT IDENTITY PK for the clustered part, and retain the current clustered index as a non-clustered.

I've read some stuff here along these lines (ie. Clustered and nonclustered indexes performance) but everything I've read assumes single-column involvement.

Here's where my situation gets confusing (at least to me):

This table has a PK/Clustered Index based on 4 columns!

I tried to create a new table with same schema plus the new int identity column, but I can't quite get the PK separated from the clustered index (as recommended in the article above).

table schema:

ProjectItemID  (PK, FK, uniqueidentifier, not null)   <--- GUID
PermissionSourceType  (PK, int, not null)
GranteeID  (PK, uniqueidentifier, not null)           <--- GUID
GranteeType  (int, not null)
Access  (int, not null)
PermissionType  (PK, int, not null)
ExpirationDate  (datetime, null)

as you can see, columns 1,2,3 & 6 are all part of the PK.

How do I script the new table (with added int identity PK) so that the single-column PK is the clustered index, but the OLD PK fields are in a non-clustered index?

Thanks, Kevin

Upvotes: 2

Views: 661

Answers (1)

Pedro Fialho
Pedro Fialho

Reputation: 441

It's pretty simple, actually, when you follow these steps (I'm assuming T-SQL syntax is not a trouble for you):

  1. Remove PK (thus removing the clustered index)
  2. Add an nonclustered UNIQUE index with the 4 columns
  3. Add the IDENTITY column
  4. Add a clustered PK constraint for the PK

Upvotes: 4

Related Questions