would like to know more about identity_cache

Question

would like to know more about identity_cache

sram24_mca

SSCrazy

Points: 2633
More actions
March 23, 2020 at 11:57 am

#3736192

Can anyone tell about identity_cache pros and cons in detail?
Regards,
Ram

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

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

Steve Collins SSCrazy Points: 2068 More actions · Answer 1

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

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: 734418 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

Jeff Moden wrote:

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

Steve Collins SSCrazy Points: 2068 More actions · Answer 6

Jeff Moden wrote:

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

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: 1003853 More actions · Answer 8

MVDBA (Mike Vessey) wrote:

Jeff Moden wrote:
Steve Collins 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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

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)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 10

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.

Steve Collins SSCrazy Points: 2068 More actions · Answer 11

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.

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

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 12

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: 1003853 More actions · Answer 13

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)