ALTER DATABASE Compatibility Level (Transact-SQL)

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

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. In this case, the order of results might be different than the results returned in SQL Server 2000. To achieve the same result order as SQL Server 2000, remove the column prefix in the ORDER BY clause.

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

SET XACT_ABORT OFF is allowed inside a trigger.

SET XACT_ABORT OFF is not allowed inside a trigger.

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

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

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

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

Empty SET statement (with no SET option assignments) is allowed.

Empty SET clause is not allowed.

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 or ARITHABORT is ON.

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 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

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

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

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

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

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

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

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

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

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

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

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 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

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

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 AdventureWorks2008R2
SET compatibility_level = 90;
GO
USE AdventureWorks2008R2;
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 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

100

CUBE, MERGE, ROLLUP

80

COLLATE, FUNCTION, OPENXML