Gfrktyl

I've got a SQL Server 2016 instance with a database on it, running with a compatibility level of 2008. However, the following dummy proc creates, and executes just fine.

CREATE PROC usp_TestFormat

AS

BEGIN

    SELECT FORMAT(CAST('10/25/2018' AS DATE), 'MMM') AS Month;

END;

However FORMAT() is a 2012+ function, so when I tried to take a stored proc I'd written in above database, or the test proc above even, and deploy it to our production database, that is running on a 2008r2 box, it fails, as it doesn't recognize the function. It was my understanding that compatibility levels would prevent this type of behavior, as I'm now having to go through and re-work the code that uses the format function. Is this a bug, or am I not understanding Compatibility levels as much as I thought?

Is this a bug, or am I not understanding Compatibility levels as much as I thought?

Not a bug. The Compatibility Levels are intended solely to make upgrading easier. IE to prevent errors or changes in behavior when you upgrade to a newer version of SQL Server.

After you upgrade to a new version of SQL Server, you might want to take advantage of new features and capabilities without changing the Database Compatibility Level, so where possible, new TSQL language features are not blocked by your lower Compatibility Level.

This is not a bug, you just misunderstand the compatibility level.

You cannot use features of 2012 such as lead(), lag(), format() in 2008 because they only appeared in 2012.

But you are permitted to use them as you are on 2012, even if database has lower compatibility level.

Here is a BOL article ALTER DATABASE (Transact-SQL) Compatibility Level that states:

Database compatibility level provides only partial backward
compatibility with earlier versions of SQL Server.

The classic scenario for why you might want to use an older database compatibility level is an upgrade to a newer version of SQL Server.

For example, your query plans might change because of use of newer cardinality estimatior, so the execution becomes slower and you want to generate similar plans as you had on the older version. When these queries are not numerous, you can use query hints to use the old estimator at query level, but if these are almost all your procs you want to change your db compatibility level to older one to preserve good (old) plans.

In addition to the other posts: It seems like MS tries to, where possible, add new language elements without making them keywords. If a new language element is a keyword, then you can have objects with the same name, and that will cause confusion. In general, when that happens, the new language element is only available in newer compatibility level (since it requires you to encapsulate the object name in [square brakets] or "double quotes" if you had such. But by not making the new language elements keywords, then can allow for them in lower compatibility level. In general. So, in short, where it doesn't "break" anything and it seems safe, then the language element will be available in lower compatibility levels as well.