SQL Server - What's the best way to change a PK data type?

Question

Hello all,

I've currently got a database with about 20 reference tables, i.e. stuff like products, assets, depots, users, etc. This information is stored in a central database and is downloaded to PDAs of engineers who are out on the road. Every table in my database has a PK of UniqueIdentifier (i.e. a GUID).

I've realised after 2 years of product development that I never do and never will need to edit any of these ref tables. Therefor there's no need for them to have UniqueIdentifiers as primary keys.

As I'm serializing all my collections before I send them to the PDA over my webservice, the actual serialized data is huge due to the length of a GUID.

Anyway, getting to the point - I want to change all the PKs of the tables to IDENTITY Ints. Is there any easy way to do this, or am I going to have to go along the lines of severing all FKs, creating temp tables, and then writing some software to remap the data?

Thanks in advance.

Answer 1

If you have FK referencing your PKs, you're in big trouble. I'm guessing you went with GUIDs because of the distributed nature of your application (PDAs) and that perhaps the original intent might have been for new data to be added from those PDAs, in which case GUIDs are ideal. The down side is that you can't index on a GUID.

My suggestion is not to use identity ints to begin with. First up, disable your FKs. Add a new column that is INT (not identity) for all references to that PK (ie all tables that have a FK to it as well). Then for each GUID, create a new int (the next value) and insert it against the guid, and all references to the guid in other tables. Then turn on new FKs for the ints, and then shift your PK to your new int column. Finally delete your GUID columns, and then rebuild your index.

Hope that wasn't too garbled.

Answer 2

As long as you have some field (or set of fields) that is unique, you can just reassign the PK to that field. i.e. if you don't need a guid, you probably don't need a IDENTITY surrogate key either. The easiest thing to do overall is just to use a PK on one or more non-null existing columns that are unique.

Otherwise, just add an IDENTITY column and reassign the PK to it. (It will populate when you save the altered table.) Then you can delete the column with the guid.

(I'm assuming we're having this discussion because your guid column isn't currently used for anything. Even if it's a FK in another table, if you have unique columns to join on, you don't need it there, either.)

If none of that matches your situation, post again with more detail. This can be done with little fuss or bother.

(I'm not sure why you thought you needed uniqueidentifiers for your primary keys even if the tables were to be edited, BTW.)

Answer 3

Well, what I do not understand is that on the one hand you state that you do not need the Id columns but on the other you are sending them via your web service.

So, I would first look into my queries to see what is fetched and optimize there in terms of the returned resultset.

Changing the PK's is big effort, but with the help of "SQL Compare" it can be done.

Answer 4

First - LOL. I know a quite a few other people in your company... 'Wow - GUIDs are cool, I'm going to use them everywhere...' At least you recognize one of the issues with going this route.

Second - you may find these commands helpful:

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

<do stuff here>

exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Third - the part where it says -do stuff here-

1) add an int autoincrementing identity column to ref table
2) set the current fk guid in data tables to the db key you just created in step 1
3) rinse/repeat for each ref table/data table combination
4) after all is done, refactor code to use id (int) instead of guid

I'd leave the guids alone for a few months just in case you need to adjust/fix anything. At some point in the future, you should be able to delete the guids from your ref tables.