Thursday, December 23, 2004

Database Compatibility ...

Database Compatibility

This article would explain a practical way of using the system stored procedure "sp_dbcmptlevel".

Sp_dbcmptlevel :: Set the database compatibility level.

--------------------------------------------------------------------------------
By default,
for SQL Server 6.5 the comatibility level is 65,
for SQL Server 7.0 the comatibility level is 70,
for SQL Server 2000 the comatibility level is 80
--------------------------------------------------------------------------------


One can check their database compatibility level by executing the sp_dbcmptlevel system stored procedure.

Let me explain the way I made use of this system stored procedure in my previous company. We were having SQL Server 2k in our development environment but for a project our requirement were to use only SQL Server 6.5 (hmm quite old isn't it? when did you last heard about SQL Server 6.5 :) ). Its for sure that we can't purchase 6.5 version for this project alone. At that time sp_dbcmptlevel came to our rescue. We just executed the below query so that our database (get it right, its database level and NOT server level change) compatibility would be set to SQL Server 6.5

-- Replace Pubs with your actual database name
EXEC sp_dbcmptlevel 'pubs', 65

This way if by mistake you use any keywords which are not in version 6.5 it would throw an error. Once the project is done or anytime during the course of the project you can reset the compatibility level back to 80.

For testing purpose

1. EXEC sp_dbcmptlevel 'pubs'
This would give you the current compatibilty information for pubs database

2. EXEC sp_dbcmptlevel 'pubs', 65
This would set the compatibility to SQL Server 6.5 for Pubs database alone.

3. Select top 5* from authors
After executing point 2 if we run the above query it would throw an error because TOP keyword is not available in SQL Server 6.5

4. EXEC sp_dbcmptlevel 'pubs', 80
This would reset the compatibility of pubs database to SQL Server 2000. Now execute the
query mentioned in point 3 to see the top 5 records displayed.

For further reading:

I suggest you have a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_5c8c.asp for more details on this topic.

No comments: