Identity Crisis

  • dun

    Say Hey Kid

    Points: 708

    Comments posted to this topic are about the item Identity Crisis

  • Joy Smith San

    SSC-Insane

    Points: 24877

    Gud question. I had forgotten abt incremental value as I never use it. I always increment identity column with default value.

  • BudaCli

    Hall of Fame

    Points: 3378

    Nice one, realised my mistake after running the qry on management studio.

    What you don't know won't hurt you but what you know will make you plan to know better
  • Carlo Romagnano

    SSC-Insane

    Points: 21713

    San-847017 (10/28/2009)


    I always increment identity column with default value.

    I used a seed = 1 and an increment of 5 and in another table seed = 2 and an increment of 5. So table A always has last digit = 1 or 6 and table B 2 and 7.

    When I reference in table C the two table above, I know thanks to the last digit which table is linked.

  • daniel.surridge

    Old Hand

    Points: 391

    In your explanation 'According to BOL, "If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used."' you omit to say that this is for TRUNCATE.

  • kramaswamy

    SSCoach

    Points: 18135

    bah. automatically assumed it was seeded with increments of 1. i've never used a non-1 increment personally :/

  • Mark Harr

    SSCrazy Eights

    Points: 9760

    Shoot! I had the identity increment right, but forgot about truncate table resetting the seed value.



    Mark

  • Michael Poppers

    SSCrazy

    Points: 2119

    kramaswamy (10/29/2009)


    bah. automatically assumed it was seeded with increments of 1. i've never used a non-1 increment personally :/

    Ditto :-).

  • Michael Poppers

    SSCrazy

    Points: 2119

    Mark Harr (10/29/2009)


    Shoot! I had the identity increment right, but forgot about truncate table resetting the seed value.

    There was a recent question which brought that point out (maybe Mark missed it :-)). I appreciate this question's showing an aspect of IDENTITY() with which I wasn't familiar.

  • SanjayAttray

    SSChampion

    Points: 13157

    Damn. I missed the increment/seed part. Generally we used (1,1) for identity column and its (1,3) in the question.

    SQL DBA.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715107

    I have added the truncate item to the explanation.

  • ck9663

    Right there with Babe

    Points: 763

    SanjayAttray (10/29/2009)


    Damn. I missed the increment/seed part. Generally we used (1,1) for identity column and its (1,3) in the question.

    That hit me, too...:angry:

  • VM-723206

    SSCrazy

    Points: 2964

    Damn, I knew how this would work, and noticed the increment 3 too. My mind kinda' swapped the DELETE and TRUNCATE. Chose 4.

  • BudaCli

    Hall of Fame

    Points: 3378

    VM-723206 (10/30/2009)


    Damn, I knew how this would work, and noticed the increment 3 too. My mind kinda' swapped the DELETE and TRUNCATE. Chose 4.

    Welcome to the party. Not as easy as 1 2 3 ...

    What you don't know won't hurt you but what you know will make you plan to know better
  • rizwan.inam

    SSC Journeyman

    Points: 86

    What is the Difference between Truncate Table and Delete Table

Viewing 15 posts - 1 through 15 (of 17 total)

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