Identity column as Primary Key - good or bad?

  • Mr. Brian Gale

    SSC-Insane

    Points: 22474

    Jeff Moden wrote:

    Mr. Brian Gale wrote:

    Are you going to have more than 2 million records in the table? If so, probably want that to be a bigint.

    Heh... I know you meant BILLION but thought I'd point it out for any newbies that might read this. 😀

    Yes, that was a typo.  Had a few things break at work as I was typing up that reply, so had it half written and then came back to it later and tried to just continue my train of thought.  Yes, it should say billion.  Good catch with that!  Thanks 😀

    The other fun thing with Identity is that it doesn't get reset if you do a "DELETE [dbo].[table_name]".

  • roger.plowman

    SSChampion

    Points: 10185

    Mr. Brian Gale wrote:

    Jeff Moden wrote:

    Mr. Brian Gale wrote:

    Are you going to have more than 2 million records in the table? If so, probably want that to be a bigint.

    Heh... I know you meant BILLION but thought I'd point it out for any newbies that might read this. 😀

    Yes, that was a typo.  Had a few things break at work as I was typing up that reply, so had it half written and then came back to it later and tried to just continue my train of thought.  Yes, it should say billion.  Good catch with that!  Thanks 😀

    The other fun thing with Identity is that it doesn't get reset if you do a "DELETE [dbo].[table_name]".

    That's actually a plus. 🙂 True names, and all that. Is the lack of resets also true for the TRUNCATE verb?

     

    • This reply was modified 1 month, 2 weeks ago by  roger.plowman. Reason: Spelling
  • Jeffrey Williams 3188

    SSC Guru

    Points: 88215

    roger.plowman wrote:

    That's actually a plus. 🙂 True names, and all that. Is the lack of resets also true for the TRUNCATE verb? 

    No - truncate will reset the identity value to the original seed value.  If the original seed was 1 - it will reset it back to 1, if the original seed value was 1000000 - it will reset it to 1000000.

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Jeff Moden

    SSC Guru

    Points: 995468

    Of course, you can do an immediate reseed to whatever number you want after that.  Heh... and if you want the table to handle up to 4 Billion rows, start the seed at minus 2 Billion.

    Other fun things about TRUNCATE vs DELETE is that TRUNCATE...

    1. Is almost instantaneous because it only deallocates pages instead of deleting data.
    2. Is mistakenly called minimally logged.  It's NOT minimally logged.  The page deallocations are fully logged and you don't need to meet any requirements for any of that to be true.
    3. Is thought unable to be rolled back.  It can be rolled back if with ROLLBACK if you have implicit transactions running (I recommend you almost never do that) or you use an explicit BEGIN TRANSACTION (or you  somehow stop the command in the middle while it's running, which is almost impossible for a human to be fast enough to do.
    4.  Cannot be used in the presence of Foreign Keys (something pointing at table even if that something has no rows).

    I know you folks already know that... just mentioning it for future newbies that might read this.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090

    SSCrazy Eights

    Points: 8906

    >> THIS coming from the person that says he likes the MySQL "standard" of using YYYY-00-00 as the notation for whole years and YYYY-MM-00 for whole months. What a line of hooie. <<

    The MySQL proposal is well understood and not something I made up locally. I'm forcing this kind of thing only because SQL Server does not have INTERVAL temporal datatypes. The nice part about this is my lookup table of date ranges can be quickly converted when SQL Server comes up to standard. You might also remember that I was the guy that wrote "LEFT OUTER JOIN" Versions of the old *= extended equality outer joins so that when Microsoft caught up the rest of the world, they could simply uncomment my code.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 5 posts - 31 through 35 (of 35 total)

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