Why compatibilty level is important

Question

Why compatibilty level is important

forsqlserver

SSCoach

Points: 18902
More actions
June 9, 2011 at 1:22 am

#251478

Why compatibilty level is important?
Thanks

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://www.google.com/search?q=%22compatibility+level%22

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

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 2

Basically, if the compatibility level is set to a lower value than the current value of the server, you will have reduced functionality in that database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

GSquared SSC Guru Points: 260824 More actions · Answer 3

Because of what it is and what it does.

Do you have a more specific question? There's a lot to the subject of compatibility levels, what they do, what they don't do, etc.

(I always find it interesting how many people think setting a compatibility level back a version will allow them to restore a backup to a prior version of SQL Server.)

- 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

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 4

It changes the set of rules that the Optimizer uses to validate your queries and to create execution plans.

Here is the MSDN take

http://msdn.microsoft.com/en-us/library/cc280407.aspx

It is a Myth that functionality is reduced, unless that functionality is in the Relational Engine.

http://www.sqlballs.com/search/label/backwards%20Compatibility

So far I've been able to user TDE, Compression, and Snapshots with a DB taken from 2000 and left in 80 compatibility (But all those are managed by the Storage Engine, so Test, Check, and re-test unless you KNOW what part of the engine is handling what you are attempting).

Certian Data Types can be restricted but I think there are ways to specify them you must use ANSI Standards.

I'M NOT ADVOCATING IT, JUST SAYIN!

Compatibility mode is supposed to be there to allow a quicker upgrade path, and provide developers more time to fix thier Code.

SQL only supports 3 Compat levels, so Denali will no longer support compat lvl 80. The goal isn't to leave code there, but to give you more time to get on a higher (or supported version) and not have to alter all of your app code.

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field