[post] compatibility level of SQL Server

Time:2020-11-22

Original post address:https://www.cnblogs.com/jinanxiaolaohu/p/10030021.html

Alter database (transact SQL) compatibility level

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017

Applicable objects:yesSQL Server (from 2008)yesAzure SQL databasenoAzure SQL data warehousenoParallel data warehouse

Set some database behaviors to match the specifiedSQL server version is compatible.  For other alter database options, seeALTER DATABASE (Transact-SQL)

For more information about syntax conventions, seeTransact SQL syntax conventions

grammar

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

parameter

database_name
The name of the database to modify.

COMPATIBILITY_LEVEL { 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }
To make the database compatible withSQL server version.  The following compatibility level values can be configured (not all versions support all of the Compatibility levels listed above):

Product Database engine version Compatibility level specification Supported compatibility level values
SQL Server 2019 Preview 15 150 150、140、130、120、110、100
SQL Server 2017 (14.x) 14 140 140、130、120、110、100
Azure SQL Database Logical server 12 130 150、140、130、120、110、100
Azure SQL Database Managed instance 12 130 150、140、130、120、110、100
SQL Server 2016 (13.x) 13 130 130、120、110、100
SQL Server 2014 (12.x) 12 120 120、110、100
SQL Server 2012 (11.x) 11 110 110、100、90
SQL Server 2008 R2 10.5 100 100、90、80
SQL Server 2008 10 100 100、90、80
SQL Server 2005 9 90 90、80
SQL Server 2000 8 80 80

remarks

Starting from January 2018, atIn azure SQL database, the default compatibility level of the newly created database is 140.  We will not update the database compatibility level for existing databases.  It’s up to the customer to decide.  However, it is strongly recommended that customers plan to move to the latest compatibility level to take advantage of the latest improvements.

If you want to use database compatibility level 140 for the entire database, there is a reason to preferFor the cardinality estimation model of SQL Server 2012 (11. X), seeALTER DATABASE SCOPED CONFIGURATION (Transact-SQL), especially its keywordsLEGACY_CARDINALITY_ESTIMATION = ON

How to evaluateFor details on the performance differences of the most important queries between the two Compatibility levels on azure SQL database, seeImproved query performance with compatibility level 130 in azure SQL database。 Note that this article refers to compatibility levels 130 andSQL server, but the same method applies to theSQL server andAzure SQL Database。

Perform the following query to determine theThe version of the database engine.

SQL
SELECT SERVERPROPERTY('ProductVersion');

remarks

All features are not supported on azure SQL database (depending on the compatibility level).

To determine the current compatibility level, querysys.databases (Transact-SQL)Compatibility_ Level column.

SQL
SELECT name, compatibility_level FROM sys.databases;

Remarks

For allSQL server installation, the default compatibility level is set toThe version of the database engine.  Unless the database has a lower compatibility level, the database is set to this level.  In the database fromWhen upgrading any previous version of SQL server, if the database is at leastThe lowest level allowed by the SQL server instance, it retains the existing compatibility level.  Upgrading a database with a compatibility level lower than the allowable level automatically sets the database to the minimum allowable compatibility level.  This applies to both the system database and the user database.

When attaching or restoring a database and after in place upgrade,SQL Server 2017 (14. X) should have the following behavior:

  • If the compatibility level of the user database is 100 or higher before the upgrade, the corresponding level will be maintained after the upgrade.
  • If the compatibility level of the user database before the upgrade is 90, then in the upgraded database, the compatibility level will be set to 100, which isMinimum compatibility level supported by SQL Server 2017 (14. X).
  • After the upgrade, the compatibility level of tempdb, model, MSDB and resource databases will be set to the current compatibility level.
  • The master system database retains its compatibility level prior to the upgrade.

UseALTER DATABASEChange the compatibility level of the database.  When issuedUSE <database>Command or use the database as the default database context to process new logins, the new compatibility level settings for the database take effect.
To view the current compatibility level of the database, querysys.databasesCompatibility in catalog view_ Level column.

remarks

