what is use of "sp_dbcmptlevel" , Can anyone explain briefly? its for change compatibility version for one to another , why we go for change?

more ▼

asked Aug 13, 2010 at 07:12 AM in Default

avatar image

11 4 4 5

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

This procedure allows you to set the database compatibility level. Each level corresponds to the version of SQL Server:

  • 60 - SQL Server 6.0

  • 65 - SQL Server 6.5

  • 70 - SQL Server 7.0

  • 80 - SQL Server 2000

  • 90 - SQL Server 2005

  • 100 - SQL Server 2008

This procedure is in the list of features which will be deprecated in feature versions. For now you can still use it, but it is ill advised in most cases. For example, if you have SQL Server 2005 database and force its compatibility level to 80 (which is SQL Server 2000 normal level) then you loose the ability to implement CROSS APPLY, PIVOT, UNPIVOT in your T-SQL, as well as ability to use DMV. Sometimes, people lower the compat level because they are afraid that the new version will break some of the existing queries. In any case, you should not set the compat level to more than 2 versions behind. For example, SQL Server 2005 database should not be attempted to operate in compat 65, etc.


more ▼

answered Aug 13, 2010 at 08:05 AM

avatar image

20.6k 3 7 29

And this is applied per each database, isn't that right? So on one SQL Server server, you could have databases co-existing with different compatability levels? (How do you guys get around saying the apparently redundant "SQL Server server?") :-)

Aug 13, 2010 at 08:46 AM Mark

@Mark By using select distinct words from "SQL Server server", which comes out as SQL Server in any collation which has _CI_ in its name (**c*ase i*nsensitive). Unfortunately, yes, it is possible to have databases with different compat levels on the same instance.

By the way, I forgot to mention that because sp_dbcmptlevel is on the deprecated features list, the recommended approach is to use the following to set compat level:

alter database [the_db_name]
    set compatibility_level = {80 | 90 | 100};

The good news about the latter that compat level less than 80 is not a valid setting :)

Aug 13, 2010 at 09:04 AM Oleg

@Kev Riley I thank you, kind Sir, I fell a victim of a copy paste, which left my post with improper value for the 90 level :)

Aug 13, 2010 at 09:10 AM Oleg

I think it's kind of funny since it's like Honda naming one of its models the "Green Car" or something similar. If you owned one, and you were asked what you drive, you would have to say a "Green Car car?" Hey, it's Friday and I just feel like kidding around a minute. :-)

Aug 13, 2010 at 09:21 AM Mark

@Oleg, you have a good answer, but I do not think it is unforuntate you can have different compatibility levels. I have previously needed this fact during a transition while migrating databases. One of the applications actually did break if it was not set to 6.5 compatibility level. We eventually rewrote it of course, but it was a very convenient short term solution until we had time to rewrite it.

Aug 13, 2010 at 09:54 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 13, 2010 at 07:12 AM

Seen: 2158 times

Last Updated: Aug 13, 2010 at 07:12 AM

Copyright 2018 Redgate Software. Privacy Policy