I have presently got a database about 20 reference tables, i.e. things like items, assets, depots, customers, etc. This post is saved inside a central database and it is downloaded to Smartphones of engineers who're out on the highway. Every table during my database includes a PK of UniqueIdentifier (i.e. a GUID).
I have realized after 24 months of product which i never do rather than will have to edit these ref tables. Because of this you shouldn't have to allow them to have UniqueIdentifiers as primary secrets.
As I am serializing my collections before I send these to the PDA over my webservice, the particular serialized information is huge because of the size of a GUID.
Anyway, getting to the stage - I wish to change all of the PKs from the tables to IDENTITY Ints. Can there be any good way to do that, or can i need to go like cutting all FKs, creating temp tables, after which writing some software to remap the information?
Thanks ahead of time.
First - LOL. I understand a a number of others inside your company... 'Wow - GUIDs are awesome, I am going for their services everywhere...' A minimum of you already know among the difficulties with going this route.
Second - you might find these instructions useful:
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' <do stuff here> exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Third - the part where it states -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 any couple of several weeks just just in case you have to adjust/fix anything. Sooner or later later on, you need to have the ability to remove the guids out of your ref tables.
For those who have FK referencing your PKs, you are in large trouble. I am speculating you opted for GUIDs due to the distributed character of the application (Smartphones) which possibly the initial intent may have been for brand new data to become added from individuals Smartphones, by which situation GUIDs are perfect. The lower side is you can't index on the GUID.
My suggestion isn't to make use of identity ints to start with. First of all, disable your FKs. Give a new column that's INT (not identity) for those references to that particular PK (ie all tables which have a FK to it too). Then for every GUID, produce a new int (the following value) and place it from the guid, and all sorts of references towards the guid in other tables. Then switch on new FKs for that ints, after which change your PK for your new int column. Finally remove your GUID posts, after which rebuild your index.
Hope that wasn't too garbled.
As lengthy as you've some area (or group of fields) that's unique, you can easily reassign the PK to that particular area. i.e. if you do not require a guid, you most likely have no need for a IDENTITY surrogate key either. The simplest factor to complete overall is simply to utilize a PK on a number of non-null existing posts which are unique.
Otherwise, just add a name column and reassign the PK into it. (It'll populate whenever you save the changed table.) You'll be able to remove the column using the guid.
(I am presuming we are getting this discussion since your guid column is not presently employed for anything. Even when it is a FK in another table, for those who have unique posts to become listed on on, you do not need it there, either.)
If none of this matches your circumstances, publish again with increased detail. You can do this with little fuss or bother.
(I am unsure the reason why you thought you possessed uniqueidentifiers for the primary secrets even when the tables may be edited, BTW.)
Well, what I don't seem to comprehend is that on one side you condition that you don't require the Id posts but alternatively you're delivering them via your internet service.
So, I'd first consider my queries to determine what's fetched and optimize there when it comes to the came back resultset.
Altering the PK's is large effort, but with the aid of "SQL Compare" it is possible.