0

When I execute this command,

USE MyDB
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

It throws the error -

Incorrect syntax near 'IDENTITY_CACHE'.

Thom A
  • 88,727
  • 11
  • 45
  • 75
sukesh
  • 2,379
  • 12
  • 56
  • 111
  • 2
    `IDENTITY_CACHE` as a database property was added in SQL Server 2017; it doesn't exist in 2014. See the 3rd bullet point in [What's new in SQL Server 2017 - SQL Server 2017 Database Engine](https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2017?view=sql-server-ver15#sql-server-2017-database-engine) – Thom A Dec 01 '20 at 11:59
  • 1
    use a sequence instead of identity or trace flag 272 (not database scoped) – Martin Smith Dec 01 '20 at 12:02
  • 2
    The equivalent setup for 2014 would be to turn on [trace flag 272](https://docs.microsoft.com/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql#trace-flags). But this cannot be scoped to a database and is inherently more clumsy to configure, of course (hence why 2017 makes it more accessible). Also, consider carefully if you're doing this for compatibility purposes (an earlier application relies on the behavior and can't be easily changed) or just because bad assumptions are being made about identities being contiguous that shouldn't be made (TF 272 doesn't solve all). – Jeroen Mostert Dec 01 '20 at 12:03
  • Does this answer your question? [Identity increment is jumping in SQL Server database](https://stackoverflow.com/q/14146148/2029983) – Thom A Dec 01 '20 at 12:12

0 Answers0