I'm about to convert a big and very nice VFP/SQL server app to .NET.
Currently all the keys in the app are UIDs - Guid(36). Makes a lot of sense in the current context but I think I can increase performance a lot and simplify future development if I can convert to int identity keys. There are a lot of installed users, and data tables in the millions of records.
I am considering how to go about a onetime conversion of the current data in any given installation to the new schema.
I have a nice SMO tool that I use on SQL databases to make most modifications and I know how I would go about this procedurall in VFP or something. But in the setbased world of TSQL I'm not sure. ( I have also considered CLR sprocs if necessary. )
The long and short of it, I want add new columns to each table to hold an int which will be the new pk and the necessary int future fks. Then I want to sequentially fill the new int keys, and based on current relationships of the guids fill the fks. Finally, drop the UID keys and make the int keys the new pk / fk s
I'd also like to add rowguids to some of the tables and want to use sequential guids.
So, any T-SQL wonks have any thoughts? Also accepting estimates from anybody who wants to write the scripts (email on that)
Suggestions welcome as to whether this is a reasonable thing to ask of T SQL scripts.
TIA
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.