Our production SQL Server are running on version 2014 (12.0.6433.1) and our development SQL Server is version 2019 (15.0.4033.1).

The development database is set to compatibility level 120 to simulate production. A script developed against the dev database failed in production as the syntax was only introduced in SQL Server 2016.

The example below executes in SSMS (18.5) against the development database with compatibility level 120 despite it being introduced SQL Server 2016 in version.

CREATE TABLE [dbo].[Compatability120_TEST] (ID INT)
GO

DROP TABLE IF EXISTS [dbo].[Compatability120_TEST]
GO

Should setting the compatibility level have prevented it working in development?

CodePudding user response：

No. The purpose of compatibility levels is not to provide a 100% emulation of old versions. There is no previous behavior for DROP TABLE IF EXISTS that backwards compatibility needs to be preserved for so it is allowed. This is generally useful as it allows you to use the new syntax in new code. Seems like you need a test pre-production environment that is configured as prod.

If you want proper version checks, use database projects. Setting the target version to 2016 will check for syntax that is not supported there (and building the project will generate a script that only uses 2016 syntax -- barring some bugs). You can also use LocalDB installs of the proper versions if you need a more solid test.