The IDENTITY Column Property

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4631

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

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • Michael L John

    One Orange Chip

    Points: 25950

    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: 417

    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: 25950

    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/

  • Steve Collins

    Ten Centuries

    Points: 1097

    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

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4631

    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
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4631

    Steve Collins 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
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • Michael L John

    One Orange Chip

    Points: 25950

    Steve Collins 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/

  • Carlo Romagnano

    SSC-Insane

    Points: 22011

    The increment also is out of transactions. So, rollback doesn't restore the original value.

    Also, you can not remove the property IDENTITY from a column.

    This adds the IDENTITY to a column:

    ALTER TABLE TB ALTER COLUMN my_id INT IDENTITY

    but this doesn't remove it, also though no error is given:

    ALTER TABLE TB ALTER COLUMN my_id INT

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

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