In earlier versionsCreated in SQL server and upgraded toSQL Server 2016 (13. X) RTM or service pack 1Distribution databaseCompatibility level 90 is adopted, which is not supported by other databases.  This does not affect the replication function.  Upgrade to a later version of the service pack andThe SQL server version will increase the compatibility level of the distribution database to match the master database.

Compatibility level and SQL server upgrade

Database compatibility level is an important tool that can be upgraded by allowingThe SQL Server Database Engine helps to modernize the database by maintaining the same pre upgrade database compatibility level and maintaining the functional state of the connected application.  As long as the application does not need to take advantage of the enhancements available only at higher database Compatibility levels, it is an upgradeSQL Server database engine and an effective way to maintain the database compatibility level before.  For more information about using Compatibility levels for backward compatibility, seeUsing compatibility level to get backward compatibility

For new development work, or when existing applications need to use new features and performance improvements completed in the query optimizer space, plan to upgrade the database compatibility level toThe latest level available in SQL server and verify that the application can be used with this compatibility level.  For more details about upgrading the database compatibility level, seeBest practices for upgrading database Compatibility levels

Tips

If theTo test and validate an application on the SQL Server Version, theImplicit testing and validation at the SQL Server Version native database compatibility level.

Therefore, theSQL server version of the database compatibility level, the database compatibility level provides an easy way to authenticate existing applications.

For more information about the differences between Compatibility levels, see the appropriate section later.

If you want toSQL Server database engine is upgraded to the latest version, while maintaining the database compatibility level at the level before the upgrade and maintaining its supportability state, it is recommended to use in the databaseMicrosoft Data Migration AssistantThe tool (DMA) performs static function peripheral validation on application code.  There are no errors in the DMA tool output about missing or incompatible features, which protects the application from any functional regression on the new target version.  For more information about DMA tools, seehere

remarks

DMA supports database compatibility level 100 and higher.  ExcludeSQL Server 2005 as the source version.

important

Microsoft recommends performing some minimal tests to verify that the update was successful while maintaining the previous database compatibility level.  You should determine the minimum tests that apply to your application and scenario.

remarks

Microsoft provides query plan shape protection when:

  • New editionSQL Server (target) before equivalentSQL Server Version (source) runs on the hardware.
  • ObjectivesSQL server and sourceSQL server uses the sameSupported database Compatibility levels

Any query plan shape regression (with sourceSQL Server) will be solved.  If this happens, contact Microsoft customer support services.

Using compatibility level to get backward compatibility

The database compatibility level setting only affects the behavior of the specified database, not the behavior of the entire server.  The database compatibility level only implements thePrevious versions of SQL Server maintain partial backward compatibility.

Tips

Because the database compatibility level is a database level setting, theApplications running on SQL Server database engine but using older database Compatibility levels can still take advantage of server level enhancements without any changes to the application.

This includes rich monitoring and troubleshooting improvements, and provides newSystem dynamic management viewandExtended events。 In addition, scalability is improved, for example, by providingAutomatic soft NUMA

Starting with compatibility mode 130, any new query plan that affects functionality is intentionally added only to the new compatibility level.  This is done to minimize risk during the upgrade process due to performance degradation due to query plan changes.
From an application perspective, the goal should still be upgraded to the latest compatibility level at some point in time to inherit some of the new features, as well as the performance improvements achieved in the query optimizer space, but in a controlled way.  By using lower Compatibility levels as a more secure migration aid, version differences between behaviors controlled by the relevant compatibility level settings can be resolved.  For more details, including recommended workflows to upgrade the database compatibility level, seeBest practices for upgrading database Compatibility levels

important

GivenThe repeal feature introduced in the SQL server version is not protected by the compatibility level.  This means fromRemoved features from the SQL Server database engine.

For example,FASTFIRSTROWPrompt inSQL Server 2012 (11. X) and replaced withOPTION (FAST n )Tips.  Setting the database compatibility level to 110 does not restore the defunct prompt.  For more information about the defunct feature, seeDatabase engine functions abolished in SQL Server 2016Database engine function abolished in SQL Server 2014Database engine function abolished in SQL Server 2012AndThe database engine function abolished in SQL Server 2008

