Two ways to set the database compatibility level and the difference

Ext.: http://blog.csdn.net/htl258/article/details/5696325

--Two ways to set the database compatibility level

--To set up compatible SQL Serve 2005 as an example

--Law One:

ALTER DATABASE database_name SET compatibility_level = 90

GO

--Law II:

EXEC sp_dbcmptlevel database_name,90

GO

database_name

The name to be modified for the database.

Database version corresponding to each parameter value:

SQL Server 2000

2005 = SQL Server

+ = SQL Server 2008

If you change the compatibility level when a user connects to the database, the active query may produce an incorrect result set. For example, if the compatibility level changes when you write a query plan, the written plan may be based on both the old and new compatibility levels, resulting in incorrect planning and possibly inaccurate results. In addition, if you put the plan in the plan cache for subsequent query reuse, the problem can be more complex. To avoid inaccurate query results, we recommend that you use the following procedure to change the compatibility level of the database:

1. Set the database to single-user access mode by using the ALTER database set Single_user.

2. Change the compatibility level of the database in both of these ways.

3. Set the database to multi-user access mode by using the ALTER database set Multi_user.

Differences between compatibility level 80 and compatibility level 90

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

With compatibility level 90 o'clock, the following changes occur for the behavior.

compatibility level set to	compatibility level set to	Possibility of impact
The WITH keyword is always optional for the lock hint in the FROM clause.	However, in some exceptions, these hints are only supported in the FROM clause when table hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL).	High
Supports the use of outer join operators *= and =* in warning messages.	The use of these two operators is not supported; the OUTER JOIN keyword should be used.	High
When a column reference in the ORDER by list is bound to a column defined in the SELECT list, the ambiguity of the column is ignored, and the column prefix is sometimes ignored. This can cause the result set to be returned in an unexpected order. For example, there is a two-part breakdown (<table_alias>. <column>) The ORDER by clause can be used as a reference to a column in the SELECT list, but will token the alias. Please consider the following query. SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1 When executed, the column prefix in ORDER by is ignored. The sort operation does not perform as expected on the specified source column ( x.c1 ), but instead on the derived column defined in the query c1 . The execution plan for this query is displayed by first calculating the values of the derived columns, and then sorting the computed values.	If there is an ambiguity in the column, an error is raised. The column prefix (if any) specified in ORDER by is not ignored when binding to a column defined in the SELECT list. Please 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 executes as expected for the specified source column ( x.c1 ). The execution plan for this query displays the sort operator to t_table sort the rows returned from, and then sorts the values of the derived columns defined in the SELECT list c1 .	In
In insert SELECT, if the union belongs to a different data type, each union branch is converted directly to the type of the INSERT destination column. Even if the union that you are using may fail due to incompatible type conversions, INSERT SELECT causes the union to execute successfully because the branch of the result type of union is never converted.	The result type of UNION is derived independently of the INSERT SELECT. Each branch of the Union is converted to the result type of union and then converted to the target column type of INSERT. If there is an incompatible type in the UNION, the first conversion may result in an error. To run at compatibility level 90, you must repair the union of all incompatible types that are used in INSERT SELECT.	In
When a view or referenced view uses the TOP clause, inserting and updating through the view is not supported correctly in the view that specifies the WITH CHECK OPTION clause.	Inserting and updating through views is not supported in views that use the with CHECK OPTION when the view or referenced view uses the TOP clause.	In
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.	In
Allows the use of SET xact_abort OFF in triggers.	The use of SET xact_abort OFF in triggers is not allowed.	In
Lets you use (and ignore) the FOR BROWSE clause in the view.	Use the FOR BROWSE clause in the view is not allowed.	In
ANSI_WARNINGS does not control domain errors. If Ansi_warnings is set to OFF and ARITHABORT is not changed, the ARITHABORT setting is respected.	The domain error is controlled by Ansi_warnings and is an error with a severity level of 16. If Ansi_warnings or ARITHABORT is on, an error is thrown instead of returning a NULL value. This change may break user scripts that depend on ARITHABORT that are set to OFF.	In
If a direct pass query executed on a remote data source (OpenRowset or OpenQuery) produces a column with duplicate names, the duplicate column names are ignored unless the columns are explicitly named in the query.	If you generate a column with duplicate column names for a direct pass query performed on a remote data source (OpenRowset or OpenQuery), an error is raised.	Low
String constants and varbinary constants of size greater than 8000 are treated as text,ntext , or image.	String constants and varbinary constants larger than 8000 are treated as varchar (max) types (or are treated as nvarchar (max) and varbinary, respectively ( Max)). If the select list contains such an expression, you can change the use select ... The data type of the table created by into.	Low
By converting a lower-priority comparison number in a type hierarchy to a higher-priority type, in each numeric type (smallint,tinyint,int,bigint, Numeric,decimal,smallmoney,and money) are compared.	Numeric type values are compared without conversion. This helps improve performance. However, this can cause some changes in behavior, especially in cases where the conversion causes an overflow exception.	Low
The built-in metadata function that uses the string parameter truncates the input if its input length is greater than 4,000 characters.	If truncation causes the loss of non-whitespace characters, the built-in metadata function throws an error.	Low
The disabled character set in an unquoted identifier remains unchanged.	The Transact-SQL parser supports the Unicode 3.2 standard, which changes the character classification of some international characters that are currently prohibited from being used in delimited identifiers.	Low
When a floating-point domain error occurs (that is, the log () function has a negative argument), set ANSI_WARNINGS on does not overwrite set ARITHABORT OFF. If Ansi_warnings is on but ARITHABORT is OFF, a floating-point domain error does not cause the query to terminate.	Set ANSI_WARNINGS on completely overrides the ARITHABORT OFF setting. In this case, a floating-point domain error causes the query to terminate.	Low
Allows (and ignores) non-integer constants in the ORDER by clause.	Non-integer constants are not allowed in the ORDER by clause.	Low
An empty Set statement is allowed (no set option specified).	Empty SET clauses are not allowed.	Low
The IDENTITY property is not correctly derived for the column generated by the derived table.	The columns generated for the derived table are correctly derived from the IDENTITY property.	Low
The Nullable property of an arithmetic operator that performs operations on a floating-point data type can always be null.	When the input is non-nullable and ansi_warnings is on, the nullable property of the arithmetic operator that performs the operation on the floating-point data type is changed to non-nullable.	Low
INSERT in combination with UNION: In a SELECT statement, the types generated by each result set are converted to the target result type.	INSERT in combination with UNION: SELECT statement, determine the primary type of each branch and convert the result to the target table type before converting it to that type.	Low
In SELECT: In the For XML statement, the hex (' character) and Hex ("character" ) are always manifested, even if not required.	The for XML only manifested Hex (27) and Hex (22) only when needed. These characters are not manifested in the following cases: In the property content, if the attribute value is "delimited" , then the hex (' character) is not manifested, and if the attribute value is ' delimited ' , then the hex ("character" ) is not manifested. In the element content, the Hex (27) and Hex (22) are never manifested.	Low
In the for XML, the timestamp value is mapped to an integer.	In the for XML, the timestamp value is mapped to a binary value. For more information, see for XML support for timestamp data types.	High if timestamp column is used; otherwise low
In the for XML and OPENXML, a large range of Unicode characters (3 bytes) in the name is represented by a 8-bit representation. For example, the for XML uses a 8-bit representation of the Unicode code-bit u+10000 in the following manner: <a_x00010000_ c1= "1"/> td>	In the for XML and OPENXML, the name is represented by a large range of Unicode characters (3 bytes) using 6-bit. For example, the for XML uses a 6-bit representation of the Unicode code-bit u+10000 in the following manner: <a_x010000_ c1= "1"/>	Low
In the for XML, a derived table mapping using AUTO mode is considered transparent. For example: &NBSP; copy code use adventureworkscreate TABLE test (id int), insert INTO Test VALUES (1), insert INTO Test VAL UES (2); SELECT * FROM (select a.ID as a, b.id as B from test a joins Test B on a.id=b.id) test for XML AUTO; If the compatibility level for AdventureWorks is set to 80, the example above will be generated: <a a= "1" ><b b = "1"/></A> <a a= "2" ><b b= "2"/></A>	In the for XML, A derived table mapping that uses AUTO mode is considered opaque. If the compatibility level of AdventureWorks is set to 90, the example above will be generated: <test a= "1" b= "1"/> <test a= "2" b= "2"/>	High If the for XML AUTO mode is applied to the view; otherwise low
In Japanese and Korean only, the string-to -money conversion supports the use of the backslash character (/) as the currency symbol.	In all languages, the backslash character ( /) is accepted in all string-to-money conversions. When/as a currency symbol, ISNUMERIC returns TRUE. For databases on versions of SQL Server prior to SQL Server 2005, this new behavior splits dependent on the included/IsNumeric return value and the language used is neither Japanese nor Korean indexed and computed columns.	Low
The result of an arithmetic operator is always nullable, even if the operand is not nullable and ansi_warnings or ARITHABORT is set to ON.	When Ansi_warnings or ARITHABORT is set to ON, the result of a floating-point arithmetic operator cannot be null if the two operands are not NULL. This nullability change can cause an error in the following scenario: Use bcp to bulk export data in a binary format from a SQL Server 2000 table that contains computed columns that use floating-point arithmetic operators, and then use bcp or BULK INSERT to bulk Import these data into SQL Server 2005 tables that contain the same definition. Attention: When all two 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 with nvarchar as parameters, the value is converted to nvarchar(4000) If the supplied value is varchar. In SQL Server 2000, if a large value is passed, the value is truncated itself.	For built-in functions with nvarchar as parameters, the value is converted to nvarchar(4000) Even if the supplied value is varchar. However, if you pass a large value, SQL Server 2008 generates an error. To run at compatibility level 90, you must fix any custom code that relies on truncation behavior.	Low
Fixed-Length (char,binary , or nchar) strings with variable lengths (varchar,varbinary,nvarchar the Union of Strings returns a fixed-length result.	The union of a variable-size string and a fixed-size string returns a variable-size string. To run at compatibility level 90, you must fix any place (index, query, and computed column) as long as it depends on the type that is obtained by the set of the variable and fixed-size types.	Low
The object name that contains the character 0xFFFF is a valid identifier.	The object name that contains the character 0xFFFF is an invalid identifier and cannot be accessed. To run at compatibility level 90, you must rename the object that contains this character.	Low
In SELECT isnumeric ('<string>'), the comma embedded within<string> is very important. For example, the following SELECT ISNUMERIC(‘121212,12‘) query returns 0. This indicates that the string 121212,12 is not a number.	In SELECT isnumeric ('<string>'), the comma embedded in<string> can be ignored. For example, the following SELECT ISNUMERIC(‘121212,12‘) query returns 1. This indicates that the string 121212,12 is a number.	Low
The colon (:) that follows the reserved keyword in a Transact-SQL statement is ignored.	The colon after the keyword is reserved in a Transact-SQL statement (:) Will cause the statement to fail.	Low
A GROUP by clause in a subquery that references a column in an external query succeeds.	The GROUP by clause in a subquery referencing a column in an external query returns an error, according to the SQL standard.	Low

Differences between the lower compatibility level and level 100

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

The

compatibility level set to 90 or lower	compatibility level set to	Possibility of impact
For multi-statement table-valued functions, the Quoted_identifer setting is always on when they are created, regardless of the session-level setting.	When you create a multi-statement table-valued function, the QUOTED IDENTIFIER session settings are followed.	In
When you create or change a partition function, you evaluate the datetime and smalldatetime literals in the function, and assume that the language is set to us_english.	Evaluates the datetime and smalldatetime literals in the partition function using the current language setting.	In
The FOR BROWSE clause is allowed (but ignored) in the INSERT and SELECT into statements.	The FOR BROWSE clause is not allowed in the INSERT and SELECT into statements.	In
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 a new full-text index.	Support for CREATE Fulltext Stoplist, ALTER Fulltext Stoplist, and DROP Fulltext Stoplist.	Low
MERGE does not force the app as a reserved keyword.	The MERGE is a fully reserved keyword. The MERGE statement is supported under the 100 and 90 compatibility levels.	Low
Using the <dml_table_source> parameter of the INSERT statement throws a syntax error.	You can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and then insert the results into the target table or view. This is done by using the <dml_table_source> parameter 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 all of its nonclustered indexes 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 all of its nonclustered indexes. However, by default, physical consistency checks are performed only on XML indexes, spatial indexes, and indexed views. If with Extended_logical_checks is specified, a logical check is performed on the indexed view, the XML index, and the spatial index (if present). By default, a physical consistency check is performed before a logical consistency check is performed. If NOINDEX is also specified, only logical checks are performed.	Low
If you use an OUTPUT clause with a Data manipulation language (DML) statement, and a run-time error occurs during statement execution, the entire transaction is terminated and rolled back.	If you use an OUTPUT clause 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. A statement abort error that is generated by a DML statement that uses the OUTPUT clause terminates the statement if set Xact_abort to OFF, but the execution of the batch continues, and the transaction is not rolled back. If set Xact_abort to ON, all run-time 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.	CUBE and ROLLUP are reserved keywords in the GROUP by clause.	Low
Apply strict validation to elements of the XML anyType type.	Apply loose validation to elements of the XML anyType type. For more information, see Wildcard component and content validation.	Low
Data Manipulation Language statement cannot query or modify special properties Xsi:nil and Xsi:type . This means that /e/@xsi: nil fails, and /e/@* ignores xsi:nil and Xsi:type Property. However, /E returns the xsi:nil and Xsi:type properties to maintain consistency with SELECT Xmlcol , even if the Code>xsi:nil = "False" is also true.	Special Properties Xsi:nil and xsi:type are stored as general properties and cannot be queried and modified. For example, executing a query SELECT x.query (' a/b/@* ') returns the xsi:nil and Xsi:type All attributes are included. To exclude these types from the query, use @*[namespace-uri (.)! = " Insert xsi namespace URI replace @* instead of (Local-name (.) = "type" or local-name (.) = "nil" .	Low
User-defined functions for converting XML constant string values to SQL Server datetime types are marked as deterministic.	User-defined functions for converting XML constant string values to SQL Server datetime types are marked as indeterminate.	Low
XML syndication and list types are not fully supported.	Full support for federation and list types, including the following features: Syndication of Lists Combined Union List of atomic types List of federated	Low
When an XQuery method is included in a view or inline table-valued function, the SET options required for the method are not validated.	When the view or inline table-valued function contains an xQuery method, the SET options required for the method are validated. An error will be raised if the SET option for the method is incorrect. For more information about the desired option settings, see Setting Options (XML data type).	Low
XML attribute values that contain end-of-line characters (carriage returns and line breaks) are not normalized according to the XML standard. Returns a carriage return and a newline character instead of a single line break.	XML attribute values that contain end-of-line characters (carriage returns and line breaks) are normalized according to the XML standard. That is, all line breaks in external parsed entities, including document entities, are normalized at input by converting the two-character sequence #xD #xA and all #xD that are not followed #xA to a single #xA character. Applications that use properties to transfer string values that contain end-of-line characters will be different from those characters that are submitted. To avoid the normalization process, encode all end-of-line characters using an XML numeric character entity.	Low
The ROWGUIDCOL and IDENTITY column properties may be incorrectly named constraints. For example, a CREATE TABLE T (C1 int CONSTRAINT MyConstraint IDENTITY) statement can be executed, but the constraint name is not persisted and is not accessible to the user.	The ROWGUIDCOL and IDENTITY column properties cannot be named constraints. Returns error 156.	Low
Updating a column with two-way assignments (such as UPDATE T1 SET @v = column_name = <expression> ) can have unintended consequences, as the real-time values of the variables may be used in other clauses, such as WHERE and on clauses, rather than using the statement start value during statement execution. This causes the predicate's meaning to change in unpredictable rows. This behavior applies only if the compatibility level is set to 90 o'clock.	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
In the statement that contains the top-level UNION operator, the variable is allowed to be assigned, but the unexpected result is returned. For example, in the following statement, assign the value of a union EmployeeID column from two tables to the local variable @v . By definition, if the SELECT statement returns more than one value, 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. &NBSP; copy code ALTER DATABASE adventureworksset compatibility_level = 90; Gouse AdventureWorks; Godeclare @v int; SELECT @v = EmployeeID from humanresources.employeeunion allselect @v = EmployeeID from Humanresources.employeeaddress;se Lect @v;	Variable assignments are not allowed in statements that contain the top-level UNION operator. Returns error 10734. To correct the error, rewrite the query, as shown in the following example. Copy Code DECLARE @v int; Select @v = EmployeeID from (select EmployeeID to HumanResources.Employee UNION all SELECT EmployeeID from humanresources.employeeaddress) as Testselect @v;	Low
The ODBC function {fn CONVERT ()} uses the default date format for the language. For some languages, the default format is YDM, which results in a conversion error when convert () is used in conjunction with other functions that require the use of the YMD format, such as {fn curdate ()}.	ODBC function {fn CONVERT ()} uses style 1 when converting to ODBC data types Sql_timestamp, Sql_date, Sql_time, Sqldate, Sql_type_time, and Sql_type_timestamp 21 (a language-independent YMD format).	Low
The ODBC function {fn curdate ()} returns only the date "YYYY-MM-DD" format.	The ODBC function {fn curdate ()} also returns the date and time, such as "Yyyy-mm-dd Hh:mm:ss".	Low
DateTime intrinsics (such as DATEPART) do not require a string input value to become valid datetime literals. For example, SELECT DATEPART (year, ' 2007/05-30 ') can compile successfully.	DateTime intrinsics (such as DATEPART) require a string input value to be a valid datetime literal. Error 241 is returned when invalid datetime literals are used.	Low

Reserved keywords

The compatibility setting also determines which keywords are reserved by the database engine. The following table shows the reserved keywords that are introduced for each compatibility level.

compatibility level settings	Reserved Keywords
100	CUBE, MERGE, ROLLUP
90	EXTERNAL, PIVOT, UNPIVOT, REVERT, tablesample
80	COLLATE, FUNCTION, OPENXML

At a given compatibility level, reserved keywords include all the keywords introduced at that level or lower level. For example, for applications with a compatibility level of 100, all the keywords listed in the previous table are retained. At a lower compatibility level, the keyword for level 100 still retains valid object names, but the language features of level 100 corresponding to those keywords will not be available.

Once introduced, the keywords remain as reserved keywords. For example, the reserved keyword OPENXML introduced in compatibility level 80 is also retained in levels 90 and 100.

If an application uses an identifier that is a keyword for its reserved level, the application will fail. To resolve this issue, enclose the identifier in square brackets ([ ]) or quotation marks (" "); for example, to upgrade an application that uses an identifier EXTERNAL to a compatibility level 90, you can change the identifier to [EXTERNAL] or "EXTERNAL".

Two ways to set the database compatibility level and the difference