SQL Server 2008 compatibility mode

Question

SQL Server 2008 compatibility mode

Jpotucek

SSCoach

Points: 18583
More actions
December 19, 2014 at 1:14 pm

#313190

I recently restored a group of SQL Server 2005 Databases to a SQL Server 2008R2 Instance and I would like to now change the compatibility mode on these databases to full 2008 Compatibility mode:
USE [master]
GO
ALTER DATABASE [Admin] SET COMPATIBILITY_LEVEL = 100
GO
If I find I need to revert back to 2005 compatibility mode, is it really as simple as running the Alter Database command again like this:
USE [master]
GO
ALTER DATABASE [Admin] SET COMPATIBILITY_LEVEL = 90
GO
???
Or would I need to restore the databases????

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

nope it's just a command, but remermber what it does:

all it does is limit queries that use that database to a follow a specific syntax/set of rules....it has nothing to do with the way the database is stored.

the moment you restored that database on 2008, it's behind-the-scenes structure was modified to be 2008. all you are doing is trying to limit the syntax calls to the database,

Examples are things like limiting referneces datatypes that didn't exist, or allow sloppy join critieria that you could get away with in 2005, but was restricted in 2008.

but if you refer to a dmv that exists in 2008, but did not in 2005, it's going to work ; the limitation is syntax only.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jpotucek SSCoach Points: 18583 More actions · Answer 2

Thank you!!! So just to be clear, it sounds like if our APP Dev Team is not happy with the Full 2008 (100) compatibility, I can easier revert back to 2005 Compatibility mode.

Lowell SSC Guru Points: 323504 More actions · Answer 3

yes that is correct. you cna change the compatibiolity level at will, and it instantly, dynamically starts affecting the next query that it encounters.

In my experience, there's not much that breaks. way back when i upgraded, i had a single query in an app, that was using a correlated sub query, but was grouping by or ordering by a column that wa sinside the correlated subquery...you could get away with it in 2005, but in 2008, it raised an error.

it was one of those quick fixes of the code and redeploy issues, but for a week or so, i kept compatibility at a level down till we fixed it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jpotucek SSCoach Points: 18583 More actions · Answer 4

Jpotucek

SSCoach

Points: 18583

December 19, 2014 at 2:20 pm

#1765807

Thank you!