important

GivenSignificant changes introduced in the SQL server version may not be protected by the compatibility level.  This meansBehavior changes between SQL Server database engine versions.  Transact-SQL Behavior is usually protected by a compatibility level.  However, system objects that have been changed or deleted are not protected by the compatibility level.

An example of a significant change protected by the compatibility level is the implicit conversion from datetime to datetime2 data types.  Below the database compatibility level of 130, these conversions are more accurate by considering the fraction of milliseconds that results in different conversion values.  To restore the previous conversion behavior, set the database compatibility level to 120 or lower.

Examples of major changes that are not protected by the compatibility level are:

  • Column name changed in system object.  InIn SQL Server 2012 (11. X), sys.dm_ Os_ sys_ Column single in info_ pages_ KB has been renamed to pages_ kb。  Regardless of the compatibility level, querySELECT single_pages_kb FROM sys.dm_os_sys_infoWill generate error 207 (invalid column name).
  • System object deleted.  InIn SQL Server 2012 (11. X),sp_dboptionDeleted.  Regardless of the compatibility level, theEXEC sp_dboption 'AdventureWorks2016CTP3', 'autoshrink', 'FALSE';Error 2812 (stored procedure “SP” not found_ dboption”)。

For more information about major changes, seeSignificant changes in database engine functionality in SQL Server 2017Significant changes in database engine functionality in SQL Server 2016Major changes of database engine function in SQL Server 2014Major changes of database engine function in SQL Server 2012AndMajor changes of database engine function in SQL Server 2008

Best practices for upgrading database Compatibility levels

For recommended workflows for upgrading Compatibility levels, seeChange database compatibility mode and use query storage

Compatibility levels and stored procedures

When a stored procedure is executed, it uses the current compatibility level of the database that defines it.  When you change the compatibility settings of a database, all stored procedures for that database are automatically rewritten.

Differences between Compatibility levels 140 and 150

This section describes the new behavior introduced with compatibility level 150.

ForAzure SQL database andSQL Server 2019 preview, database compatibility level 150 is currently a personal preview version.  In addition to the improvements introduced in database compatibility level 140, this database compatibility level will be associated with next generation query processing improvements.

For more information about query processing enabled in database compatibility level 150, seeWhat’s new in SQL Server 2019

Differences between compatibility level 130 and compatibility level 140

This section describes the new behavior introduced with compatibility level 140.

Compatibility level set to 130 or lower The compatibility level is set to 140
Fixed row guessing is used to estimate the cardinality of statements that refer to multi statement table valued functions. The cardinality estimate of a qualified statement that references a multi statement table valued function uses the actual cardinality of the function output.  This is achieved by interleaved execution of multi statement table valued functions.
Requests can cause insufficient memory to overflow to disk. Batch mode queries with grant size may continue to cause problems with continuous execution. Requests can cause insufficient memory to overflow to disk. Batch mode queries of grant size may improve the performance of continuous execution.  This is achieved by updating the batch mode memory grant feedback of the cache plan memory grant size when an overflow occurs to the batch mode operator.
Requests for excessive memory grant size batch mode queries that cause concurrency problems may continue to cause problems with continuous execution. Requests for excessive memory grant size batch mode queries that cause concurrency problems may improve concurrency for continuous execution.  This is achieved through batch mode memory grant feedback that updates the cache plan memory grant size when an excessive amount is initially requested.
Batch mode queries containing join operators qualify for three physical join algorithms, including nested loops, hash joins, and merge joins.If the cardinality estimation is not correct for the join input, an inappropriate join algorithm may be selected.  If this happens, performance will degrade and the improper join algorithm will remain in use until the cache plan is recompiled. There is another join operator called adaptive join.  If the cardinality estimation is not input correctly for the externally generated join, an inappropriate join algorithm may be selected.  If this happens and the statement is eligible for adaptive joins, nested loops are used for smaller join inputs and hash joins are dynamically used for larger join inputs without recompiling.
A normal plan that references a column store index is not eligible for batch mode execution. Normal plans that reference column store indexes are discarded to support conditional batch mode execution.
sp_execute_external_scriptUDX operators can only run in row mode. sp_execute_external_scriptUDX operators are eligible for batch mode execution.
Multi statement table valued functions (tvf) are not interleaved Multi statement tvf interleaved execution for improving plan quality.

