I have SQL server 2014 running on my computer and a database that has a compatibility_level of 100 (SQL Server 2008). I thought that that would be sufficient for writing queries in SSMS that only work for SQL Server versions 2008 and lower but for some reason SSMS runs queries that use OVER (PARTITION BY ...) clauses (with the BETWEEN argument) which are only for 2012 and up.
Our software needs to be compatible with 2008 R2 and up. Is there a setting that I can change that will cause my queries to fail in SSMS if I write a query that is not compatible with 2008 R2?
Best Answer
Besides doing your research on what features you should deliberately not use, the only reasonable solution here is to have a robust testing structure where you run your application against all possible versions of SQL server that you're intending to be compatible with.
Even for queries that might work on multiple SQL versions, they might behave slightly differently.
99 times out of 100, you're unlikely to see new versions of SQL break existing code, but there is a difference between "(shrug) it'll probably work" vs "my application is fully tested and supported on the following SQL versions:..." If you're throwing a helpful script on your blog, the former is probably ok. But if you're selling and supporting a commercial application, you definitely want the latter.