4

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?

Improve this question
1
  • 3
    SSMS runs the queries that you write for it to execute. If it's creating its own queries using windowing functions, it may have become sentient and you need to talk to Microsoft immediately.
    – alroc
    Jun 9, 2017 at 1:46

4 Answers 4

Reset to default
9

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.

Improve this answer
0
2

One confusing thing is that both SQL Server 2008 and SQL Server 2008 R2 both have the same compatibility level, 100. See MS documentation here. This link also contains a chart describing the restrictions that compatibility level imposes.

That said, the OVER clause with (PARTITION BY...) is available for both these versions. See MS documentation here.

Improve this answer
1
  • Wow, you're definitely right. however there are a few arguments that are for 2012 and up only. I when I was trying this I must have been using one of them. As for the version number, I thought that was a little weird too. My concern is that it runs in at least 2008 R2 so running in 2008 would be fine as well. Thanks!
    – Jmaurier
    Jun 8, 2017 at 21:00
2

Changing the compatibility level only enables/disables certain database behaviors, it doesn't make the database act as if it is running on that version of SQL Server. For example, new 2014 t-sql features are still available for a database running on SQL 2014 under a lower compatibility level, but the new cardinality estimator is not.

Check the MSDN doc to see exactly what each compatibility level enables/disables. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level

Improve this answer
1
  • I'm failing to see the value of this answer, to be honest. The first paragraph merely summarises what the OP already knows, and the second one could be either a comment or an edit to BradC's answer.
    – Andriy M
    Jun 9, 2017 at 9:04
1

You could always install SQL 2008 R2 as a named instance, and develop against that. If you are actually supporting that version and up, it probably wouldn't hurt to run your code against different instances to see they are reasonably similar, as just because SQL accepts the queries doesn't mean it will even run the same way between versions.

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.