sp_dbcmptlevel
Sets certain database behaviors to be compatible with the specified earlier version of Microsoft® SQL Server™.
Syntax
sp_dbcmptlevel [ [ @dbname = ] name ]
[ , [ @new_cmptlevel = ] version ]
Arguments
[@dbname =] name
Is the name of the database whose 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 80, 70, 65, or 60.
Note The only difference between levels 70 and 80 is that several reserved keywords introduced in SQL Server 2000 are not supported in level 70.
Return Code Values
0 (success) or 1 (failure)
Result Sets
sp_dbcmptlevel returns this message if no parameters are specified or if the name parameter is not specified:
Valid values of database compatibility level are 60, 65, 70, or 80.
If name is specified with no version, SQL Server displays a message with the compatibility setting for the named database.
Remarks
In SQL Server 2000, the master database has a compatibility level of 80, which cannot be modified.
For installations of all instances of SQL Server 2000, the default level for all databases is 80. For upgrades from SQL Server 7.0 to SQL Server 2000, the default level for all databases is 80. For upgrades from SQL Server 6.5 and SQL Server 6.0 to SQL Server 2000, the existing default compatibility level is retained.
Use sp_dbcmptlevel as an interim migration aid. If existing SQL Server version 6.x applications are affected by the differences in SQL Server version 7.0 or SQL Server 2000 behaviors that are controlled by the compatibility level setting of sp_dbcmptlevel, use this procedure to set the earlier version behaviors until the application can be converted to work properly with the SQL Server 2000 compatibility level. sp_dbcmptlevel does not restore full backward compatibility.
sp_dbcmptlevel affects the behaviors in the specified database, not the entire server. The compatibility setting for a database takes effect when the database is made the current database with the USE statement, or if the database is the default database for the login. When a stored procedure is executed, the current compatibility level of the database in which the procedure is defined is used. All stored procedures in the database are recompiled when the compatibility setting is changed in that database.
Setting the compatibility level to 65 or 60 affects these behaviors. For more information about backward compatible behaviors, see SQL Server Backward Compatibility Details.
| Compatibility level setting of either 60 or 65 | Compatibility level setting of 70 or 80 (default) |
|---|---|
| The result sets of SELECT statements with a GROUP BY clause and no ORDER BY clause are sorted by the GROUP BY columns. | A GROUP BY clause does no sorting on its own. An ORDER BY clause must be explicitly specified for SQL Server to sort any result set. For more information, see SELECT. |
| Columns prefixed with table aliases are accepted in the SET clause of an UPDATE statement. | Table aliases are not accepted in the SET clause of an UPDATE statement. The table or view specified in the SET clause must match that specified immediately following the UPDATE keyword. For more information, see UPDATE. |
| bit columns created without an explicit NULL or NOT NULL option in CREATE TABLE or ALTER TABLE are created as NOT NULL. | The nullability of bit columns without explicit nullability is determined by either the session setting of SET ANSI_NULL_DFLT_ON or SET ANSI_NULL_DFLT_OFF; or the database setting of SET ANSI NULL DEFAULT. For more information, see SET. |
| The ALTER COLUMN clause cannot be used on ALTER TABLE. | The ALTER COLUMN clause can be used on ALTER TABLE. For more information, see ALTER TABLE. |
| A trigger created for a table replaces any existing triggers of the same type (INSERT, UPDATE, DELETE). The WITH APPEND option of CREATE TRIGGER can be used to create multiple triggers of the same type. | Triggers of the same type are appended. Trigger names must be unique. The WITH APPEND option is assumed. For more information, see CREATE TRIGGER. |
| When a batch or procedure contains invalid object names, a warning is returned when the batch is parsed or compiled, and an error message is returned when the batch is executed. | No warning is returned when the batch is parsed or compiled, and an error message is returned when the batch is executed. For more information about deferred name resolution, see CREATE PROCEDURE (Level 4). |
Queries of the following form are properly executed by ignoring table Y and inserting the SELECT statement results into table X.
|
SQL Server returns a syntax error when this same query is executed. |
| The empty string literal (' ') is interpreted as a single blank. | The empty string literal (' ') is interpreted as an empty string. |
| DATALENGTH('') returns 1 ('' parsed as a single space). DATALENGTH(N'') returns 2 (N'' parsed as a single Unicode space). LEFT('123', m) returns NULL when m = 0. LEFT(N'123', m) returns NULL when m = 0. LTRIM(' ') returns NULL. LTRIM(N' ') returns NULL. REPLICATE('123', m) returns NULL when m = 0. REPLICATE(N'123', m) returns NULL when m = 0. RIGHT(N'123', m) returns NULL when m = 0. RIGHT('123', m) returns NULL when m = 0. RIGHT('123', m) returns NULL when m is negative. RIGHT(N'123', m) returns NULL when m is negative. RTRIM(' ') returns NULL. RTRIM(N' ') returns NULL. SPACE(0) returns NULL. SUBSTRING('123', m, n) returns NULL when m < length of the string or when n = 0. SUBSTRING(N'123', m, n) returns NULL when m > length of the string or when n = 0. UPDATETEXT table.textcolumn textpointer > 0 NULL NULL results in a NULL value. |
DATALENGTH('') returns 0. DATALENGTH(N'') returns 0. LEFT('123', m) returns an empty string when m = 0. LEFT(N'123', m) returns an empty string when m = 0. LTRIM(' ') returns an empty string. LTRIM(N' ') returns an empty string. REPLICATE('123', m) returns an empty string when m = 0. REPLICATE(N'123', m) returns an empty string when m = 0. RIGHT('123', m) returns an empty string when m = 0. RIGHT(N'123', m) returns an empty string when m = 0. RIGHT('123', m) returns error when m is negative. RIGHT(N'123', m) returns error when m is negative. RTRIM(' ') returns an empty string. RTRIM(N' ') returns an empty string. SPACE(0) returns an empty string. SUBSTRING('123', m, n) returns an empty string when m < length of the string or when n = 0. SUBSTRING(N'123', m, n) returns an empty string when m > length of the string or when n = 0. UPDATETEXT table.textcolumn textpointer > 0 NULL NULL results in empty text. |
| The CHARINDEX and PATINDEX functions return null only if both the pattern and the expression are null. | The CHARINDEX and PATINDEX functions return NULL when any input parameters are NULL. |
| References to text or image columns in the inserted and deleted tables appear as NULL. | References to text or image columns in the inserted and deleted tables are not allowed. |
| Allows UPDATETEXT to initialize text columns to NULL. | UPDATETEXT initializes text columns to an empty string. WRITETEXT initializes text columns to NULL. |
| The concatenation of null yields null setting of sp_dboption is off (disabled) which returns an empty string if any operands in a concatenation operation is null. | The concatenation of null yields null setting of sp_dboption is on (enabled), which returns a NULL if any operands in a concatenation operation is null. |
| In an INSERT statement, a SELECT returning a scalar value is allowed in the VALUES clause. | The INSERT statement cannot have a SELECT statement in the VALUES clause as one of the values to be inserted. |
| A ROLLBACK statement in a stored procedure referenced in an INSERT table EXEC procedure statement causes the INSERT to be rolled back, but the batch continues. | A ROLLBACK statement in a stored procedure referenced by an INSERT...EXEC statement causes the entire transaction to be rolled back and the batch stops executing. |
| Retrieving text or image columns from the inserted or deleted tables inside a trigger returns NULL values for text or image columns. | Retrieving text or image columns from the inserted or deleted tables inside a trigger is not allowed and causes an error. |
The compatibility setting also has an effect on reserved keywords. This table shows the words reserved at the specified level, but valid for use in object names at lower levels. At lower compatibility levels, the language features corresponding to the reserved keywords in upper levels are not available.
| Compatibility level setting | Reserved keywords |
|---|---|
| 80 | COLLATE, FUNCTION, OPENXML |
| 70 | BACKUP, CONTAINS, CONTAINSTABLE, DENY, FREETEXT, FREETEXTTABLE, PERCENT, RESTORE, ROWGUIDCOL, TOP |
| 65 | AUTHORIZATION, CASCADE, CROSS, DISTRIBUTED, ESCAPE, FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES, RESTRICT, RIGHT, SCHEMA, WORK |
The compatibility level setting cannot be changed in the master database, but it can be changed in the model database to take effect in all new databases. The compatibility level cannot be changed inside a stored procedure or in Transact-SQL strings executed with the EXEC('string') syntax. The compatibility level should not be changed inside a batch of Transact-SQL statements.
Permissions
Only the DBO, members of the sysadmin fixed server role, and the db_owner fixed database role (if the database whose compatibility level is to be changed is the current database) can execute this procedure.
Examples
This example creates a procedure named distributed, which is an SQL Server reserved keyword, by setting the compatibility level setting for the pubs database to 60.
CREATE PROCEDURE distributed
AS
PRINT 'This won't happen'
EXEC sp_dbcmptlevel 'pubs', 60
CREATE PROCEDURE distributed
AS
PRINT 'You are in a procedure that could not be defined'
PRINT 'in a version of SQL Server 6.5 or later'
PRINT 'without the compatibility setting.'
EXEC distributed
Here is the result set:
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'distributed'.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
You are in a procedure that could not be defined
in a version of SQL Server 6.5 or greater
without the compatibility setting.