Patches under trace flag 4199 in previous versions of SQL server prior to SQL Server 2017 are now enabled by default.  With compatibility mode 140.  Trace flag 4199 will still apply to new query optimizer patches released after SQL Server 2017.  For information about trace flag 4199, seeTracking flag 4199

Differences between compatibility level 120 and compatibility level 130

This section describes the new behavior introduced with compatibility level 130.

Compatibility level set to 120 or lower The compatibility level is set to 130
The insert in an insert-select statement is a single thread. The insert in an insert-select statement is multithreaded or can have a parallel plan.
Memory optimization table query execution single thread. Queries for memory optimization tables can now have parallel plans.
Introduced SQL 2014 cardinality estimation model version = 120 Cardinality estimation model 130 brings further cardinality estimation (CE) improvements (visible in query plans).  CardinalityEstimationModelVersion=”130″
Batch mode and row mode changes for column store indexes:

  • The sort on a table with a column store index is in row mode
  • Windowing functions are aggregated in row mode (e.gLAGOrLEAD)Run under
  • Queries for column storage tables with multiple different clauses run in row mode
  • Queries that run under maxtop 1 or have a serial plan are executed in row mode
Batch mode and row mode changes for column store indexes:

  • Sorting on a table with a column store index is now in batch mode
  • Windowed aggregation is now in batch mode (such asLAGOrLEAD)Run under
  • Queries with column storage tables with multiple different clauses run in batch mode
  • Queries that run under maxtop 1 or have a serial schedule are executed in batch mode
Statistics can be updated automatically. The logic for automatically updating statistics is more proactive for large tables.  In practice, this should reduce the situation where customers experience performance issues for queries that frequently query newly inserted rows but do not update statistics to include these values.
InIn SQL Server 2014 (12. X), trace 2371 is turned off by default. InIn SQL Server 2016 (13. X),Trace 2371Opens by default.  Trace flag 2371 tells the automatic statistics updater to sample a smaller but more intelligent subset of rows in a table with many rows.  

An important improvement is to include more recently inserted rows in the sampling.  

Another improvement is to make the query run while the update statistics process is running without blocking the query.

For level 120, statistics are sampled through a single threaded process. For level 130, statistics are sampled through a multithreaded process.
253 passing in a foreign key is a restriction. A given table can be referenced by up to 10000 incoming foreign keys or similar references.  For restrictions, seeCreate Foreign Key Relationships
Disallowed MD2, MD4, MD5, Sha, and SHA1 hash algorithms are allowed. Only Sha2 is allowed_ 256 and Sha2_ 512 hash algorithm.
  SQL Server 2016 (13.x) It includes improvements to some data type conversions and some unusual operations.  For more information, seeSQL Server 2016 improvements in handling some data types and common operations
STRING_ The split function is not available. STRING_ The split function is available at compatibility level 130 or higher.If the database compatibility level is lower than 130SQL server will not be able to find and execute string_ Split function.

Early days before SQL Server 2016 (13. X)Patches in the SQL Server Version under trace flag 4199 are now enabled by default.  Has compatibility mode 130.  The trace flag 4199 will still apply to theNew query optimizer patches released after SQL Server 2016 (13. X).  If you want toOlder query optimizer is used in SQL database, compatibility level 110 must be selected.For information about trace flag 4199, seeTracking flag 4199

Differences between lower Compatibility levels and level 120

This section describes the new behavior introduced with compatibility level 120.

