OPTIMIZE_FOR_SEQUENTIAL_KEY in 2019 compatible 2017-CodePudding

I have a script like this

CREATE TABLE [dbo].[MYTABLE]
(
    ....
    CONSTRAINT [PK_MYTABLE] 
        PRIMARY KEY CLUSTERED ([Legend_Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

On SQL Server 2017, it does not run (OPTIMIZE_FOR_SEQUENTIAL_KEY is not a recognized CREATE TABLE option)

On SQL Server 2019 (with the database in SQL Server 2017 compatibility mode), it runs successfully.

We are using SQL Server 2019 to validate something that will be executed on SQL Server 2017. I think this is weird and I was expecting a failure also in the SQL Server 2019 version.

Is there a way to force SQL Server 2019 to strictly behave as SQL Server 2017?

CodePudding user response：

This might be useful to you - your script looks to be that which would be generated by SSMS.

In the options, if you go to SQL Server Object Explorer > Scripting there is a Version Options section:

CodePudding user response：

Is there a way to force SQL Server 2019 to strictly behave as SQL Server 2017?

Compatibility mode is there to facilitate upgrades by preventing breaking changes and optimizer enhancements when running a workload tested on an older version and running on a newer version.

But just because your application is running in compatibility mode doesn't mean you can't start using new features introduced in SQL Server 2019. Some changes may be blocked to protect backwards-compatibility, but most will be available to use if you want.