Benefits to using the Identity column

Question

Benefits to using the Identity column

Ryan-350179

Old Hand

Points: 311
More actions
July 23, 2007 at 9:59 am

#196989

I'm designing a database and was researching common conventions and standards. I found that some people don't use the identity column for a primary key because they prefer to create their own unique id number for the column. Whats your opinion?
Ryan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

i have never gone wrong with the identity column. even in cases where i need to generate my own unique ids like claim no, or ref no for the client i use the identity values in the background, and due to my use of BIGINT, helps when doing the joins.

Everything you can imagine is real.

WesleyB Ten Centuries Points: 1307 More actions · Answer 2

When you need to keep different environments synchronized it is harder with an identity since out of sync issues will occur. A simple rollback, script executed in a different order, ...

But it all depends on your specific situation of course.

Kbrown SSCommitted Points: 1794 More actions · Answer 3

I've found the identity column useful when business rules change.

For example, a business rule at the start says a repair order number will *never* be reused/duplicated. Many years down the road the transactional system starts to reuse the repair order numbers (that system only holds data for 6 mos). It doesn't matter to me since I use an identitiy column as the PKID.

I'm surprised there isn't a raging debate about the identity column vs natural key. There's opinions on both sides on this one for sure.

aureolin Hall of Fame Points: 3520 More actions · Answer 4

Natural keys are wonderful when you're dealing with a real-world situation that is neat and orderly, stable over time, and very well-defined. For everything else, I'd recommend using an Identity column.

Vivien Xing SSChampion Points: 12297 More actions · Answer 5

What kind of database? OLTP or datawarehouse? How is it being used and going to be used? Any replication?

I found a debate about "Identity and Primary Keys" so you can hear more people's voice here:

http://www.sqlteam.com/article/identity-and-primary-keys

cutespn SSCommitted Points: 1669 More actions · Answer 6

One more addition to the list http://vyaskn.tripod.com/sql_server_check_identity_columns.htm

Regards,
Sudheer

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 7

Consider also using uniqueidentifiers (or Guids), especially if using replication. You cannot use identity if inserts can be done in more than one database. (which identity counter is current and right?) You just want to make the clustered index is something else than the guid, otherwise you will quickly have split pages and page fragmentation problems.

Mark