Compatibility level set to 110 or lower The compatibility level is set to 120
Use the old version of the query optimizer. SQL Server 2014 (12.x) Includes significant improvements to the components that create and optimize query plans.  This new query optimizer feature relies on using database compatibility level 120.  To take advantage of these improvements, new database applications should be developed using database compatibility level 120.  Response from earlier versions ofThe migrated applications in SQL server are carefully tested to confirm that good performance is maintained or improved.  If performance declines, you can set the database compatibility level to 110 or lower to use an earlier query optimizer method.

Database compatibility level 120 uses a new cardinality estimator optimized for modern data warehouses and OLTP workloads.  Before setting the database compatibility level to 110 due to performance issues, seeSQL Server 2014 (12.x) New features in database engineRecommendations in the query plan section of the topic.

If the compatibility level is less than 120, the language setting is ignored when the date value is converted to a string value.  Note that this behavior is specific to the date type only.  See example B in the examples section below. When you convert the date value to a string value, the language setting is not ignored.
EXCEPTThe recursive reference to the right of the clause produces an infinite loop.  Example C in the examples section below demonstrates this behavior. EXCEPTA recursive reference in Clause causes an ANSI SQL compliant error.
Recursive common table expressions (CTEs) allow duplicate column names. Recursive CTE does not allow duplicate column names.
If you change triggers, disabled triggers are enabled. Changing a trigger does not change the state of the trigger (enabled or disabled).
The into output clause is ignoredIDENTITY_INSERT SETTING = OFF, and allows the insertion of explicit values. WillIDENTITY_INSERTWhen set to off, explicit values cannot be inserted for identity columns in a table.
After setting database inclusion to partial include, verifyMERGEStatementOUTPUTIn Clause$actionFields may return collation errors. MERGEStatement$actionClause returns a value whose collation is a database collation, not a server collation, so collation conflict errors are not returned.
SELECT INTOStatement always creates a single threaded insert operation. SELECT INTOStatement to create a parallel insert operation.  When a large number of rows are inserted, parallel operations can improve performance.

Differences between lower Compatibility levels and levels 110 and 120

This section describes the new behavior introduced with compatibility level 110.  This section also applies to level 120.

Compatibility level set to 100 or lower Set at least 110 compatibility level
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, seeNew features 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.
Allowed in recursive common table expression (CTE) queriesPIVOT。 However, when there are multiple rows per group, the query returns incorrect results. Not allowed in recursive common table expression (CTE) queriesPIVOT。 An error will be returned.
The RC4 algorithm is only used to support backward compatibility.  RC4 or RC4 can only be used when the database compatibility level is 90 or 100_ 128 encrypt the new material.  (not recommended.) InIn SQL Server 2012 (11. X), RC4 or RC4 can be used with any compatibility level pair_ 128 encrypted materials are decrypted. RC4 cannot be used_ 128 encrypt new materials.  Instead, it uses a newer algorithm, such as one of the AES algorithms.  InIn SQL Server 2012 (11. X), RC4 or RC4 can be used with any compatibility level pair_ 128 encrypted materials are decrypted.
For the time and datetime2 data typesCASTAndCONVERTThe default style for operations is 121, except when these types are used in calculated tabular expressions.  For calculated columns, the default style is 0.  This behavior affects calculated columns when you create them in queries or constraint definitions that involve automatic parameterization.

Example D in the examples section below shows the difference between styles 0 and 121.  It does not demonstrate the behavior described above.  For more information about date and time styles, seeCast and convert (transact SQL)

When the compatibility level is 110, for the time and datetime2 data typesCASTAndCONVERTThe default style for operations is always 121.  If your query depends on old behavior, use a compatibility level below 110 or explicitly specify the 0 style in the affected query.

Upgrading the database to compatibility level 110 does not change user data that has been stored on disk.  You must correct this data manually accordingly.  For example, if theSELECT INTOTo create a table from the source that contains the calculated list expression above, the data (using style 0) will be stored instead of the calculated column definition itself.  You need to manually update this data to match style 121.

All columns of type ‘smalldatetime’ of the remote table referenced in the partitioned view are mapped to datetime.The corresponding column in the local table (in the same ordinal position in the selection list) must be of type ‘datetime’. All columns of type smalldatetime of the remote table referenced in the partitioned view are mapped to smalldatetime.  The corresponding column in the local table (in the same ordinal position in the selection list) must be of type ‘smalldatetime’.

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

