Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chacha
Starting Member
39 Posts |
 Posted - 2004-07-09 : 21:32:24
|
| QUESTION:Is it possible to write a script to convert all the autoincrement keys to uniqueidentifiers (guids) without losing foreign key constraint info?BACKGROUND: I have a db schema that contains 88 tables (no it's not a piano) with an average of ~4 relational constraints per table. There are around 15 or so join tables. All the primary key constraints are int autoincrement columns. There is currently NO data in the database.Now, my application design (namely, how I identify my objects in the middle tier) can be SOOO much simplified if it is possible to convert all those primary keys to Guid columns (so I can generate new ids outside the server). The only hitch, is I need to maintain all the foreign key constraints in the schema. I am thinking that there must be a way to write a script to do this, right? Actually, there must be a way... It seems that the best way to go about this is to write a code generator in a .net lang that first gathers all the db schema metadata, then changes the 'type' of column in memory, then finally builds .sql batch files that recreate the database. Then run the batch file. I already have classes that obtain all the relevant metadata, so this might not be that bad.Is there a pure-sql way to do this? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-09 : 23:27:32
|
| How many rows are going to be in these tables; and how complex will your joins be? You might want to "seriously" reconsider this approach. A GUID key architecture is going to be significantly slower and take up much more space if it gets really large and you have a lot of reads with complex joins.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-10 : 01:12:02
|
| I hear you. I am worried about performance with 3,4,5 table joins. I just wonder which is going to be worse - having to hit the database again to get the autoincrement value when I do an insert or using the guid approach. I REALLY don't want to have to maintain 250+ stored procedures just for CRUD operations. So I am aiming for a more general 'dynamic' sql approach in my data components. This will necessitate one insert and one query for the new key. But with the performance problem of both of these approaches and the sheer numbers of objects I am dealing with I am now leaning towards a code generator that will build my stored procs and return the new id on insert. Thanks you helped me talk myself out of this futile attempt.But, I am still interested in whether there is an easy way to convert primary key column types without having to delete foreign key constraints. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-10 : 01:47:58
|
| I am a big fan of GUIDs for PK. However, the places where I am a fan are where we have data in a number of disconnected databases that needs to be merged and shared around.For example, we have a Bug Reporting facility in our application. All such bugs are harvested into our central database, replies are made, status is updated, and so on, and then all that gets pumped back out to each site. IDENTITY wouldn't cut it ...MeanOld: Why do you worry about performance on this? We're taking the key up from [I think] 8 bytes to 16 - not that big a deal? Or is it the random nature of the keys so that new will be inserted in the middle, rather than at the end of the index?chacha: When we have done this we've added a new column (lets call it [GUID]), NOT NULL, with a default of NewID(), thus all rows in table get a value as the ALTER TABLE is executed - but beware that will take a while for BIG tables.Then once all that is done we drop the FK & PK and recreate the PK on GUID instead, and the FK likewise. i.e. get all the GUIDs in place on all tables and then have a massive DROP & CREATE script.Then all the old, useless, ID columns can be dropped.But in our case we had to leave the IDs for quite some time whilst the client applications were adjusted.Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-10 : 10:38:38
|
| That size difference would be a big deal here. We have millions and millions of records in our tables. Also, we have no reason to do this as we manage currently everything from one site. We do have a couple databases with one common key though. Since the key has to be an integer anyway, we have a common key generation table to generate from one source. The appropriate RI is enforced and a little base auditing information stored in that table.Also, you might want to consider using natural keys in some places.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-11 : 03:42:38
|
| We use both of those, so I'm certainly not a fan of GUIDs for all PKs, just where stuff needs to move around - but we have quite a lot of those type of tablesKristen |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-11 : 20:36:31
|
| Kristen,thanks for that dissenting opinion. So when you do this, you write a script that alters each table, adding a new column (not null, guid) which autogenerates a new guid for the row. You do that for every table in one sweep. Then the script comes back to each table and jots down the old pk-fk relationship (placing them in a temp table so it knows what it has worked on, deletes the pk and fk, then recreates the constraint on the guid - for each table. Could you share such a script if you have one on file (or a general template if that is easier)? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-12 : 13:48:32
|
| That would be a fine idea, but it doesn't happen often enough to automate it here, so I make the script manually.If you are not familiar with it this may work for you:Make the changes in Enterprise Manager "Table Design" tool but do NOT save them, instead press the "Save Change Script" (third icon along), and then abandon the changes.I would make one change to add the GUID column (which will basically just be an ALTER TABLE ADD MyGUID ...). You can then easily duplicate this for each table that you want to change.Then run the script.Then go back to EM's Table Design1. Delete the FK2. Change the PK to the new GUID column3. make a new FK (using the new GUID columns)4. Generate the script as before.5. Abandon the changesNow examine the script and see whether you can duplicate it for the other tables involved. Should just be some find&replace, a bit of a macro, or a SELECT statement based on the system tables (You ought to use INFORMATION_SCHEMA.TABLES, but I can't do that from memory so I've lazily used sysobjectsSELECT 'ALTER TABLE [' + Name + '] ADD MyGUID uniqueidentifier NOT NULL CONSTRAINT DF_' + name + '_MyGUID DEFAULT NewID()' FROM sysobjects WHERE type = 'U' ORDER BY nameEdit: Added suqare brackets just-in-case; probably ought to do something about that constraint name too ...Kristen |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-12 : 17:05:05
|
| Thanks. |
|
|
|
|
|
|
|
|
convert all primary keys to guid