SQL server replication - OPTIMIZE_FOR_SEQUENTIAL_KEY’ is not a recognized CREATE TABLE option

Question

Hi,
I have 2 DB in SQL Server Microsoft SQL Server 2019 (RTM-CU10).
Both DB are published (Merge Replication).
The schema is the same for the 2 DB and also the publication was created using the same script.

The subscribers are SQL Express 2012.

One of the replications fails with error: OPTIMIZE_FOR_SEQUENTIAL_KEY’ is not a recognized CREATE TABLE option

In the snapshot folder, I found the file Area_2.shc:
DB 1 (running OK)
ALTER TABLE [dbo].[Area] ADD CONSTRAINT [Area_PK] PRIMARY KEY CLUSTERED
(
[ID_Area] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

DB 2 (with sync error)
ALTER TABLE [dbo].[Area] ADD CONSTRAINT [Area_PK] PRIMARY KEY CLUSTERED
(
[ID_Area] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, , OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO

The Compatibility Level of both Publications is 100RTM

How can I configure the Snapshot option to generate SQL 2012 compatible scripts.

Thanks in Advance

Answer 1

Hi @Daniel Kacowicz ，

when the user-generated SQL Script from the newer version of SQL Server and try to execute the same script in the older version of SQL Server, then this issue may occur

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF } applies to SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database

So, the solution is to remove the OPTIMIZE_FOR_SEQUENTIAL_KEY line

-------
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".