Syntax error at: OPTIMIZE_FOR_SEQUENTIAL_KEY

Question

I created a table in Microsoft SQL Server Management Studio and the table worked fine, no errors while building. Then i was copying the script to my project in visual studio when the following message showed:

SQL80001: Incorrect syntax ner 'OPTIMIZE_FOR_SEQUENTIAL_KEY'

I don't know why it happened, but this error was showing on this line of the code:

(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF  )

Do you guys know why the visual studio is showing that error message? How can I fix it?

Bathtub

Are you using SQL Server 2019? Is the version of SSDT you're using configured for 2019 syntax? – Essential

Yes, version 15.0. I'm using Visual studio 2019, so I don't know why that message showed – Bathtub

Answer 1

Make sure the target platform of your database project is SQL Server 2019 or later (including Azure SQL Database), where the OPTIMIZE_FOR_SEQUENTIAL_KEY option was introduced. The syntax is not allowed in earlier versions.

Note this Microsoft article recommends judicious use of OPTIMIZE_FOR_SEQUENTIAL_KEY = ON even when keys are incremental. Relevant excerpt:

If you're not experiencing the convoy phenomenon in your workload, you may not see a huge benefit from this option, and you may even see a slight degradation in performance due to the new flow control waits. You should only use this option if you have a very heavily contentious workload – one where the number of threads inserting into the index is much higher than the number of schedulers – on a clustered index with a sequential key (note that non-clustered indexes can experience this problem as well, but because they have a smaller row size they don’t have as high a tendency to form convoys so they are less likely to benefit from this option).

Grille

Answer 2

The error occurs because of the new syntax not working in the older version of SQL server .so you can apply a solution it will work as you expected If you use SQL server management studio to Generating the script to clone the table structure change the SQL server version from the sql server management studio

Step to generate the appropriate script :

STEP 1 : right-click on the database name as like below image

Step 2 : Then click on the generate script then click next then select specific table name as like image

Step 3 : Then click on the advanced settings as like image

Step 4 : Then select the SQL server version based on you need where you want to place the script :) enjoy

Step 5 : Then set the path where you want to save the script then finish

Mccammon

Thank you! That sql version piece was what I was missing (in advanced options) – Athanasius

Answer 3

When you export SQL script, choose a specific version lower then 2019.

Hadden

Answer 4

Someone handed me a table create SQL script from SQL 2019 that I had to run in SQL 2005. For anyone in this situation, simply removing this from the list of attributes was fine:

OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF 

Seems kind of obvious, but might be helpful to someone.

Kinaesthesia