We recently ran into an issue where the SQL our development team is writing is including new features from SQL Server 2012/2014 and it is being allowed by SQL Server 2014 running in 2008 compatibility.
When I run this on the development server
EXEC SP_DBCMPTLEVEL '<insert db>'
GO
IF 1=1
THROW 51000, 'values match', 1;
GO
it returns this
The current compatibility level is 100.
Msg 51000, Level 16, State 1, Line 5
values match
In production, the same SQL shows this:
The current compatibility level is 100.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'THROW'.
We haven't yet upgraded our production SQL Server and are running into deployment issues. THROW is a keyword that is new to SQL Server 2012, so I expect the 2008 server to break on the syntax. It seems that the Compatibility Level is broken to me.
What can be done to prevent incompatible SQL from being allowed on the development environment?