1

In SQL Server an identity column can jump by 10000 if we restart the service. To resolve this problem I disable the IDENTITY CACHE at SQL Server instance level.

But is there in disadvantage of this?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Rajdip Busa
  • 68
  • 1
  • 9
  • 3
    There can be gaps in the identity values even without the cache (due to e.g. transaction rollback). So ask yourself *why* you're trying to prevent these jumps in the first place. – Damien_The_Unbeliever Sep 06 '21 at 06:29
  • 1
    Why are you even asking? You'll still get gaps but performance will suffer. Gaps aren't a problem, unless they're misused. `IDENTITY` is only meant to produce incrementing values for PKs, not generate eg sequential invoice numbers. If you want to do that, use `SEQUENCE` instead – Panagiotis Kanavos Sep 06 '21 at 07:39
  • `To resolve this problem` what problem are you trying to resolve in the first place? Especially `if we restart the service.`? If you restart the server all active transactions will be rolled back and the IDs they got will never be stored. – Panagiotis Kanavos Sep 06 '21 at 07:48
  • I have no problem with gaps that is generated by transaction(here we have reason that transection is rollbacked), main problem is gap(for int 1000 and for bigint 10000) generated when we restart the service(this gap is unnecessary). – Rajdip Busa Sep 06 '21 at 07:55
  • 2
    There is *technically* a performance penalty. But the cost of generating a new identity value is in the noise of performance metrics. You will not experience any performance issues by returning to the SQL Server 6.5, 7, 2000, 2005, 2008 default. It only got introduced in 2012 - to solve a problem no one was experiencing. And as a result created thousands of problems that everyone has to Google and solve over and over. It's fine to turn it off - it's micro-optimization that should never have been turned on. – Ian Boyd Mar 03 '22 at 19:15

2 Answers2

1

IDENTITY CACHE is a database level scoped configuration. You can disable it using below command.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

It is ON by default. If you disable it, there will be impact to INSERT performance.Reference MSDN article

IDENTITY_CACHE = { ON | OFF }

APPLIES TO: SQL Server (Starting with SQL Server 2017 (14.x)), Azure SQL Database and Azure SQL Managed Instance

Enables or disables identity cache at the database level. The default is ON. Identity caching is used to improve INSERT performance on tables with identity columns. To avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. This option is similar to the existing Trace Flag 272, except that it can be set at the database level rather than only at the server level.

Note:

  • If you want to disable at the instance level, you have to use trace flag 272.
  • Even without the cache option, there can be gaps in identity values, due to many scenarios. Refer to SO Post
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
0

Quoting the documentation

Identity caching is used to improve INSERT performance on tables with identity columns.

So, if you disable the cache you'll be causing a performance penalty. And, as others have said in the comments it doesn't even solve the (perceived) problem you have. Sounds like lose/lose to me.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68