would like to know more about identity_cache

  • sram24_mca

    SSCrazy

    Points: 2631

    Can anyone tell about identity_cache pros and cons in detail?

    Regards,

    Ram

  • scdecade

    SSC Eights!

    Points: 807

    Afaik and imo there are no pros only cons.  There is no such thing as a "gap" in a primary key because 'unit consistency' is not a necessary attribute.  Maybe others have a different opinion

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    kinda of the same opinion.. it's really an unnecessary feature in 99% of environments

    missing a few identity values is no big deal... and to be fair, deletes will cause those gaps also. In my opinion I wouldn't bother, but there is no valid reason not to use it

     

    MVDBA

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719198

    Maybe you (sram24_mca) could explain more about why you asked the question or what you are thinking here.

  • Jeff Moden

    SSC Guru

    Points: 996502

    scdecade wrote:

    Afaik and imo there are no pros only cons.  There is no such thing as a "gap" in a primary key because 'unit consistency' is not a necessary attribute.  Maybe others have a different opinion

    I have worked with some folks that would disagree with us on that.  They (some auditors that need to be take out for a pork chop dinner) think that any missing values in an ever-increasing column, such as an IDENTITY or SEQUENCE column means that someone deleted rows in an unauthorized fashion.

    --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.
    "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)
    Forum FAQ

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    Jeff Moden wrote:

    scdecade wrote:

    Afaik and imo there are no pros only cons.  There is no such thing as a "gap" in a primary key because 'unit consistency' is not a necessary attribute.  Maybe others have a different opinion

    I have worked with some folks that would disagree with us on that.  They (some auditors that need to be take out for a pork chop dinner) think that any missing values in an ever-increasing column, such as an IDENTITY or SEQUENCE column means that someone deleted rows in an unauthorized fashion.

    I'm not sure what a pork chop dinner is... I googled it and got a lot of recipies

    I suppose your comment does bring up the issue of "soft deletes" - putting a "deleted flag" on a row - still doesn't stop failed inserts from creating ID gaps

    MVDBA

  • scdecade

    SSC Eights!

    Points: 807

    Jeff Moden wrote:

    scdecade wrote:

    Afaik and imo there are no pros only cons.  There is no such thing as a "gap" in a primary key because 'unit consistency' is not a necessary attribute.  Maybe others have a different opinion

    I have worked with some folks that would disagree with us on that.  They (some auditors that need to be take out for a pork chop dinner) think that any missing values in an ever-increasing column, such as an IDENTITY or SEQUENCE column means that someone deleted rows in an unauthorized fashion.

    Sometimes it's true you have to deal with perceptions.  In my case the financial transactions all come from Stripe over the interwebs so all kinds of things can go wrong.   We use 'double confirmatory transactions' inside a whole bunch of network resiliency and logging.  If an auditor looked at the broken sequence of our identity keys and it was foisted upon me as an accusation... yeah, in some way I'd change my tune.  "Hey there's this great feature called identity_cache and we just have to turn it on!"  Maybe not that!  And Mike is correct in that it's not a guarantee it's more like writing the last id # down on a napkin.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    an interesting point... using that feature to mask missing identity rows for audit … is that not unethical? just asking for opinions

    MVDBA

  • Jeff Moden

    SSC Guru

    Points: 996502

    MVDBA (Mike Vessey) wrote:

    Jeff Moden wrote:

    scdecade wrote:

    Afaik and imo there are no pros only cons.  There is no such thing as a "gap" in a primary key because 'unit consistency' is not a necessary attribute.  Maybe others have a different opinion

    I have worked with some folks that would disagree with us on that.  They (some auditors that need to be take out for a pork chop dinner) think that any missing values in an ever-increasing column, such as an IDENTITY or SEQUENCE column means that someone deleted rows in an unauthorized fashion.

    I'm not sure what a pork chop dinner is... I googled it and got a lot of recipies

    I suppose your comment does bring up the issue of "soft deletes" - putting a "deleted flag" on a row - still doesn't stop failed inserts from creating ID gaps

    Some of the folks saw a post I was involved in well over a decade ago and still identify with it.  To make a really long story much shorter, some manager was whining about how to control a Developer.  It appeared that he had little in the form of calcium in has back (no spine).  Everything that people offered him as a suggestion only increased his whining because the Developer was (in his words, not mine) "valuable".

    I wrapped up my conversation by explaining that sometimes you have to take such people out for a "pork chop dinner"... which occurs in the woodshed... and the developer is tied to a chair... and you feed the developer frozen pork chops using a 3 banded Wrist-Rocket slingshot at point blank range to keep his attention between explanations.

    That gave birth to terms like "pork chop launcher" and "high velocity pork" and, of course, "pork chop dinner".  It's definitely an "inside" understanding by some of the very long term denizens of SQLServerCentral.

    --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.
    "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)
    Forum FAQ

  • Jeff Moden

    SSC Guru

    Points: 996502

    MVDBA (Mike Vessey) wrote:

    an interesting point... using that feature to mask missing identity rows for audit … is that not unethical? just asking for opinions

    Identity cache doesn't work that way.  Identity cache creates/preallocates values for an identity column ahead of time (1,000 or so IIRC).  If you restart the service, any unused identities are lost.  Further, the cache does absolutely nothing to prevent gaps in the face of deletes or rollbacks.

    Supposedly, it makes INSERTs faster.  I've not tested that.  IIRC, the default is "ON" and I just don't care because I never rely on gapless values in an IDENTITY column except for Temp Tables that I've specifically built for a method that requires such a thing.  Normally, I'll use ROW_NUMBER() such things since it came out.

    --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.
    "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)
    Forum FAQ

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719198

    IIRC, it makes concurrent inserts faster since each person doesn't necessarily create as much locking. Not sure it helps with just a couple connections.

     

    Auditors to get worried, and rightly so. That being said, I usually have success mollifying them  with rational explanations.

  • scdecade

    SSC Eights!

    Points: 807

    select * from sys.database_scoped_configurations;

    This tells the settings.  Since for a while identity_cache ON has been the default.  I checked Azure Sql is set to ON by default.  Since it's on by default I would change my initial response to "there's no reason to turn it off."

    Regarding pork chop dinners.  I'm from New Jersey so I understand.

     

  • jcelko212 32090

    SSCrazy Eights

    Points: 8996

    I'm not sure this is still true, but at one point several countries required that invoice numbers and other commercial documents have sequential numbering. This was a legal problem, not a computing problem.

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

  • Jeff Moden

    SSC Guru

    Points: 996502

    jcelko212 32090 wrote:

    I'm not sure this is still true, but at one point several countries required that invoice numbers and other commercial documents have sequential numbering. This was a legal problem, not a computing problem.

    I don't know where that thread is but I remember you saying that before, Joe.  That's definitely one spot where gapless numbers would be required and I'm pretty sure that I'd not entrust either the IDENTITY property of a column or a SEQUENCE to do it on a guaranteed-to-never-fault basis.

    Thanks for the reminder, Joe.  Really good point.

    --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.
    "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)
    Forum FAQ

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

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