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 |
|
JamesT
Yak Posting Veteran
97 Posts |
 Posted - 2002-12-19 : 17:19:24
|
| Sorry to bait but, oh well. Do you ever use or advocate the use of SQL Server Identity for a Primary/Foreign key? If so, what circumstances would you do that for? At my place of employment we have just had it come and bite us in the Arse in a BIG way. Any takers? I am leaving off what happened because I want to see if our design is common or REAL screwed up.ThanksRev. James |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-19 : 17:27:04
|
| Well this thread is going to start a war, just like it did when an article with this same theme was discussed.We had to use an identity column as the primary key due to the real key being very large in size and comprising of many columns. We did performance testing both ways and found out that using the identity worked better for us. I am sure there are lots of reasons why not to do it this way, but my company is more concerned about performance than about a good/great database design. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-19 : 17:30:09
|
 Wow, I can't believe you missed the recent onslaught:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6136Make sure you've got a few hours free, it's up to 101 replies right now. Dare I suggest you read the article too before you dig in, you'll have some idea where all the passion comes from.  I'm sorry to hear that it's causing you problems, but I must admit I'm glad that someone can quantify first-hand how the practice can turn out to be a bad decision. Unfortunately a lot of people DO use identity, and equally unfortunately seem to be incredibly misguided in their zeal to defend it. You'll find that out from the thread pretty quickly.It's hard to say if it was really a bad choice without knowing all the details of your database and what it's designed to serve. There are times when it IS a good decision, but it's rare. You'd be surprised how often you can do without it.Anyway, check the thread, and by all means POST THE JUICY DETAILS of what went wrong. I'm not just asking for my own reasons; you can help a lot of other people make better design decisions by showing them what COULD happen if things are not thoroughly thought out. |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-12-19 : 17:57:29
|
| Ok, sorry, I didn't know about that other "Book" that had been written. I don't get to visit here often enough. Let me see how much I can say without breaking my NDA and hence employment.Almost all our tables have an Identity column that is used for the Primary Key in one form or another. In Foreign Key relations that PK is used so you have the classic Parent/Child relation with the child referencing the Identity Column in the Parent. That's all fine and dandy. Well, we have lots of DB's all over the STATE and we need to combine them into one to process STATE numbers, moon phases, etc. To accomplish this we use the most efficient means necessary: Batch. Anyone says one word about replication and I'll blow chunks on my monitor. It's not my choice to make in this case so I have NO say. In each case where we bring data we do bulk DTS Transformations into staging tables where we then do update/inserts to the 'STATE' tables. Fine and dandy so far. We do not use the Identity columns for the update/insert in most cases rather a composite key made up of columns that have real meaning. Here's the trouble: Source has identity field, destination has identity field, transformation does NOT have identity insert on. Oops. Silly of us to assume that in almost all cases we never need it for updates/inserts. In one case a sort is done on the identity field to determine the most recent record added. Well, they are not going to jive and so far, we are still finding problems. As I type it seems as if more and more senior people are going past my desk to discuss it with the CIO. Tonights our Christmas party or Winter Celebration, or whatever non-offensive title you wish to use. I am hoping that I'll get to go and not be unemployed by the time it ends. That's my store and if pressed by my NDA I'll deny that there is any factual evidence to back it up and that I had smoked too many spiders the day before and was recovering. Team Player Disclaimer: I was not present for the original architecture of the system nor it's planning. I cannot judge nor presume to know which way was better as I was not present. I know when pressed for time people do certain things to meet deadlines as we all have done. Therefore, it would be premature to say that the design was incorrect. OK, take off your hip-waders and nose plugs. I'm done. Rev. JamesNote: Certain words were changed so as to prevent my person being fingered as the poster of this truth. You get the gist though. |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-12-19 : 18:12:26
|
| I had a co-worker read my previous post and he said that his pet <insert pet> could tell it was me so I am altering my profile to take all evidence that could point to me away. For now on, I'll go by Bufustor Schlavin or Schnitzle Pleiber. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-12-19 : 18:14:35
|
quote:
Tonights our Christmas party or Winter Celebration, or whatever non-offensive title you wish to use.
I'm offended by that!It's summer in Sydney Damian |
|
|
|
rharmon
Starting Member
41 Posts |
Posted - 2002-12-19 : 19:41:36
|
| This may be of some help, I may be smoking spiders, but I have been in similar situations and lived. I'm assuming the seperate databases are of a similar schema, if they're not, the vote goes for "I'm smoking spiders"You're gonna be dealing w/ 3 databases, a miror of the source production database, the destination database and a work database. If all goes well, the schema on the destination database is identical to your existing production database, but the identity attribute has been removed from all id fields.<psudocode>use source_dbcreate table parent_things(thing_id int ,thing_name varchar(50))create table child_things(child_id int,child_name varchar(50),parent_id int)insert into parent_things (thing_id, thing_name) values(1,'something')insert into parent_things (thing_id, thing_name) values(2,'some other thing')--normaly the following would be an import from the production db via dts, distributed queries etc.insert into child_things (child_id, child_name, parent_id) values(1,'some_child',2)insert into child_things (child_id, child_name, parent_id) values(2,'some_other_child',1)</psudocode> The work database is almost identical, but I add a source_db_id int, and a native_id int field as well as set the id fields back to identity. The other trick is to drop all relational integrity. (I know, everyone freaks out here;) )The data is imported into this database (usually via sprocs) and the source system's id is moved to the native_id field allowing the id field to auto incriment. I seed the source_db_id with an arbitrary integer unique to each source db. As soon as all records from all source databases are imported, I surrogate all forign keys with their brand new spanky surrogate keys.<psudocode>use work_db--will be repeated for each prod databaseinsert into parent_things (thing_name, native_thing_id, source_db) select thing_name, thing_id, 5 from source_db..parent_thingsinsert into child_things (child_name, native_child_id, parent_id, source_db)select child_name, child_id, parent_id, 5 from source_db..child_thingsupdate child_things setparent_id = parent_things.thing_idfromchild_things inner join parent_thingson child_things.parent_id = parent_things.native_thing_idand child_things.source_db = parent_things.source_db</psudocode>The destination database should match your existing production databases exactly including ri, but for the purpose of the initial load, we drop ri and identities for the large imports.<psudocode>use dest_dbinsert into parent_things(thing_id,thing_name) select thing_id, thing_name from work_db..parent_thingsinsert into child_things(child_id, child_name, parent_id)select child_id, child_name, parent_id from work_db.child_things</psudocode>Put the identities back, then put ri back (checking existing data) and hold your breath. If you managed to track down EVERY relationship, all should be well in your new consolidated database.Hope this helps!ROb--My spelling is the product of a publik edumakation |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-12-19 : 20:28:46
|
quote:
I'm offended by that!It's summer in Sydney Damian
Heatwave in Adelaide. I'm more jealous than offended.... --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
Just curious, Do you ever use...