ALTER DATABASE Compatibility Level (Transact-SQL)

Sets certain database behaviors to be compatible with the specified version of SQL Server. New in SQL Server 2008, the following ALTER DATABASE syntax replaces the sp_dbcmptlevel procedure for setting the database compatibility level. For other ALTER DATABASE options, see ALTER DATABASE (Transact-SQL).

Transact-SQL Syntax Conventions

Syntax

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

Arguments

• database_name
  Is the name of the database to be modified.

• COMPATIBILITY_LEVEL { 80 | 90 | 100 }
  Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:

  80 = SQL Server 2000 

  90 = SQL Server 2005 

  100 = SQL Server 2008 

Remarks

For all installations of SQL Server 2008, the default compatibility level is 100. Databases created in SQL Server 2008 are set to this level unless the model database has a lower compatibility level. When a database is upgraded to SQL Server 2008 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 80. Upgrading a database with a compatibility level below 80 sets the database to compatibility level 80. This applies to both system and user databases. Use ALTER DATABASE to change the compatibility level of the database. To view the current compatibility level of a database, query the compatibility_level column in the sys.databases catalog view.

Using Compatibility Level for Backward Compatibility

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2008, convert the application to work properly. Then use ALTER DATABASE to change the compatibility level to 100. The new compatibility setting for a database takes effect when the database is next made current (whether as the default database on login or on being specified in a USE statement).

Best Practices

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. For example, if the compatibility level changes while a query plan is being compiled, the compiled plan might be based on both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results. Furthermore, the problem may be compounded if the plan is placed in the plan cache and reused for subsequent queries. To avoid inaccurate query results, we recommend the following procedure to change the compatibility level of a database:

1. Set the database to single-user access mode by using ALTER DATABASE SET SINGLE_USER.

2. Change the compatibility level of the database.

3. Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.

4. For more information about setting the access mode of a database, see ALTER DATABASE (Transact-SQL).

SET Options

New functionality might work under older compatibility levels, but SET options might require adjustments. For example, using the xml data type under compatibility level 80 requires appropriate ANSI SET options. Also, when the database compatibility level is set to 90 or higher, setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON. If the database compatibility level is set to 80, the ARITHABORT option must explicitly be set to ON. For more information, see SET Options That Affect Results.

Compatibility Levels and Stored Procedures

When a stored procedure executes, it uses the current compatibility level of the database in which it is defined. When the compatibility setting of a database is changed, all of its stored procedures are automatically recompiled accordingly.

Differences Between Compatibility Level 80 and Level 90

This section describes new behaviors introduced with compatibility level 90.

At compatibility level 90, the following changes in behavior occur.

