Enhanced Variable Declaration in SQL Server 2005 Mode

  • In SQL Server 2008 the new enhanced variable declaration functionality is working nicely (allowing you to assign a value to a variable in the DECLARE Statement). However, this new functionality also works successfully in a database running in SQL Server 2005 mode (Database Options) on a SQL Server 2008 server.

    Our DEV and QA environments are SQL Server 2008 R2. We have a number of databases that are running in SQL Server 2005 mode. Our production system is still running SQL Server 2005 for various business reasons. There are plans to eventually update Production to SQL Server 2008, but not for a while. We have SQL Scripts as part of our development projects that sometimes contain the new enhanced variable declaration functionality. These scripts pass all testing in DEV and QA. When they are deployed in Production errors are encountered.

    It seems there are issues with a database running in SQL Server 2005 mode on a SQL Server 2008 server. This issue seems to defeat one of the purposes of running a database in a previous mode.

    My questions are:

    1) Is this a known issue? If so, is there a workaround with any other settings?

    2) Are there other code issues with the same affect (SQL Server 2008 specific functionality that works in SQL Server 2005 mode, but not on a true SQL Server 2005 server)?

    We are currently inspecting SQL Scripts for this issue (to find and fix the issue before it gets to Production). But it seems there should be a better way.

    Thanks for your responses!

    Mike

  • Running a database in a different versions compatability mode doesnt make the DB a that particular modes type of database.

    Compatability level provides partial backwards compatability only certain things will act as they did in the previous version.

    This is one of the things that is not covered in the partial backwards compatability.

    Database Compatibility Level Option

    To set certain database behavior to be compatible with a specified earlier version of Microsoft SQL Server

  • I hate to point out the bleedin' obvious, but why on Earth would you have a QA environment that is different from your production environment?? Isn't the point of a QA/test env that it replicates as closely as possible the prod env? the only differences really should be possibly at hardware level where your QA server only has (e.g.) 32 Gb of memory whilst your prod server has 128 ....

    Licence-wise, it makes makes no difference really .. you have obviously acquired a 2008 licence, this ought to give you the right to install 2005 on the machine currently hosting your QA 2008 env ... my advice (unrelated to your actual question :hehe:) is to quickly get 2005 on your QA server ... so that next time you develop something on 2008 and you want to see if it works in 2005, you won't have to wait until it hits the prod server 😎

    B - I had a spare tuppence - L

  • Agreed that it is not optimal. However, that is a business decision and I have to deal with the current situation. There are technical reasons that an upgrade to SQL Server 2008 in Prod enviroment has been delayed. The use of Table Variables and Temp Tables has changed quite a bit in SQL Server 2008 (as far as the processing engine) and large reports that run in 20-30 seconds on SQL Server 2005 now run in 20-30 minutes (or more) on SQL Server 2008 (obviously not acceptable). We have a large number of reports that have to be re-factored. This and other issues have delayed the production upgrade.

  • Is there a list of these partially backwards compatible functionality changes documented anywhere?

  • The only thing that the compat level does is make certain query constructs behave as they did in the lower version. This does not mean that new functionality is disabled, only that functionality that has changed behaves the old way and not the new and that things which are not permitted in the new version still work in the lower compat mode (like *= only works with compat mode 80)

    New functionality will work 90% of the time in the lower compat mode. This is intentional and by design.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • OK, thanks for the response. I guess there are two potential views on this setting:

    1) Functionality that has been deprecated (or changed) in a newer version will be reverted back to working in the compatibility mode version.

    2) Functionality of a given version in compatibility mode will actually function like that version.

    I guess I would expect it to function more like a Microsoft Office program. When you save a document in Office 2003 Compatibility mode all 2007 or above features are removed from the document so the document can be opened successfully by Office 2003.

    Mike

  • Michael Fried (12/13/2012)


    I guess I would expect it to function more like a Microsoft Office program. When you save a document in Office 2003 Compatibility mode all 2007 or above features are removed from the document so the document can be opened successfully by Office 2003.

    If it did, then what would happen with a nice large database (let's say 2TB) with a bunch of compressed tables (SQL 2005 and above feature) when someone sets the DB into compat mode 80?

    Compat mode is not 'make this DB into a database of the lower version' it 'behave as if you were a lower version when running queries in this DB'. A database's version always matches that of the server it's attached to. With compat modes you can freely change them as often as you like. It's not a case of 'change up only' as there is with the actual database version.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I understand your overall point.

    behave as if you were a lower version when running queries in this DB

    I would argue that SQL Server is not doing this since the query syntax is different in the lower version than the newer version. It is fact not behaving like it was a lower version when running this query in the database.

  • Michael Fried (12/13/2012)


    OK, thanks for the response. I guess there are two potential views on this setting:

    1) Functionality that has been deprecated (or changed) in a newer version will be reverted back to working in the compatibility mode version.

    2) Functionality of a given version in compatibility mode will actually function like that version.

    I guess I would expect it to function more like a Microsoft Office program. When you save a document in Office 2003 Compatibility mode all 2007 or above features are removed from the document so the document can be opened successfully by Office 2003.

    Mike

    Emphasis added.

    Yes, but when you open your 2003 document in Word 2007, you still see a ribon at the top of the screen, right?

    It's the same thing. Some of it will work, because of the engine, some of it won't. You'll need to research specific features.

    For example, I just created a database in Compat 80 (SQL 2000) on my SQL 2008 R2 Dev Edition instance. I then created a table in that database using the Date datatype, which is new to SQL 2008. It worked. You would't think so, but it does.

    The number of times I've seen people set a database to an earlier compatibility, then back it up, and then try to restore it on a copy of the earlier engine, and get confused as to why it's "broken", is pretty significant. They're expecting "I saved it as Word 2003, so Word 2003 can open it" to apply to databases, but it really doesn't.

    The main thing to know is that compatibility doesn't actually change the fact that it's an SQL 2008 (or whatever) database. It just changes how certain features work, mainly to allow backwards code-compatibility. It's not really intended to keep newer code from working, it's intended to keep older code working even when you shouldn't use it any more.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I'd suggesting not using that particular new "enhancement" at all while you still have ANY older instances / compatability settings.

    It doesn't save you that much anyway. Not worth the potential hassle.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (12/13/2012)


    I'd suggesting not using that particular new "enhancement" at all while you still have ANY older instances / compatability settings.

    It doesn't save you that much anyway. Not worth the potential hassle.

    It's useful when you're writing a script that you want to convert into a proc with parameters that will have default values. One less thing to modify when swapping from script to proc. But you're right, that's not a big enough deal to be worth it in a mixed environment.

    Personally, I'd roll back the QA/staging environment to SQL 2005 till prod is ready to be upgraded, and avoid this whole mess.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Michael Fried (12/13/2012)


    I would argue that SQL Server is not doing this since the query syntax is different in the lower version than the newer version. It is fact not behaving like it was a lower version when running this query in the database.

    It is doing that. It's not doing it the way you think it should, but it is doing that. As I said before, it's not about preventing new functionality, it's about preserving old functionality/behaviour

    This works in compat mode 80, not 90 or 100.

    SELECT * FROM Table1, Table2 WHERE Table1.Column1 *= Table2.Column2

    Hence, if you have that in an app, you can upgrade to SQL 2008 and leave the DB in compat mode 80 and that query will still work.

    This works on a SQL 2008 database, regardless of the compat mode, because it's a new feature, not a change to old behaviour

    SELECT CAST(GETDATE() AS DATE);

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • As GSquared said:

    Personally, I'd roll back the QA/staging environment to SQL 2005 till prod is ready to be upgraded, and avoid this whole mess.

    It is still beyond me why anybody would want to be in the position the OP is, the person responsible for QA who signs off every changes to the code with the statement "Yay, it works on QA, therefor it will work on Prod, this is great!" is deluded ... even if s/he has very good knowledge of version compatibility etc, s/he just does not know for sure things will work as expected .... just sayin'

    B

  • bleroy (12/14/2012)


    As GSquared said:

    Personally, I'd roll back the QA/staging environment to SQL 2005 till prod is ready to be upgraded, and avoid this whole mess.

    It is still beyond me why anybody would want to be in the position the OP is, the person responsible for QA who signs off every changes to the code with the statement "Yay, it works on QA, therefor it will work on Prod, this is great!" is deluded ... even if s/he has very good knowledge of version compatibility etc, s/he just does not know for sure things will work as expected .... just sayin'

    B

    I could be misreading, but I don't think they intentionally ended up where they are. More of a "I followed the directions, but I'm nowhere near your house" kind of thing, than a "Okay, I've arrived in <fill in high crime neighborhood near you here>, in my Rolls Royce, now I just need a parking spot where I can leave it with the engine running and the windows down".

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply