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?

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).

Answer 2

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