Compatibility-level setting of 80	Compatibility-level setting of 90	Possibility of impact
For locking hints in the FROM clause, the WITH keyword is always optional.	With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL).	High
The *= and =* operators for outer join are supported with a warning message.	These operators are not supported; the OUTER JOIN keyword should be used.	High
WHEN binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order. For example, an ORDER BY clause with a single two-part column (<table_alias>.<column>) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query. SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1 When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.	Errors are raised on column ambiguities. Column prefixes, if any, specified in ORDER BY are not ignored when binding to a column defined in the SELECT list. Consider the following query. SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1 When executed, the column prefix in the ORDER BY clause is not ignored. The sort operation occurs on the specified source column (x.c1) as expected. The execution plan for this query shows that the sort operator orders the rows returned from t_table and then the values for the derived column c1 defined in the SELECT list are computed.	Medium
In an INSERT SELECT from a UNION of different data types, each UNION branch is directly cast to the type of the target column of the INSERT. Even if the union used by itself could fail because of incompatible type conversions, the INSERT SELECT makes the UNION succeed because the branch to the result type of the UNION is never converted.	The result type of UNION is derived independently of INSERT SELECT. Each branch of UNION is cast to the result type of UNION, and then cast to the target column type of INSERT. If there are incompatible types in the UNION, the first cast might cause an error. To run in compatibility level 90, you must fix all incompatible type unions used inside INSERT SELECT.	Medium
Insert and update operations through a view are incorrectly supported on views that specify the WITH CHECK OPTION clause when the view or a referenced view uses the TOP clause.	Insert and update operations through a view are not supported on views that uses WITH CHECK OPTION when the view or a referenced view uses the TOP clause.	Medium
The UNION of a variable-length column and a fixed length column produces a fixed-length column.	The UNION of a variable-length column and a fixed length column produces a variable-length column.	Medium
SET XACT_ABORT OFF is ignored inside a trigger. The option is always set to ON.	SET XACT_ABORT can be set to OFF inside a trigger.	Medium
The FOR BROWSE clause is allowed (and ignored) in views.	The FOR BROWSE clause is not allowed in views.	Medium
Domain errors are not controlled by ANSI_WARNINGS. ARITHABORT settings are honored, if ANSI_WARNINGS are set to OFF and there is no change to ARITHABORT.	Domain errors are also controlled by ANSI_WARNINGS and are severity 16 errors. If either ANSI_WARNINGS or ARITHABORT are ON, an error is thrown instead of returning NULL value. User scripts that depend upon ARITHABORT being set to OFF might be broken by this change.	Medium
If a passthrough query against a remote data source [OPENROWSET or OPENQUERY] produces columns with duplicate names, the duplicate column names are ignored unless the columns are explicitly named in the query.	If a passthrough query against a remote data source [OPENROWSET or OPENQUERY] produces a column with duplicate column names, an error is raised.	Low
Character string constants and varbinary constants of size greater than 8000 are treated as text, ntext, or image.	Character string constants and varbinary constants of size greater than 8000 are treated as type varchar(max) (or nvarchar(max) and varbinary(max), respectively). This can change the data type of the table created using SELECT … INTO if the SELECT list contains such expressions.	Low
Comparisons between numeric types (smallint, tinyint, int, bigint, numeric, decimal, smallmoney, money) are done by converting the comparand with lower precedence in the type hierarchy to the type whose precedence is higher.	The numeric-type values are compared without conversions. This provides improved performance. However, this can cause some changes in behavior, especially in cases in which the conversion caused overflow exceptions.	Low
Built-in metadata functions that take string arguments truncate their input if the input is longer than 4000 characters.	Built-in metadata functions raise an error if the truncation would result in the loss of nonspace characters.	Low
The set of disallowed characters in an unquoted identifier remains unchanged.	The Transact-SQL parser supports the Unicode 3.2 standard, which changes the character classification for some international characters that are now disallowed in nondelimited identifiers.	Low
SET ANSI_WARNINGS ON does not override the setting of SET ARITHABORT OFF for the case of floating point domain errors [that is, negative arguments for the log() function]. If ANSI_WARNINGS is ON but ARITHABORT is OFF, floating point domain errors do not cause the query to be terminated.	SET ANSI_WARNINGS ON completely overrides ARITHABORT OFF setting. Floating point domain errors in this case will cause the query to be terminated.	Low
Non-integer constants are allowed (and ignored) in the ORDER BY clause.	Non-integer constants are not allowed in the ORDER BY clause.	Low
Empty SET statement (with no SET option assignments) is allowed.	Empty SET clause is not allowed.	Low
The IDENTITY attribute is not derived correctly for columns produced by a derived table.	The IDENTITY attribute is derived correctly for columns produced by derived tables.	Low
The nullability property of arithmetic operators over floating point data type is always nullable.	The nullability property of arithmetic operators over the floating point data type is changed to nonnullable for the case where the inputs are nonnullable and ANSI_WARNINGS is ON.	Low
In the INSERT .. SELECT statement with UNION, the types produced by the individual result sets are all converted to the destination result type.	In the INSERT .. SELECT statement with UNION, the dominant type of the various branches is determined, and the results are converted to that type before being converted to the destination table type.	Low
In the SELECT .. FOR XML statement, the hex(27) (the ' character) and hex(22) (the " character) are always entitized, even where not required.	FOR XML entitizes hex(27) and hex(22) only where required. They are not entitized in the following situations: In attribute content, hex(27) (the ' character) is not entitized if attribute values are delimited with ", and hex(22) (the " character) is not entitized if attribute values are delimited with '. In element content, hex(27) and hex(22) are never entitized.	Low
In FOR XML, the timestamp value is mapped to an integer.	In FOR XML, the timestamp value is mapped to an binary value. For more information, see FOR XML Support for the timestamp Data Type.	High (if a timestamp column is used); otherwise, Low
In FOR XML and OPENXML, high-range Unicode characters (3 bytes) in names are represented using 8 positions. For example, using 8 positions, FOR XML represents the Unicode code point U+10000 as: <a_x00010000_ c1="1" />	In FOR XML and OPENXML, high-range Unicode characters (3 bytes) in names are represented using 6 positions. For example, using 6 positions, FOR XML represents the Unicode code point U+10000 as: <a_x010000_ c1="1" />	Low
In FOR XML, derived table mappings in AUTO mode are treated transparently. For example: USE AdventureWorks CREATE TABLE Test(id int); INSERT INTO Test VALUES(1); INSERT INTO Test VALUES(2); SELECT * FROM (SELECT a.id AS a, b.id AS b FROM Test a JOIN Test b ON a.id=b.id) Test FOR XML AUTO; When the compatibility level for AdventureWorks is set to 80, the above example produces: <a a="1"><b b="1"/></a> <a a="2"><b b="2"/></a>	In FOR XML, derived table mappings in AUTO mode are treated opaquely. When the compatibility level for AdventureWorks is set to 90, the preceding example produces: <Test a="1" b="1"/> <Test a="2" b="2"/>	High (if FOR XML AUTO mode is applied on views); otherwise, Low
String to money conversions support using a backslash character (\) as a currency symbol only in the Japanese and Korean languages.	The backslash character (\) is accepted in all string to money conversions in all languages. ISNUMERIC would return true when \ is used as a currency symbol. For databases on versions of SQL Server earlier than SQL Server 2005, this new behavior breaks indexes and computed columns that depend on an ISNUMERIC return value that contains \ and for which the language is neither Japanese nor Korean.	Low
The result of an arithmetic operator is always nullable, even if the operands are non-nullable and ANSI_WARNINGS or ARITHABORT is set ON.	When ANSI_WARNINGS or ARITHABORT are set to ON, the result of a floating point arithmetic operator is nonnullable, if both operands are nonnullable. This change in nullability could cause failure when bcp is used to bulk export data that uses the binary format from a SQL Server 2000 table with a computed column that uses a floating point arithmetic operator and bcp or BULK INSERT is then used bulk import that data into a SQL Server 2005 table with the same definition. Note When both options are OFF, the Database Engine marks the result as nullable. This is the same as in SQL Server 2000.	Low
For built-in functions that take nvarchar as a parameter, if the supplied value is varchar, the value is converted to nvarchar(4000). In SQL Server 2000, if a larger value is passed, it is silently truncated.	For built-in functions that take nvarchar as a parameter, if the supplied value is varchar, the value is still converted to nvarchar(4000). However, if a larger value is passed, SQL Server 2008 generates an error. To run in compatibility level 90, you must fix any custom code that relies on the truncation behavior.	Low
A union of fixed-length a (char, binary, or nchar) string with a variable-length (varchar, varbinary, nvarchar) string returns a fixed-length result.	The union of a variable-size string and a fixed-size string returns a variable-size string. To run in compatibility level 90, you must fix all the places (indexes, queries, and computed columns) that depend on the type resulting from a union of a variable-size type and a fixed-size type.	Low
Object names containing the character 0xFFFF are valid identifiers.	Object names containing the character 0xFFFF are not valid identifiers and cannot be accessed. To run in compatibility level 90, you must rename objects that contain this character.	Low
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are significant. For example, the following SELECT ISNUMERIC('121212,12') query returns 0. This indicates that the string 121212,12 is not numeric.	In SELECT ISNUMERIC('<string>'), embedded commas within <string> are ignored. For example, the following SELECT ISNUMERIC('121212,12') query returns 1. This indicate that the string 121212,12 is numeric.	Low
A colon (:) following a reserved keyword in a Transact-SQL statement is ignored.	A colon (:) following a reserved keyword in a Transact-SQL statement causes the statement to fail.	Low
A GROUP BY clause in a subquery that references a column from the outer query succeeds.	A GROUP BY clause in a subquery that references a column from the outer query returns an error as per the SQL standard.	Low

