We're developing against SQL Server 2019 currently, but we have a need to deploy the same set of SQL scripts to a SQL Server 2014 database.

For example, in SQL Server 2019 a built-in function called TRIM exists but this is not supported in SQL Server 2014. To make sure we're only using syntaxes supported by both databases, I'm wondering if there's a solution to just allow 2014 syntaxes in 2019.

I thought altering compatibility mode of the database would have this effect, but this does not have the intended effect.

  • , but this does have the intended effect. - should there be a not in there? – Vérace Mar 20 at 15:15
  • Woops.. good catch.. fixed – l46kok Mar 20 at 15:18
  • What happens when you alter the compatibility mode - any errors/whatnot? Not a SQL Server man, but I would have thought it was designed for this - unless, perhaps, it's an optimiser thing only - i.e. plans will remain the same, but new functions will work - i.e. TRIM? – Vérace Mar 20 at 15:54
  • Comapt level isn't only an optimizer thing. But, it doesn't give you 100% compat either, since new functions and stuff might be available even with lower compat mode. My suggestion is to use a "real" SQL server 2014. – Tibor Karaszi Mar 20 at 17:16
  • Nope. If you need your users to steer clear of things that won't work in > 2014, then they should develop on 2014. Compatibility level is definitely not the answer here except for very rare exceptions (like STRING_SPLIT). At the very least, you could let them continue developing on 2019, but automate scripting their objects to a 2014 instance (you can't do this with backup/restore or detach/attach, but there are plenty of ways to transfer objects). You'd also have to run all the code, because some problems won't crop up until runtime. – Aaron Bertrand Mar 20 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.