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 |
|
redlam
Starting Member
7 Posts |
 Posted - 2002-10-10 : 13:44:10
|
| Hi all.I work for a company that is developing a client/server application with a rather large and complicated database. I am told that we should not use identity columns as primary keys because "There are no advantages of using them with related tables" and "They are designed for use in small projects/simple databases". Personally, I'm inclined to use identity columns as primary keys but that's just my personal preference. Can anyone else provide a general opinion on this? What are the potential disadvantages to using identity columns?Thank you in advance for any and all comments. |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-10 : 13:54:30
|
| here is a good starting point for the discussion ...Jay White{0} |
 |
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-10-10 : 14:03:41
|
| Someone else will likely respond with more in depth info, but here is what I've gathered so far in my own experience.Honestly, identity columns are bad relational design and are not ANSI SQL. They have no meaning to the data in the table, and are hard to read if you are looking at a table that uses it as a foreign key.That said, using a Surrogate Key (i.e. an identity column) does have some advantages. Indexes can be faster on INT columns compared to a varchar column, and they take up less space on disk and in RAM when you start using it as a foreign key in other tables (Thus speeding up queries on the child tables.)Personally, I skip Identity columns for smaller applications to make it easier to view the data in tables. (yeah, you could use a view for this but....)----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-10 : 14:08:38
|
| Identites are good in some instances but bad in others.Sometimes you have no choice as there is no other data which defines the record.I would steer away from identities if you already have candidate keys but it really depends on the situation.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-10 : 14:12:10
|
| I'll give you an example of why an identity column is better than a natural key:I'm writing DTS packages moving data from flat files to a sql server. For some unknown to me reason, the designer of these tables decided a natural key was the better way to go. IE/ oil field data... each well has a specific identifier therefore that would make an excellent key. And it did. However the tables underneath (as a standard) also got natural keys. In one case I got a table that has 5 columns combined to make a natural key. That is 5 fields 2 of them varchar(15) that combine to become a key... Kinda slow and very hard to work with/trouble shoot.I personally like identity columns(along with the other dba's here.. only the guy who designed the database actually likes natural keys). I guess it depends on the data.[edit] You also need to take in consideration future plans such as replication. Remember that some replication options (transactional with immidiate update subscribers or Merge) require an identity column. If there isn't one there SQL will force one upon the table (which may mess up other pieces of code run against that table[/edit]-----------------------SQL isn't just a hobby, It's an addictionEdited by - M.E. on 10/10/2002 14:14:41 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-10 : 14:17:50
|
| more blabber on the subject.I think the last paragraph of my post says it pretty well...Jay White{0} |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-10 : 14:34:43
|
quote:
Remember that some replication options (transactional with immidiate update subscribers or Merge) require an identity column. If there isn't one there SQL will force one upon the table (which may mess up other pieces of code run against that table
I think you mean that replication requires a PRIMARY KEY, not specifically an identity column as one. I cannot find anything in Books Online that says it will add an identity column unless there is no primary key on the table.In fact I found an entry that indicates certain problems with identity columns when using immediate updating, certainly enough to suggest avoiding them. I find it hard to believe that SQL Server would enact a default behavior that they document as problematic/not recommended.Edited by - robvolk on 10/10/2002 14:35:57 |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2002-10-10 : 15:24:13
|
| Last year I diagnosed a serious design flaw in an application that involved merge replication and primary-key identity columns. The designers hadn't assigned ranges to their identity columns for the various replication nodes, so the merge-replication process was blowing away records and reports when one conflict was declared the winner over another.I personally use identity columns a lot in my designs, but they definitely have drawbacks, issues and caviots, esp. with SQL replication.Merge-replication will create a rowguid column in your replicated tables for it's lineage process if the table doesn't already have one.-- monkeyEdited by - monkeybite on 10/10/2002 15:26:35 |
|
|
|
|
|
|
|
|
Identity column - good or bad?