SOUNDEXFunction implements the following rules:

1) When separating two with the sameSOUNDEXWhen the consonant of the code, the capital h or capital W is ignored.

2) Ifcharacter_expressionThe first 2 characters of have the sameSOUNDEXCode, the two characters will be included.  If a group of parallel consonants have the sameSOUNDEXCode, they will not be included, except for the first consonant.

SOUNDEXFunction implements the following rules:

1) If the uppercase h or uppercase w separators have the sameSOUNDEXIf there are two consonants in the code, the consonant on the right is ignored

2) If a group of parallel consonants have the sameSOUNDEXCode, they will not be included, except for the first consonant.

Other rules may result inSOUNDEXThe value calculated by the function is different from the value calculated at a lower database compatibility level.  After upgrading to compatibility level 110, you may need to regenerate theSOUNDEXThe index, heap, or check constraint for the function.  For more information, seeSOUNDEX (Transact-SQL)

Differences between compatibility level 90 and compatibility level 100

This section describes the new behavior introduced with compatibility level 100.

Compatibility level set to 90 The compatibility level is set to 100 Possibility of impact
For multi statement table valued functions, when they are created, regardless of the session level setting, quoted_ Identifier is always set to on. When creating multi statement table valued functions, the quoted identifier session settings are followed. Medium
When you create or change a partition function, the datetime and smalldatetime literals in the function are evaluated and the language is assumed to be us_ English。 Use the current language settings to evaluate the datetime and smalldatetime literals in the partition function. Medium
Allowed inINSERTAndSELECT INTOStatement (and ignored)FOR BROWSEClause. Not allowed inINSERTAndSELECT INTOStatementFOR BROWSEClause. Medium
OUTPUTFull text predicates are allowed in clause. OUTPUTPredicates are not allowed in full-text. Low
CREATE FULLTEXT STOPLISTALTER FULLTEXT STOPLISTAndDROP FULLTEXT STOPLISTNot supported.  The system stoplist is automatically associated with a new full-text search. CREATE FULLTEXT STOPLISTALTER FULLTEXT STOPLISTAndDROP FULLTEXT STOPLISTSupported. Low
MERGENot mandatory as a reserved keyword. Merge is a fully reserved keyword.  It is supported at both 100 and 90 Compatibility levelsMERGEStatement. Low
< DML using insert statement_ table_ The source > parameter raises a syntax error. You can capture the results of the output clause in a nested insert, update, delete, or merge statement, and then insert the results into the target table or view.  This is achieved by using the < DML of the insert statement_ table_ Source > parameter. Low
Unless specifiedNOINDEXOtherwiseDBCC CHECKDBOrDBCC CHECKTABLEBoth physical and logical consistency checks are performed on a single table or indexed view and all of its nonclustered and XML indexes.  Spatial index is not supported. Unless specifiedNOINDEXOtherwiseDBCC CHECKDBOrDBCC CHECKTABLEBoth physical and logical consistency checks are performed on a single table and all nonclustered indexes.  However, by default, only XML indexes, spatial indexes, and indexed views are checked for physical consistency.

If specifiedWITH EXTENDED_LOGICAL_CHECKS, a logical check is performed on the indexed view, XML index, and spatial index (if any).  By default, the physical consistency check is performed first, followed by the logical consistency check.  If also specifiedNOINDEX, only the logical check is performed.

Low
If the output clause is used with a data manipulation language (DML) statement and a runtime error occurs during the execution of the statement, the entire transaction is terminated and rolled back. IfOUTPUTClause is used with a data manipulation language (DML) statement, and a runtime error occurs during the execution of the statement, the behavior depends onSET XACT_ABORTSettings.  IfSET XACT_ABORTSet to off, theOUTPUTThe statement abort error generated by the DML statement of clause will terminate the statement, but the execution of the batch will continue and the transaction will not be rolled back.  IfSET XACT_ABORTSet to on, all runtime errors generated by DML statements that use the output clause terminate the batch and roll back the transaction. Low
Cube and rollup are not enforced as reserved keywords. CUBEAndROLLUPIs a reserved keyword in the group by clause. Low
Apply strict validation to elements of type XML anytype. Apply loose validation to elements of type anytype.  For more information, seeWildcard component and content validation Low
Data manipulation language statements cannot query or modify special properties xsi:nil And xsi:type 。

This means that/e/@xsi:nilFailure, at the same time/e/@*Ignore xsi:nil And xsi:type Property.  But,/eReturn xsi:nil And xsi:type Property to keep theSELECT xmlColEven ifxsi:nil = "false"So it is.

Special properties xsi:nil And xsi:type It is stored as a general property and cannot be queried and modified.

For example, execute a querySELECT x.query('a/b/@*')All attributes including XSI: nil and XSI: type are returned.  To exclude these types from the query, use the@*[namespace-uri(.) != "insert xsi namespace uri"Replacement@*Instead of using(local-name(.) = "type"Orlocal-name(.) ="nil".To replace

Low
Used to convert XML constant string values toUser defined functions of type SQL Server datetime are marked as determined. Used to convert XML constant string values toUser defined functions of type SQL Server datetime are marked as indeterminate. Low
XML Union and list types are not fully supported. Fully supports Union and list types, including the following functions:

Union of lists

Union of Union

List of atomic types

List of unions

Low
When an XQuery method is included in a view or inline table valued function, the set option required by the method is not validated. When an XQuery method is included in a view or inline table valued function, the set options required by the method are verified.  If the set option of this method is not set correctly, an error will be raised. 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, carriage return and line feed characters are returned instead of single line breaks. XML attribute values containing end of line characters (carriage return and line feed) are normalized according to the XML standard.  In other words, all line breaks in the external analyzed entities (including document entities) are normalized at input time by converting the two character sequence ා XD ා Xa and all ා XD ා XA that are not followed by ා XA into a single ා XA character.

Applications that use properties to transmit string values that contain end of line characters will receive different characters than at the time of submission.  To avoid the normalization process, encode all end of line characters using XML numeric character entities.

Low
ROWGUIDCOLAndIDENTITYColumn properties may be incorrectly named constraints.For example,CREATE TABLE T (C1 int CONSTRAINT MyConstraint IDENTITY)The statement can be executed, but the constraint name is not preserved and cannot be accessed by the user. ROWGUIDCOLAndIDENTITYColumn properties cannot be named constraints.Error 156 returned. Low
Use bidirectional assignment (e.gUPDATE T1 SET @v = column_name = <expression>)To update columns can have unexpected consequences because during statement execution, you canWHERAndONClause) instead of the statement start value.  This leads to unpredictable row by line changes in the meaning of the predicate.

This behavior applies only when the compatibility level is set to 90.

Updating a column with a bidirectional assignment produces the expected result, because only the statement start value of the column is accessed during statement execution. Low
See example E in the examples section below. See example f in the examples section below. Low
The ODBC function {FN convert()} uses the language’s default date format.  For some languages, the default format is YDM, which results in converting() to other functions (such as{fn CURDATE()})Conversion error in combination. When converting to ODBC data type SQL_ TIMESTAMP、SQL_ DATE、SQL_ TIME、SQLDATE、SQL_ TYPE_ Time and SQL_ TYPE_ When timestamp, ODBC function{fn CONVERT()}Use style 121, a language independent YMD format. Low
Date time intrinsic functions, such as datepart, can be valid date time literals without string input.  For example,SELECT DATEPART (year, '2007/05-30')Will compile successfully. Date time intrinsic functions (such asDATEPART)A string input value is required to be a valid date time literal.  Error 241 is returned when using invalid date time text. Low

Reserved keywords

Compatibility settings are also determinedKeywords reserved by the database engine.  The following table shows the reserved keywords introduced for each compatibility level.

Compatibility level settings Reserved keywords
130 To be determined.
120 None.
110 WITHIN GROUP、TRY_CONVERT、SEMANTICKEYPHRASETABLE、SEMANTICSIMILARITYDETAILSTABLE、SEMANTICSIMILARITYTABLE
100 CUBE、MERGE、ROLLUP
90 EXTERNAL、PIVOT、UNPIVOT、REVERT、TABLESAMPLE

At a given compatibility level, reserved keywords include all keywords introduced at that level or lower.  For example, for an application with compatibility level 110, all keywords listed in the table above are retained.  At lower Compatibility levels, level 100 keywords retain valid object names, but the language capabilities of level 110 corresponding to those keywords are not available.

Once introduced, keywords remain reserved.  For example, the reserved keyword pivot, introduced in compatibility level 90, is also reserved at levels 100, 110, and 120.

If an application uses an identifier that is a keyword for its retention level, the application fails.  To resolve this problem, enclose the identifier in square brackets (()) or quotation marks (“); for example, to upgrade an application that uses the identifier external to compatibility level 90, you can change the identifier to [external] or” external. “.

For more information, seeReserved keywords (transact SQL)

Permissions

Alter permission is required on the database.

Examples

A. Change compatibility level

The following example willThe compatibility level of the AdventureWorks 2012 database is changed to110, SQL Server 2012 (11.x)。

SQL
ALTER DATABASE AdventureWorks2012  
SET COMPATIBILITY_LEVEL = 110;  
GO

The following example returns the compatibility level of the current database.

SQL
SELECT name, compatibility_level   
FROM sys.databases   
WHERE name = db_name();

B. Ignore set language statements (unless below compatibility level 120)

The following queries ignore the set language statement only when the compatibility level is lower than 120.

SQL
SET DATEFORMAT dmy;   
DECLARE @t2 date = '12/5/2011' ;  
SET LANGUAGE dutch;   
SELECT CONVERT(varchar(11), @t2, 106);   

-- Results when the compatibility level is less than 120.   
12 May 2011   

-- Results when the compatibility level is set to 120).  
12 mei 2011

C.

For compatibility level settings of 110 or lower, the recursive reference to the right of the except clause generates an infinite loop.

SQL
WITH   
cte AS (SELECT * FROM (VALUES (1),(2),(3)) v (a)),  
r   
AS (SELECT a FROM Table1  
UNION ALL  
(SELECT a FROM Table1 EXCEPT SELECT a FROM r) )   
SELECT a   
FROM r;

D.

This example shows the difference between styles 0 and 121.  For more information about date and time styles, seeCast and convert (transact SQL)

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

E.

Variable assignment is allowed in statements containing the top-level union operator, but unexpected results are returned.  For example, in the following statement, the@vThe value of the column is assigned to a local variableBusinessEntityID。 By definition, if the select statement returns multiple values, the last value returned is assigned to the variable.  In this case, the last value is correctly assigned to the variable, but the result set of the select union statement is also returned.

SQL
ALTER DATABASE AdventureWorks2012  
SET compatibility_level = 110;  
GO  
USE AdventureWorks2012;  
GO  
DECLARE @v int;  
SELECT @v = BusinessEntityID FROM HumanResources.Employee  
UNION ALL  
SELECT @v = BusinessEntityID FROM HumanResources.EmployeeAddress;  
SELECT @v;

F.

Variable assignments are not allowed in statements that contain the top-level union operator.  Error 10734 is returned.  To correct this error, rewrite the query, as shown in the following example.

SQL
DECLARE @v int;  
SELECT @v = BusinessEntityID FROM   
    (SELECT BusinessEntityID FROM HumanResources.Employee  
     UNION ALL  
     SELECT BusinessEntityID FROM HumanResources.EmployeeAddress) AS Test;  
SELECT @v;

See also

ALTER DATABASE (Transact-SQL) 
Reserved keywords (transact SQL) 
CREATE DATABASE (SQL Server Transact-SQL) 
DATABASEPROPERTYEX (Transact-SQL) 
sys.databases (Transact-SQL) 
sys.database_files (Transact-SQL)
View or change the compatibility level of the database