Azure database (compatibility level 100) accepts new operators from 2017

  • manuel314

    SSC Rookie

    Points: 44

    I have a SQL server instance in Azure with 2 databases with compatibility level 100 (sql server 2018R2):

    master      --> 140

    Database1 --> 100

    Database2 --> 100

    When I execute a script against Database1 or Database2 that includes an instruction not supported in 2018R2, for example:

    SELECT TRIM(' Example ')

    The script executes OK in those databases.

    I expected  the typical error: TRIM is not a recognized built-in function name.

    Is there any way to force Azure to validate the sintax using the target database compatibility level?




  • Site Owners

    SSC Guru

    Points: 80376

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.


    SSC Guru

    Points: 112475

    Compatibility level doesn't work like that.

    If you on a 2017 server you get the 2017 syntax.

    Changing the compatibility level is only for some internal things to revert to the old ways, like cardinality estimation and certain DBCC commands.  It's not run the whole database in 2008 mode.


  • Mr. Brian Gale


    Points: 22933

    I was following this thread because I was curious about this too.  Thanks Anthony for the answer to it.

    It also bring up the question, why would you want to restrict the new syntax? TRIM is a nice feature (for example) and much cleaner to read and type out than LTRIM(RTRIM('Example')).  LAG and LEAD are also nice features.  I can't think of a good scenario where you would want to DISABLE the new features.  The only case I can think of would be the opposite where some syntax was removed in a future version and you wanted to use it for whatever reason.

  • manuel314

    SSC Rookie

    Points: 44

    Thank you for your answers. I'm gonna explain you more about why I want this behaviour.

    My problem is that some of our clients already remain in version 2008 R2 and others use modern versions (2012-2014-2017). All of them use our on-premise software product (web application + DB). That's the reason why we must keep compatibility for the lowest SQL version of our customers.

    In development stage we have worked for many years with a "master"  2008 database to keep the 'last version' of our product. In those days there was no trouble, because if a developer tried to make a change not valid in 2008 syntax, he recieved the error when he tried to execute the script to that database.

    Recently, we've moved this database to Azure and after setting the sql 2008 collation, we expected the same behaviour (2008 syntax checking).

    • This reply was modified 1 month, 3 weeks ago by  manuel314.

    SSC Guru

    Points: 112475

    Remember that 2008R2 and below are all now out of mainstream support and extended support.

    SQL 2012 and SQL 2014 are now out of mainstream support.


    These legacy versions of the engine shouldn't really be supported any more I appreciate that your business is to make money but having to keep supporting legacy software is going to have a major overhead to your development processes.

    You're going to have to draw a line somewhere, I would say do it now and in the next release of the software you say you no longer support 2008R2 or below and then start to phase out 2012 and 2014.


    But back to the issue at hand, if you want to have the 2008R2 or below syntax you have to run it on that particular version of the software.  Once you upgrade the SQL version you also upgrade the database, compatibility level is for internal use only it is not for user use.

  • manuel314

    SSC Rookie

    Points: 44

    Thank you all for your answers. We will have to adopt some of the measures that you indicate.

  • Jacob Wilkins

    One Orange Chip

    Points: 27886

    For some official word on this if you would like a reference, check this link, which includes the following:

    New Transact-SQL syntax is not gated by database compatibility level, except when they can break existing applications by creating a conflict with user Transact-SQL code. These exceptions are documented in the next sections of this article that outline the differences between specific compatibility levels.

    (my emphasis)


    • This reply was modified 1 month, 3 weeks ago by  Jacob Wilkins.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply