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?
Thanks!
May 20, 2020 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 21, 2020 at 8:34 am
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.
May 21, 2020 at 3:20 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 22, 2020 at 6:48 am
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).
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.
May 22, 2020 at 4:35 pm
Thank you all for your answers. We will have to adopt some of the measures that you indicate.
May 22, 2020 at 4:47 pm
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)
Cheers!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply