2

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

Community
  • 1
  • 1
Kevin Hanson
  • 25
  • 2

1 Answers1

4

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
Pedro Fialho
  • 444
  • 8
  • 18
  • 1
    Thanks. That's almost exactly what I came up with. I just did your steps in a slightly different order (1,3,4,2). Glad to have confirmation of the direction I went. :) – Kevin Hanson Jun 20 '13 at 19:06
  • sorry I can't upvote your answer... I don't have enough reputation. – Kevin Hanson Jun 20 '13 at 19:13
  • That order is also correct. Be sure to run these inside the same transaction to ensure that no data is modified before all constraints are applied. And about the upvote, no problem, somebody else may do that and you can always come back later :) – Pedro Fialho Jul 03 '13 at 14:15