[Solved] ‘OPTIMIZE_FOR_SEQUENTIAL_KEY’ is not a recognized CREATE TABLE option

The Microsoft SQL Server error ‘OPTIMIZE_FOR_SEQUENTIAL_KEY’ is not a recognized CREATE TABLE option that will be encountered when we try to execute the SQL script in an older version of SQL Server software whereas SQL script is generated by a newer version of the Microsoft SQL server software.

Problem

When the user-generated SQL Scripts 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. In my case, I generated an SQL script using SQL Server 2019 and I tried to run it on SQL Server 2008. OPTIMIZE_FOR_SEQUENTIAL_KEY introduced in a newer version of SQL Server, so this keyword or syntax is not supported in an older version of SQL Server.

Solution

The solution is simple if we analyze the issue properly. After understanding the question, finding the solution is very easy in this case. The solution is to remove the OPTIMIZE_FOR_SEQUENTIAL_KEY line.

If the script is very big, then we can take help of find and replace tool to replace the OPTIMIZE_FOR_SEQUENTIAL_KEY line with space as in below image.

Below image shows the result after the replace execution.

Now after removing the OPTIMIZE_FOR_SEQUENTIAL_KEY lines, I could run the SQL script successfully.

Conclusion

Whenever we try to migrate from one version to other, these kind of issues are common, we just need to analyze the issue properly and that will help us to find the solution easily.

Default image
Sreenivasa Rangan TR
Software professional with 7+ years of experience in all phases of software development with robust problem-solving skills and proven experience in creating and designing software in a test-driven environment.

One comment

  1. You can also goto Option > SQL Server Object Explorer > Scripting and set the Version Options to the desired compatibility level

Leave a Reply

%d bloggers like this: