Article
10/01/2010

sp_dbcmptlevel (Transact-SQL)

Sets certain database behaviors to be compatible with the specified version of SQL Server.

Important

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE Compatibility Level instead.

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_dbcmptlevel [ [ @dbname= ] name ] 
    [ , [ @new_cmptlevel= ] version ]

Arguments

[ @dbname= ] name
Is the name of the database for which the compatibility level is to be changed. Database names must conform to the rules for identifiers. name is sysname, with a default of NULL.
[ @new_cmptlevel= ] version
Is the version of SQL Server with which the database is to be made compatible. version is tinyint, with a default of NULL. The value must be one of the following:

80 = SQL Server 2000

90 = SQL Server 2005

100 = SQL Server 2008

Return Code Values

0 (success) or 1 (failure)

Result Sets

If no parameters are specified or if the name parameter is not specified, sp_dbcmptlevel returns an error.

If name is specified without version, the Database Engine returns a message displaying the current compatibility level of the specified database.

Remarks

For a description of compatibilities levels, see ALTER DATABASE Compatibility Level (Transact-SQL).

Permissions

Only the database owner, members of the sysadmin fixed server role, and the db_owner fixed database role (if you are changing the current database) can execute this procedure.

Examples

A. Changing the compatibility level to SQL Server 2000

The following example changes the compatibility level of the AdventureWorks database to 80.

EXEC sp_dbcmptlevel AdventureWorks, 80;
GO

B. Effect of compatibility level on ORDER BY (scenario 1)

The following example illustrates the difference in the ORDER BY binding for the 80 and 90 compatibility levels. The example creates a sample table, SampleTable, in the tempdb database.

USE tempdb;
CREATE TABLE SampleTable(c1 int, c2 int);
GO

In compatibility level 90, the default level, the following SELECT... ORDER BY statement produces an error because the column name in the AS clause, c1, is ambiguous.

SELECT c1, c2 AS c1
    FROM SampleTable
    ORDER BY c1;
GO

After resetting the database to compatibility level 80, the same SELECT... ORDER BY statement succeeds.

sp_dbcmptlevel tempdb, 80
SELECT c1, c2 AS c1
    FROM SampleTable
    ORDER BY c1;
GO

The following SELECT... ORDER BY statement works in both compatibility levels.

sp_dbcmptlevel tempdb, 80
SELECT c1, c2 AS c3
FROM SampleTable
ORDER BY c1;
GO

sp_dbcmptlevel tempdb, 90
SELECT c1, c2 AS c3
FROM SampleTable
ORDER BY c1;
GO

C. Effect of compatibility level on ORDER BY (scenario 2)

In compatibility level 90, the default level, the following SELECT...ORDER BY statement produces an error because there is an additional table prefix in the ORDER BY clause.

SELECT c1 AS x
    FROM SampleTable
    ORDER BY SampleTable.x;
GO

After the database is reset to compatibility level 80, the same SELECT...ORDER BY statement succeeds.

sp_dbcmptlevel tempdb, 80
SELECT c1 AS x
    FROM SampleTable
    ORDER BY SampleTable.x;
GO

The following SELECT...ORDER BY statement works in both compatibility levels.

sp_dbcmptlevel tempdb, 80
SELECT c1 AS x
    FROM SampleTable
    ORDER BY x;
GO
sp_dbcmptlevel tempdb, 90
SELECT c1 AS x
    FROM SampleTable
    ORDER BY x;
GO

Share via