4

We have some DB scripts that need to be migrated from SQL Server 2008 R2 back to 2005, often scripts created in SQL Server 2008 / 2008 R2 won't run on a SQL Server 2005 installation (which some customers still use).

So my question: from your experience/knowledge, is this really necessary, or does setting the compatibility level back to 90 on SQL Server 2008 (2008 R2) fix the issue of unnoticed breaking scripts in SQL Server 2005? MSDN says "compatibility level provides only partial backward compatibility with earlier versions of SQL Server ", so I'm unsure here.

Thanks...

I would like a concrete list of “features will pass compatibility level 90 but break on SQL Server 2005" or link. If the list is short list, we could convert that to an internal "don't do that!" list and save a lot of work.

Improve this question
5
  • I'm not sure what you're asking here. Do you want the scripts to work?
    – Lasse V. Karlsen
    Oct 11, 2011 at 16:36
  • 2
    You need to test your scripts against a 2005 instance. You can't rely on compatibility level to catch everything. Example select CRYPT_GEN_RANDOM(10) will work on 2008 compat 90 but not 2005.
    – Martin Smith
    Oct 11, 2011 at 16:41
  • @Martin Smith: any more examples? would like to get a feeling for how "exotic" this issue is after changing the compat level. Link to a summary would be ideal.
    – hko
    Oct 11, 2011 at 16:59
  • 2
    @hko - What's the point? Just run it against a 2005 instance and see what breaks. Easier than running it against a 90 compat DB and then looking for specific problematic constructs on top of that.
    – Martin Smith
    Oct 11, 2011 at 17:10
  • @Martin Smith the point is, that migrating a sql server 2008 R2 instance with around 10 databases with fulltext catalogs for 20 devs is a bit of work. We don't have a centralized DB script testing for Sql2005, and someone decided that it is better to prohibit such scripts than let them be found by testing.
    – hko
    Oct 12, 2011 at 7:06

2 Answers 2

Reset to default
5

No. The compatibility level only affects some features that exists in both versions. To give an example from ALTER DATABASE Compatibility Level:

Compatibility-level setting of 90 or lower: MERGE is not enforced as a reserved keyword. Compatibility-level setting of 100: MERGE is a fully reserved keyword. The MERGE statement is supported under both 100 and 90 compatibility levels.

As you can see, you can still write a script that uses MERGE, a post SQL Server 2008 only feature, and run it in 90 compatibility mode, and it will work. However, the script will break on SQL Server 2005. The comptability level 90 only affects the enforcement of the keyword (which affects parsing, in 100 mode a table named MERGE would have to be enquoted in brackets as [MERGE]), but not the execution.

There are many more features like this. So your only option is to run against the target SQL Server.

Improve this answer
1
  • +1 for the exapmle. Do you know if the compatibility changes things like the query plans for the different levels?
    – StanleyJohns
    Oct 12, 2011 at 10:02
8

No the compatibility level is to allow deprecated features to work not to prevent you from writing code that won't work on a lower version.

If your production machine is SQL Server 2005, you must develop on a SQL server 2005 machine to ensure that features allowed in 2005 are the only ones in the code.

Of course in Books online there is a page which shows all the features that are new in 2008, so you could simply forbid your developers from using them. And check for them in code reviews, but developing against a correct database is the only real solution.

The list is not short and exotic, the compatibility mode is not intended to allow you to develop on a higher database and deploy to a lower one.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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