The database compatibility level determines how certain database behaviors work. For instance, in 90 compatibility, you need to use the OUTER JOIN syntax to do an outer join, whereas in earlier compatibility levels, you can use ‘*=’ and ‘=*’. Contrary to popular myth, all of the behavioral differences ARE documented – in the Books Online section for sp_dbcmptlevel – the SP used to set the compatibility level.
Version of SQL Server that can be reverted to can be one of the following:
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
The compatibly level setting is used by SQL Server to determine how certain new features should be handled
Compatibility level tells SQL Server how to interpret T-SQL commands. When you perform an upgrade from SQL Server 2000 to SQL Server 2005, the upgrade won’t automatically change the compatibility level from 80 to 90. This is because if you need to change the compatibility level then all the SP’s, DTS, functions etc should be migrated to 90 compatibility level since some of the T-SQL statements are removed in 90. You need to manually change the compatibility level after up gradation. As said above query plan changes between compatibility levels
When upgrading databases from an older version of SQL Server using either the backup and restore method or detach and attach method the compatibility level does not automatically change and therefore your databases still act as though they are running using an earlier version of SQL Server. From an overall standpoint this is not a major problem, but there are certain features that you will not be able to take advantage of unless your database compatibly level is changed.
Identifying Compatibly Level
To check the compatibility level of your databases you can use one of these methods:
You can see the compatibility level of all databases by doing:
sp_helpdb
Or
For particular database
sp_helpdb ‘NHS_TESTING’
Changing Compatibility Level
sp_dbcmptlevel [ [ @dbname = ] name ]
[ , [ @new_cmptlevel = ] version ]
–to change to level 80
dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=80
–to change to level 90
dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=90
–or
sp_dbcmptlevel ‘test’, ’80′
sp_dbcmptlevel ‘test’, ’90′
Testing all procs before upgrading to Compatibility Level 90
If you use SQL Server 2005 but your database is still in Compatibility Level 80 (SQL Server 2000), you can use this script to test if any procs or functions won’t work when you upgrade the database.
The script is quite simple. All that it does is to try to recreate a proc with a different name (Temp_TestProc_DeleteMeTemp_TestProc_DeleteMe). If something goes wrong, the script will record the proc name, the code used to create the proc, and the error message. After trying this with all procs in the database, the procs that couldn’t be created will be listed. Those will be the ones that must be fixed before upgrading the database to compatibility level 90.
A word of caution:
1. If the problem is inside of a dynamic SQL code, it won’t be detected.
2. This script is intended to find incompatibility only in procs and functions. You still have to check codes used to create triggers and views.
Script
DECLARE @sql VARCHAR(max),
@Text VARCHAR(max),
@ProcName VARCHAR(200),
@ProcName1 VARCHAR(200)
DECLARE @T TABLE (ProcName VARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))
DECLARE c Cursor FOR
SELECT O.Name, C.Text
FROM sysobjects O
JOIN syscomments C ON o.ID=C.ID
WHERE O.XType IN (‘P’,'TF’,'FN’)
and C.text IS NOT NULL
ORDER BY O.Name, C.colid
Open C
FETCH NEXT FROM c INTO @ProcName, @Text
SET @sql=@Text
SET @ProcName1=@ProcName
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM c INTO @ProcName, @Text
IF @@FETCH_STATUS = 0 AND @ProcName1=@ProcName BEGIN
SET @sql=@sql+@Text
END ELSE BEGIN
SET @sql = REPLACE(@sql, @ProcName1, ‘Temp_TestProc_DeleteMe’) — change proc name
IF EXISTS (SELECT * FROM sysobjects WHERE Name=’Temp_TestProc_DeleteMe’ AND XType=’P') EXEC(‘DROP PROC Temp_TestProc_DeleteMe’)
IF EXISTS (SELECT * FROM sysobjects WHERE Name=’Temp_TestProc_DeleteMe’ AND XType IN (‘TF’,'FN’)) EXEC(‘DROP FUNCTION Temp_TestProc_DeleteMe’)
BEGIN TRY
EXEC(@sql) — try to create the proc
END TRY
BEGIN CATCH
INSERT @T values (@ProcName1, @sql, ERROR_MESSAGE()) — record procs that couldn’t be created
END CATCH
print @ProcName1
SET @sql=@Text
SET @ProcName1=@ProcName
END
END
CLOSE c
DEALLOCATE c
IF EXISTS (SELECT * FROM sysobjects WHERE Name=’Temp_TestProc_DeleteMe’ AND XType=’P') EXEC(‘DROP PROC Temp_TestProc_DeleteMe’)
IF EXISTS (SELECT * FROM sysobjects WHERE Name=’Temp_TestProc_DeleteMe’ AND XType IN (‘TF’,'FN’)) EXEC(‘DROP FUNCTION Temp_TestProc_DeleteMe’)
SELECT * FROM @T
Hi buddy, your blog’s design is simple and clean and i like it. Your blog posts are superb. Please keep them coming. Greets!!!
:’* that seems to be a great topic, i really love it .;*
Some tips i have observed in terms of pc memory is there are specs such as SDRAM, DDR and many others, that must fit in with the specs of the motherboard. If the computer’s motherboard is kind of current and there are no main system issues, changing the memory space literally requires under 1 hour. It’s among the list of easiest laptop upgrade types of procedures one can consider. Thanks for discussing your ideas. .
Good post. I be taught one thing more challenging on different blogs everyday. It would at all times be stimulating to learn content material from other writers and apply just a little one thing from their store. I’d desire to make use of some with the content on my weblog whether or not you don’t mind. Natually I’ll give you a hyperlink on your internet blog. Thanks for sharing.