Post reply

Benefits to using the Identity column

  • 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

  • July 23, 2007 at 10:12 am

    #721569

    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.

  • July 23, 2007 at 12:29 pm

    #721613

    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.

  • July 24, 2007 at 9:08 am

    #721958

    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. 

  • July 24, 2007 at 12:29 pm

    #722005

    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.

  • July 25, 2007 at 10:36 pm

    #722476

    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

     

  • July 27, 2007 at 4:39 pm

    #723090

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






    Regards,
    Sudheer 

    My Blog

  • July 30, 2007 at 2:31 pm

    #723413

    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

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

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