The IDENTITY Column Property

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4595

    Comments posted to this topic are about the item The IDENTITY Column Property

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • Michael L John

    One Orange Chip

    Points: 25915

    Good job!

    There is one things that could probably be added to this.  An identity is incremented when an attempt to insert is performed.  Even if the insert fails, the identity is incremented.

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • artilugio

    SSC-Addicted

    Points: 415

    Good handy reference. I NEVER use identities in my designs or code, but they exist and you have to deal with them.

  • Michael L John

    One Orange Chip

    Points: 25915

    artilugio wrote:

    Good handy reference. I NEVER use identities in my designs or code, but they exist and you have to deal with them.

    Curiously, why would you NEVER use an identity?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • scdecade

    SSC Eights!

    Points: 802

    It's great to see this article!  I'm going to sit this pie fight out though.  Two items I would toss in fwiw:

    1. The return type of the SCOPE_IDENTITY function is NUMERIC(38, 0) which is colossal sized.  Because (or since?) that's the largest possible primary key you can create in SQL Server.  Bigger than bigint
    2. I have never and I will never use @@IDENTITY. Cattle prods could not get me to use that.  Doing anything based on "session" is fraught with issues imo.  No way
  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4595

    Michael L John wrote:

    Good job!

    There is one things that could probably be added to this.  An identity is incremented when an attempt to insert is performed.  Even if the insert fails, the identity is incremented.

    Thanks so much. Absolutely correct Michael.

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4595

    scdecade wrote:

    It's great to see this article!  I'm going to sit this pie fight out though.  Two items I would toss in fwiw:

     

      <li style="list-style-type: none;">

    1. The return type of the SCOPE_IDENTITY function is NUMERIC(38, 0) which is colossal sized.  Because (or since?) that's the largest possible primary key you can create in SQL Server.  Bigger than bigint

     

      <li style="list-style-type: none;">

    1. I have never and I will never use @@IDENTITY. Cattle prods could not get me to use that.  Doing anything based on "session" is fraught with issues imo.  No way

     

     

    Good points, scdecade. Thanks. You know, this feedback was definitely expected given the amount of material out there about the limitations of IDENTITY. Definitely doing follow up articles  on NEWID() and SEQUENCES.

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • Michael L John

    One Orange Chip

    Points: 25915

    scdecade wrote:

    It's great to see this article!  I'm going to sit this pie fight out though.  Two items I would toss in fwiw:

     

      <li style="list-style-type: none;">

    1. The return type of the SCOPE_IDENTITY function is NUMERIC(38, 0) which is colossal sized.  Because (or since?) that's the largest possible primary key you can create in SQL Server.  Bigger than bigint

     

      <li style="list-style-type: none;">

    1. I have never and I will never use @@IDENTITY. Cattle prods could not get me to use that.  Doing anything based on "session" is fraught with issues imo.  No way

     

    At a previous position, @@IDENTITY was in hundreds of procs.   I did a demo of why this is not a good idea and proved to the devs why and where this was causing bugs.

    Spend forever removing it.

    A big giant new module was developed.  QA failed miserably. Well, the dev's paid no attention, @@IDENTITY was all through this "new" code.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

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

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