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