Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    11

    Unanswered: Autonumber or unidue id generation

    Hi,

    I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?

    Thanks
    -Sudhakar

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by sudhakar_112
    Hi,

    I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?

    Thanks
    -Sudhakar
    Make the datatype int and set the Identity constraint on that column.
    Joydeep

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your two best choices are either an Identity column (integer), or a GUID with the default set to NewID().
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would STRONGLY recommend using the GUID if that option is available to you. IDENTITY values are fine for many purposes, and are easy for legacy code to manage, but GUID values are much simpler in the long run. They are easier to use, easier to generate, easier to manage, and very nearly foolproof, at least in my experience.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, I use GUIDs almost exclusively, but I don't know that I would recommend them as strongly as Pat. They do have some drawbacks, and I am not talking about their size or the fact they they are difficult to type (stupid arguments...). One of my pet peeves is that I can't apply aggregate functions such as MAX and MIN to GUIDs, which, believe or not, can be handy thing to do some times, such as when eliminating duplicates. You have to cast the GUID as char(36) first, which throws any indexes out the window.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •