Enhanced Variable Declaration in SQL Server 2005 Mode

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Enhanced Variable Declaration in SQL Server...

16 posts, Page 1 of 2

1 2 »»

Enhanced Variable Declaration in SQL Server 2005 Mode

Rate Topic

Display Mode

Topic Options

Author

Message

Michael Fried

Posted Wednesday, December 12, 2012 8:41 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, December 14, 2012 2:42 PM
Points: 947, Visits: 62

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

Post #1395711

anthony.green

Posted Wednesday, December 12, 2012 8:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

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

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1395715

bleroy

Posted Wednesday, December 12, 2012 9:00 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:22 AM
Points: 141, Visits: 558

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 Cool

B - I had a spare tuppence - L

Post #1395735

Michael Fried

Posted Wednesday, December 12, 2012 9:21 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, December 14, 2012 2:42 PM
Points: 947, Visits: 62

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.

Post #1395761

Michael Fried

Posted Wednesday, December 12, 2012 9:23 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, December 14, 2012 2:42 PM
Points: 947, Visits: 62

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

Post #1395762

GilaMonster

Posted Wednesday, December 12, 2012 10:28 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 38,099, Visits: 30,393

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 2008, MVP
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

Post #1395794

Michael Fried

Posted Thursday, December 13, 2012 9:52 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, December 14, 2012 2:42 PM
Points: 947, Visits: 62

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

Post #1396277

GilaMonster

Posted Thursday, December 13, 2012 10:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 38,099, Visits: 30,393

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 2008, MVP
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

Post #1396280

Michael Fried

Posted Thursday, December 13, 2012 1:08 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, December 14, 2012 2:42 PM
Points: 947, Visits: 62

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.

Post #1396363

GSquared

Posted Thursday, December 13, 2012 1:32 PM

SSCoach

Group: General Forum Members
Last Login: Monday, June 17, 2013 1:45 PM
Points: 15,442, Visits: 9,572

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

Post #1396377

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions