| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2002-01-17 : 16:48:31
|
| Maybe it's that I grew up on Oracle... but I found it very odd that one of our developers said that he has never created a table without using the SQL Server Identity column. I've always used the system of keeping my data unique and making the unique column(s) the Primary key, rather than using the Identity column. I've heard that you really shouldnt ever use the Identity column and well I never have because I have never found a need for it. The developer said that it is the only way to enforce referencial integrity. I know that he is wrong there but he did say that using the Identity columns with ADO makes your program much faster. He said he has been a SQL Developer for 8 years. I have only been a DBA for 2 years so I doubt myself a little. Whats the story with this pesky little microsoft feature called the Identity column?!?Thanks in advance for any replies that help...DanielSQL Server DBA |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-01-17 : 17:08:14
|
| Daniel,Welcome to the great "Identity" debate......8 years DBA and he thinks an Identity is the only way to enforce RI?I am absoultely dumbfounded........My $0.02..Pros:It is a great surrogate key..If made the PK, makes a great substitute for Cascade Update functionality ie Look Up tables...Cons:Can be unpredictable...Cannot Update them...Requires table ownership or above to Insert out of sequence...Not portable....Extra administration required....DavidMTomorrow is the same day as Today was the day before. |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-01-17 : 17:14:11
|
| Thanks that's what I was looking for was the pros/cons. I just always heard not to use them because they are not really needed. Which, IMO, they are not needed if the DB is designed correctly in the first place. I just didnt know too much about the whole ADO thing he was talking about...Anybody else have some input on this?DanielSQL Server DBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-17 : 18:31:07
|
| For what it's worth:http://www.sqlteam.com/item.asp?ItemID=2599Not my best, but ultimately I agree with you and David: if the table is properly designed you don't need an identity column; it is unfortunately a crutch and/or an unnecessary remnant from Access databases and users. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-17 : 18:46:43
|
| Personally, I like identity columns. Yes, if you design your database right you can do without them most of the time, but having identity does make a lot of things easier. First of all, if a primary key in one of your table is very wide, and a lot of other tables in your reference it, the joins will be slow (not to mention that queries start looking pretty ugly when there are a lot of composite keys involved). Secondly, when a table has identity columns you can batch long-running operations very easily. As far as drawbacks... David and Rob have pretty much covered everything I can think of... But at the end -- it isn't much more than a judgement call. Edited by - izaltsman on 01/17/2002 18:48:25 |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-01-17 : 19:01:07
|
| I favor unique identifiers (GUIDs) as my Primary keys. You don't have to worry about the "out of order" issue, and it makes some things easier when moving data from one DB to another (like development to production). You (almost) never have to worry about your "keys" colliding between development and production. If I remember there were issues with identities when getting the last identity out to return from your SP. I think you can use @@Identity to do it. I like being able to create my "key" before I call my insert statement (i do both in one SP). When you pass that key out, you know 100% for sure that is the key used for that insert. I think @@Identity might not be reliable when there are lots and lots of transaction happeneing at once.The downsides are that they are much larger keys, so you take up more space in your database. I think they take a bit longer to create than an identity, but creation of GUID is still plenty fast enough that you'd probably never notice.MichaelP |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-18 : 10:23:20
|
quote:
Well come to think of it... if this is a standard practice that Microsoft embraces then why are both the NorthWind and Pubs DBs free of identity columns?!?
First of all, whether or not pubs and Northwind are specimens of ideal database design is very debatable.  Secondly, if Microsoft didn't embrace this practice, they wouldn't have included it in their product. And lastly, have you looked at the Orders table in Northwind or the Categories table (aren't OrderID and CategoryID identity fields?) ?  Edited by - izaltsman on 01/18/2002 10:24:29 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-18 : 14:36:32
|
| I tend to use IDENTITY keys myself, primarily because of the compound-key join issue that Ilya mentions and a long-ago nightmare scenario involving updating the fields that were selected as Primary Keys in a moderately robust system. That experience created a (probably irrational) desire to use IDENTITY fields instead of "real data" for my keys.As for this guys claim that "using the Identity columns with ADO makes your program much faster" I've never seen nor heard anything that supports (or contradicts) that statement. I'd ask him if he has any performance metrics, white papers, or articles to back him up. I'd suggest that the overall design (including proper indexing) has a FAR greater impact on performance than does use of IDENTITY. And I sure hope he hasn't spent 8 years doing rs.AddNew... rs.Update statements (using ADO commands) instead of SQL Stored Procedures. Maybe if that has been his technique there is something to what he says, but I still haven't seen/heard anything.--------------------------------There's a new General in town... |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-19 : 21:10:22
|
| Microsoft does embrace the Identity column as a primary key. I always learned to use a unique field in your data, but if you ever take the Solutions Requirements MCSD exam they ask questions such as "What would be the primary key of the following table" And 2 options are VIN# or Auto_ID (An ID Column). This is one thing I diagree with them on (And I work for MS), I would use VIN#, but the correct answer is Auto_ID.I think the pros and cons are covered well in this thread. I use IDs from time to time, but I still believe if there is a relatively small unique value in your data, it should be the obvious choice.My 2 cents-Chad |
|
|
|
|
Identity column in every table?!?