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.

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 BusinessEntityID 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 AdventureWorks2012
SET compatibility_level = 90;
GO
USE AdventureWorks2012;
GO
DECLARE @v int;
SELECT @v = BusinessEntityID FROM HumanResources.Employee
UNION ALL
SELECT @v = BusinessEntityID 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 = BusinessEntityID FROM 
    (SELECT BusinessEntityID FROM HumanResources.Employee
     UNION ALL
     SELECT BusinessEntityID 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

Common language runtime (CLR) database objects are executed with version 4 of the CLR. However, some behavior changes introduced in version 4 of the CLR are avoided. For more information, see What's New in CLR Integration.

CLR database objects are executed with version 4 of the CLR.

The XQuery functions string-length and substring count each surrogate as two characters.

The XQuery functions string-length and substring count each surrogate as one character.

PIVOT is allowed in a recursive common table expression (CTE) query. However, the query returns incorrect results when there are multiple rows per grouping.

PIVOT is not allowed in a recursive common table expression (CTE) query. An error is returned.

The RC4 algorithm is only supported for backward compatibility. New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (Not recommended.) In SQL Server 2012, material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.

New material cannot be encrypted using RC4 or RC4_128. Use a newer algorithm such as one of the AES algorithms instead. In SQL Server 2012, material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.

The default style for CAST and CONVERT operations on time and datetime2 data types is 121 except when either type is used in a computed column expression. For computed columns, the default style is 0. This behavior impacts computed columns when they are created, used in queries involving auto-parameterization, or used in constraint definitions.

The following example shows the difference between styles 0 and 121. It does not demonstrate the behavior described above. For more information about date and time styles, see CAST and CONVERT (Transact-SQL).

CREATE TABLE t1 (c1 time(7), c2 datetime2); 

INSERT t1 (c1,c2) VALUES (GETDATE(), GETDATE());

SELECT CONVERT(nvarchar(16),c1,0) AS TimeStyle0
       ,CONVERT(nvarchar(16),c1,121)AS TimeStyle121
       ,CONVERT(nvarchar(32),c2,0) AS Datetime2Style0
       ,CONVERT(nvarchar(32),c2,121)AS Datetime2Style121
FROM t1;

-- Returns values such as the following.
TimeStyle0       TimeStyle121     Datetime2Style0      Datetime2Style121
---------------- ---------------- -------------------- --------------------------
3:15PM           15:15:35.8100000 Jun  7 2011  3:15PM  2011-06-07 15:15:35.8130000

Under compatibility level 110, the default style for CAST and CONVERT operations on time and datetime2 data types is always 121. If your query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.

Upgrading the database to compatibility level 110 will not change user data that has been stored to disk. You must manually correct this data as appropriate. For example, if you used SELECT INTO to create a table from a source that contained a computed column expression described above, the data (using style 0) would be stored rather than the computed column definition itself. You would need to manually update this data to match style 121.

Any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as datetime. Corresponding columns in local tables (in the same ordinal position in the select list) must be of type datetime.

Any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as smalldatetime. Corresponding columns in local tables (in the same ordinal position in the select list) must be of type smalldatetime.

After upgrading to 110, the distributed partitioned view will fail because of the data type mismatch. You can resolve this by changing the data type on the remote table to datetime or setting the compatibility level of the local database to 100 or lower.

SOUNDEX function implements the following rules.

1. If character_expression has any double letters, they are treated as one letter.

2. If a vowel (A, E, I, O, U) separates two consonants that have the same soundex code, the consonant to the right of the vowel is coded.

SOUNDEX function implements the following rules

1. If character_expression has any double letters, they are treated as one letter.

2. If character_expression has different letters side-by-side that have the same number in the soundex coding guide, they are treated as one letter.

3. If a vowel (A, E, I, O, U) separates two consonants that have the same soundex code, the consonant to the right of the vowel is coded.

4. If H or W separate two consonants that have the same soundex code, the consonant to the right of the vowel is not coded.

The additional rules may cause the values computed by the SOUNDEX function to be different than the values computed under earlier compatibility levels. After upgrading to compatibility level 110, you may need to rebuild the indexes, heaps, or CHECK constraints that use the SOUNDEX function. For more information, see SOUNDEX (Transact-SQL)

110

WITHIN GROUP, TRY_CONVERT, SEMANTICKEYPHRASETABLE, SEMANTICSIMILARITYDETAILSTABLE, SEMANTICSIMILARITYTABLE

100

CUBE, MERGE, ROLLUP

90

EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE