what is use of "sp_dbcmptlevel" , Can anyone explain briefly? its for change compatibility version for one to another , why we go for change?
asked Aug 13 '10 at 07:12 AM in Default
This procedure allows you to set the database compatibility level. Each level corresponds to the version of SQL Server:
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.