Differences Between Lower Compatibility Levels and Level 100

This section describes new behaviors introduced with compatibility level 100.

Compatibility-level setting of 90 or lower	Compatibility-level setting of 100	Possibility of impact
The QUOTED_IDENTIFER setting is always set to ON for multistatement table-valued functions when they are created regardless of the session level setting.	The QUOTED IDENTIFIER session setting is honored when multistatement table-valued functions are created.	Medium
When you create or alter a partition function, datetime and smalldatetime literals in the function are evaluated assuming US_English as the language setting.	The current language setting is used to evaluate datetime and smalldatetime literals in the partition function.	Medium
The FOR BROWSE clause is allowed (and ignored) in INSERT and SELECT INTO statements.	The FOR BROWSE clause is not allowed in INSERT and SELECT INTO statements.	Medium
Full-text predicates are allowed in the OUTPUT clause.	Full-text predicates are not allowed in the OUTPUT clause.	Low
CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are not supported. The system stoplist is automatically associated with new full-text indexes.	CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported.	Low
MERGE is not enforced as a reserved keyword.	MERGE is a fully reserved keyword. The MERGE statement is supported under both 100 and 90 compatibility levels.	Low
Using the <dml_table_source> argument of the INSERT statement raises a syntax error.	You can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view. This is done using the <dml_table_source> argument of the INSERT statement.	Low
Unless NOINDEX is specified, DBCC CHECKDB or DBCC CHECKTABLE performs both physical and logical consistency checks on a single table or indexed view and on all its nonclustered and XML indexes. Spatial indexes are not supported.	Unless NOINDEX is specified, DBCC CHECKDB or DBCC CHECKTABLE performs both physical and logical consistency checks on a single table and on all its nonclustered indexes. However, on XML indexes, spatial indexes, and indexed views, only physical consistency checks are performed by default. If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on indexed views, XML indexes, and spatial indexes, where present. By default, physical consistency checks are performed before the logical consistency checks. If NOINDEX is also specified, only the logical checks are performed.	Low
When an OUTPUT clause is used with a data manipulation language (DML) statement and a run-time error occurs during statement execution, the entire transaction is terminated and rolled back.	When an OUTPUT clause is used with a data manipulation language (DML) statement and a run-time error occurs during statement execution, the behavior depends on the SET XACT_ABORT setting. If SET XACT_ABORT is OFF, a statement abort error generated by the DML statement using the OUTPUT clause will terminate the statement, but the execution of the batch continues and the transaction is not rolled back. If SET XACT_ABORT is ON, all run-time errors generated by the DML statement using the OUTPUT clause will terminate the batch, and the transaction is rolled back.	Low
CUBE and ROLLUP are not enforced as reserved keywords.	CUBE and ROLLUP are reserved keywords within the GROUP BY clause.	Low
Strict validation is applied to elements of the XML anyType type.	Lax validation is applied to elements of the anyType type. For more information, see Wildcard Components and Content Validation.	Low
The special attributes xsi:nil and xsi:type cannot be queried or modified by data manipulation language statements. This means that /e/@xsi:nil fails while /e/@* ignores the xsi:nil and xsi:type attributes. However, /e returns the xsi:nil and xsi:type attributes for consistency with SELECT xmlCol, even if xsi:nil = "false".	The special attributes xsi:nil and xsi:type are stored as regular attributes and can be queried and modified. For example, executing the query SELECT x.query('a/b/@*') returns all attributes including xsi:nil and xsi:type. To exclude these types in the query, replace @* with @*[namespace-uri(.) != "insert xsi namespace uri" and not (local-name(.) = "type" or local-name(.) ="nil".	Low
A user-defined function that converts an XML constant string value to a SQL Server datetime type is marked as deterministic.	A user-defined function that converts an XML constant string value to a SQL Server datetime type is marked as non-deterministic.	Low
The XML union and list types are not fully supported.	The union and list types are fully supported including the following functionality: Union of list Union of union List of atomic types List of union	Low
The SET options required for an xQuery method are not validated when the method is contained in a view or inline table-valued function.	The SET options required for an xQuery method are validated when the method is contained in a view or inline table-valued function. An error is raised if the SET options of the method are set incorrectly. For more information about the required option settings, see Setting Options (XML Data Type).	Low
XML attribute values that contain end-of-line characters (carriage return and line feed) are not normalized according to the XML standard. That is, both characters are returned instead of a single line-feed character.	XML attribute values that contain end-of-line characters (carriage return and line feed) are normalized according to the XML standard. That is, all line breaks in external parsed entities (including the document entity) are normalized on input by translating both the two-character sequence #xD #xA and any #xD that is not followed by #xA to a single #xA character. Applications that use attributes to transport string values that contain end-of-line characters will not receive these characters back as they are submitted. To avoid the normalization process, use the XML numeric character entities to encode all end-of-line characters.	Low
The column properties ROWGUIDCOL and IDENTITY can be incorrectly named as a constraint. For example the statement CREATE TABLE T (C1 int CONSTRAINT MyConstraint IDENTITY) executes, but the constraint name is not preserved and is not accessible to the user.	The column properties ROWGUIDCOL and IDENTITY cannot be named as a constraint. Error 156 is returned.	Low
Updating columns by using a two-way assignment such as UPDATE T1 SET @v = column_name = <expression> can produce unexpected results because the live value of the variable can be used in other clauses such as the WHERE and ON clause during statement execution instead of the statement starting value. This can cause the meanings of the predicates to change unpredictably on a per-row basis. This behavior is applicable only when the compatibility level is set to 90.	Updating columns by using a two-way assignment produces expected results because only the statement starting value of the column is accessed during statement execution.	Low
Variable assignment is allowed in a statement containing a top-level UNION operator, but returns unexpected results. For example, in the following statements, local variable @v is assigned the value of the column EmployeeID from the union of two tables. By definition, when the SELECT statement returns more than one value, the variable is assigned the last value that is returned. In this case, the variable is correctly assigned the last value, however, the result set of the SELECT UNION statement is also returned. ALTER DATABASE AdventureWorks SET compatibility_level = 90; GO USE AdventureWorks; GO DECLARE @v int; SELECT @v = EmployeeID FROM HumanResources.Employee UNION ALL SELECT @v = EmployeeID FROM HumanResources.EmployeeAddress; SELECT @v;	Variable assignment is not allowed in a statement containing a top-level UNION operator. Error 10734 is returned. To resolve the error, rewrite the query as shown in the following example. DECLARE @v int; SELECT @v = EmployeeID FROM (SELECT EmployeeID FROM HumanResources.Employee UNION ALL SELECT EmployeeID FROM HumanResources.EmployeeAddress) AS Test SELECT @v;	Low
The ODBC function {fn CONVERT()} uses the default date format of the language. For some languages, the default format is YDM, which can result in conversion errors when CONVERT() is combined with other functions, such as {fn CURDATE()}, that expect a YMD format.	The ODBC function {fn CONVERT()} uses style 121 (a language-independent YMD format) when converting to the ODBC data types SQL_TIMESTAMP, SQL_DATE, SQL_TIME, SQLDATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP.	Low
The ODBC function {fn CURDATE()} returns only the date in the format 'YYYY-MM-DD'.	The ODBC function {fn CURDATE()} returns both date and time, for example 'YYYY-MM-DD hh:mm:ss.	Low
Datetime intrinsics such as DATEPART do not require string input values to be valid datetime literals. For example, SELECT DATEPART (year, '2007/05-30') compiles successfully.	Datetime intrinsics such as DATEPART require string input values to be valid datetime literals. Error 241 is returned when an invalid datetime literal is used.	Low

Reserved Keywords

The compatibility setting also determines the keywords that are reserved by the Database Engine. The following table shows the reserved keywords that are introduced by each of the compatibility levels.

Compatibility-level setting	Reserved keywords
100	CUBE, MERGE, ROLLUP
90	EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
80	COLLATE, FUNCTION, OPENXML

At a given compatibility level, the reserved keywords include all of the keywords introduced at or below that level. Thus, for instance, for applications at level 100, all of the keywords listed in the preceding table are reserved. At the lower compatibility levels, level-100 keywords remain valid object names, but the level-100 language features corresponding to those keywords are unavailable.

Once introduced, a keyword remains reserved. For example, the reserved keyword OPENXML, which was introduced in compatibility level 80, is also reserved in levels 90 and 100.

If an application uses an identifier that is reserved as a keyword for its compatibility level, the application will fail. To work around this, enclose the identifier between either brackets ([]) or quotation marks (""); for example, to upgrade an application that uses the identifier EXTERNAL to compatibility level 90, you could change the identifier to either [EXTERNAL] or "EXTERNAL".

For more information, see Reserved Keywords (Transact-SQL).

Permissions

Requires ALTER permission on the database.

Examples

A. Changing the compatibility level

The following example changes the compatibility level of the AdventureWorks database to 90,SQL Server 2005.

ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 90;
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 100 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 and higher, the default level, the following SELECT... ORDER BY statement produces an error because the column alias 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.

ALTER DATABASE tempdb
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT c1, c2 AS c1
FROM SampleTable
ORDER BY c1;
GO

The following SELECT... ORDER BY statement works in both compatibility levels because an unambiguous alias is specified in the AS clause.

ALTER DATABASE tempdb
SET COMPATIBILITY_LEVEL = 100;
GO
SELECT c1, c2 AS c3
FROM SampleTable
ORDER BY c1;
GO

ALTER DATABASE tempdb
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT c1, c2 AS c3
FROM SampleTable
ORDER BY c1;
GO

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

At compatibility level 90 and higher, the default level, the following SELECT...ORDER BY statement produces an error because the column alias specified in the ORDER BY clause contains a table prefix.

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.

ALTER DATABASE tempdb
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT c1 AS x
FROM SampleTable
ORDER BY SampleTable.x;
GO

The following SELECT...ORDER BY statement works in both compatibility levels because the table prefix is removed from the column alias specified in the ORDER BY clause.

ALTER DATABASE tempdb
SET COMPATIBILITY_LEVEL = 100;
GO
SELECT c1 AS x
FROM SampleTable
ORDER BY x;
GO
ALTER DATABASE tempdb
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT c1 AS x
FROM SampleTable
ORDER BY x;
GO

See Also

Reference

ALTER DATABASE (Transact-SQL)

Reserved Keywords (Transact-SQL)

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

sys.databases (Transact-SQL)

sys.database_files (Transact-SQL)

Concepts

Database Compatibility Level Option

Change History

Updated content
Corrected the behavior of the XACT_ABORT statement when specified inside a trigger. Setting XACT_ABORT to OFF in a trigger is ignored in 80 compatibility level and is allowed in 90 and higher compatibility level. Previous documentation reversed this information.