Syntax error: OPTIMIZE_FOR_SEQUENTIAL_KEY

Question

I am trying to build a SQL Database Project, which was created in Visual Studio, in Azure DevOps build pipeline. However, when I build the database project, I get the following error:

Error SQL46010: Incorrect syntax near OPTIMIZE_FOR_SEQUENTIAL_KEY

I am currently exporting script from SSMS 18, and importing them into my Visual Studio project. The current 'Target Platform' is set to 'Azure SQL Database'.

Note: The script which has the OPTIMIZE_FOR_SEQUENTIAL_KEY is part of my CREATE script as follows:

CREATE TABLE [Table1](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [VALUE1] [bigint] NOT NULL
 CONSTRAINT [ID] PRIMARY KEY CLUSTERED 
(

    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [Table1]  WITH CHECK ADD  CONSTRAINT [Table2FK] FOREIGN KEY([Table2FKID])
REFERENCES [Table2])
GO

Also, the following is the compatibility level as shown in SSMS:

I would really appreciate any help. Thank you.

Answer 1

Delete , OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, will solve the issue.

The reason for this problem is that your local generated sql script is generated in accordance with the latest sql server 2019, and the version of sql server used in devops may be inconsistent, causing the error to occur.

The script I generated on sql server2019 before, run it in sql server2008, and the error occurred is the same as yours.

Answer 2

We ran into this issue with SQL Server 2019 Merge Replication snapshot scripts. Setting the database compatibility level down to 2017 and that fixed the snapshot scripts by removing the "OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF" option. Our subscribers were 2017. So, I would think you will have the same results without the effort and time of removing the option manually.