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.
Best Answer
No. If you need your users to steer clear of things that won't work in versions newer than 2014, then they should develop on 2014.
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/data to an older version. You'd also have to run all of the code, because some problems won't crop up until runtime.
Compatibility level is not the answer here, except for very rare exceptions (like
STRING_SPLIT). Compatibility level is not used for allowing / disallowing syntax, in modern versions at least. See this and this. In older versions, yes, a lot of syntax was controlled this way, but that’s not how it works anymore.