Share via


sp_dbcmptlevel (Transact-SQL)

Sets certain database behaviors to be compatible with the specified version of SQL Server.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_dbcmptlevel [ [ @dbname = ] name ] 
    [ , [ @new_cmptlevel = ] version ]

Arguments

  • [ @dbname= ] name
    Is the name of the database for which the compatibility level is to be changed. Database names must conform to the rules for identifiers. name is sysname, with a default of NULL.
  • [ @new_cmptlevel= ] version
    Is the version of SQL Server with which the database is to be made compatible. version is tinyint, with a default of NULL. The value must be one of the following:

    60 = SQL Server 6.0

    65 = SQL Server 6.5

    70 = SQL Server 7.0

    80 = SQL Server 2000

    90 = SQL Server 2005

    Note

    The values 60 and 65 are deprecated and will be removed in a future release.

    Warning

    SQL Server Management Studio and SQL Server Management Objects (SMO) do not support compatibility level 60. If you use SMO or Management Studio with a database set to compatibility level 60, some operations will produce errors.

Return Code Values

0 (success) or 1 (failure)

Result Sets

If no parameters are specified or if the name parameter is not specified, sp_dbcmptlevel returns an error.

If name is specified without version, the SQL Server 2005 Database Engine returns a message displaying the current compatibility level of the specified database.

Remarks

For all installations of SQL Server 2005, the default compatibility level is 90. Databases created in SQL Server 2005 are set to this level unless the model database has a lower compatibility level. When a database is upgraded to SQL Server 2005 from any earlier version of SQL Server, the database retains its existing compatibility level. This applies to both system and user databases. Use sp_dbcmptlevel to change the compatibility level of the database to 90. To view the current compatibility level of a database, query the compatibility_level column in the sys.databases catalog view.

Using sp_dbcmptlevel for Backward Compatibility

The sp_dbcmptlevel stored procedure affects behaviors only for the specified database, not for the entire server. sp_dbcmptlevel provides only partial backward compatibility with earlier versions of SQL Server. Use sp_dbcmptlevel 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 2005, convert the application to work properly. Then use sp_dbcmptlevel to change the compatibility level to 90. 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).

A database containing an indexed view cannot be changed to a compatibility level lower than 80.

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, setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON. If the database compatibility level is set to 80 or lower, 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.

Environmental Considerations for Using sp_dbcmptlevel

Each sp_dbcmptlevel call must be submitted individually. sp_dbcmptlevel cannot be called from within other contexts, such as a:

  • Stored procedure.
  • Transact-SQL string executed with the EXEC(string) syntax.
  • Batch of Transact-SQL statements.

Differences Between Level 60 or 65 and Level 70, 80, or 90

Setting the compatibility level to 60 or 65 affects many behaviors. Among these are those listed in the left column of the following table.

Note

For additional differences that affect 6.x applications, see "Differences Between Lower Compatibility Levels and Level 90" later in this topic, and the post-6.x reserved keywords in the Remarks section.

Compatibility level setting of 60 or 65

Compatibility level setting of 70 or higher

The result sets of SELECT statements with a GROUP BY clause and no ORDER BY clause are sorted by the GROUP BY columns.

A GROUP BY clause does no sorting on its own. An ORDER BY clause must be explicitly specified for SQL Server to sort any result set. For more information, see SELECT (Transact-SQL).

Columns prefixed with table aliases are accepted in the SET clause of an UPDATE statement.

Table aliases are not accepted in the SET clause of an UPDATE statement. The table or view specified in the SET clause must match that specified immediately following the UPDATE keyword. For more information, see UPDATE (Transact-SQL).

bit columns created without an explicit NULL or NOT NULL option in CREATE TABLE or ALTER TABLE are created as NOT NULL.

The nullability of bit columns without explicit nullability is determined by the session setting of SET ANSI_NULL_DFLT_ON / SET ANSI_NULL_DFLT_OFF or by the database setting of SET ANSI NULL DEFAULT. For more information, see SET (Transact-SQL).

The ALTER COLUMN clause cannot be used on ALTER TABLE.

The ALTER COLUMN clause can be used on ALTER TABLE. For more information, see ALTER TABLE (Transact-SQL).

A trigger created for a table replaces any existing triggers of the same type (INSERT, UPDATE, DELETE). The WITH APPEND option of CREATE TRIGGER can be used to create multiple triggers of the same type.

Triggers of the same type are appended. Trigger names must be unique. The WITH APPEND option is assumed. For more information, see CREATE TRIGGER (Transact-SQL).

When a batch or procedure contains invalid object names, a warning is returned when the batch is parsed or compiled, and an error message is returned when the batch is executed.

For a local invalid object, no warning is returned when the batch is parsed or compiled, and an error message is returned when the batch is executed.

For a remote invalid object, however, deferred name resolution (DNR) is not supported; if an invalid remote table is used in a procedure, the procedure creation fails and returns an error.

ms178653.note(en-US,SQL.90).gifNote:

DNR support (the ability during compilation to reference objects that do not exist until execution time) is applicable only for table or view names. For more information about deferred name resolution, see CREATE PROCEDURE (Transact-SQL).

Queries of the following form are properly executed by ignoring table Y and inserting the SELECT statement results into table X.

INSERT X
SELECT select_list INTO Y

Microsoft SQL Server 7.0 or later returns a syntax error when this same query is executed.

The empty string literal (' ') is interpreted as a single blank.

The empty string literal (' ') is interpreted as an empty string.

DATALENGTH('') returns 1 ('' parsed as a single space).

DATALENGTH(N'') returns 2 (N'' parsed as a single Unicode space).

DATALENGTH('') returns 0.

DATALENGTH(N'') returns 0.

LEFT('123', 0) returns NULL.

LEFT(N'123', 0) returns NULL.

LEFT('123', 0) returns an empty string.

LEFT(N'123', 0) returns an empty string.

LTRIM('     ') returns NULL.

LTRIM(N'     ') returns NULL.

LTRIM('     ') returns an empty string.

LTRIM(N'     ') returns an empty string.

REPLICATE('123', 0) returns NULL.

REPLICATE(N'123', 0) returns NULL.

REPLICATE('123', 0) returns an empty string.

REPLICATE(N'123', 0) returns an empty string.

RIGHT(N'123', 0) returns NULL. RIGHT('123', 0) returns NULL.

RIGHT('123', integer_expression) returns NULL when integer_expression is negative.

RIGHT(N'123', integer_expression) returns NULL when integer_expression is negative.

RIGHT('123', 0) returns an empty string.

RIGHT(N'123', 0) returns an empty string.

RIGHT('123', integer_expression) returns error when integer_expression is negative. RIGHT(N'123', integer_expression) returns error when integer_expression is negative.

RTRIM('     ') returns NULL.

RTRIM(N'     ') returns NULL.

RTRIM('     ') returns an empty string.

RTRIM(N'     ') returns an empty string.

SPACE(0) returns NULL.

SPACE(0) returns an empty string.

The function SUBSTRING(expression, start, length) returns NULL if a start value greater than the number of characters in expression is specified*,* or if length equals zero; for example, SUBSTRING(N'123', 4, 1) returns NULL.

Under identical conditions, SUBSTRING(expression, start, length) returns an empty string that is delimited by a pair of single quotation marks; for example: SUBSTRING(N'123', 4, 1) returns ''.

UPDATETEXT table.textcolumn textpointer 0 NULL NULL results in a null value.

UPDATETEXT table.textcolumn textpointer 0 NULL NULL results in empty text.

The CHARINDEX and PATINDEX functions return NULL only if both the pattern and the expression are NULL.

The CHARINDEX and PATINDEX functions return NULL when any input parameters are NULL.

References to text or image columns in the inserted and deleted tables appear as NULL.

References to text or image columns in the inserted and deleted tables are not allowed.

Retrieving text or image columns from the inserted or deleted tables inside a trigger returns NULL values for text or image columns.

Retrieving text or image columns from the inserted or deleted tables inside a trigger is not allowed and causes an error.

Allows UPDATETEXT to initialize text columns to NULL.

UPDATETEXT initializes text columns to an empty string.

WRITETEXT initializes text columns to NULL.

The concatenation of null yields null setting of sp_dboption is off (disabled), which returns an empty string if any operand in a concatenation operation is NULL.

The concatenation of null yields null setting of sp_dboption is on (enabled), which returns a NULL if any operand in a concatenation operation is NULL.

In an INSERT statement, a SELECT statement returning a scalar value is allowed in the VALUES clause.

The INSERT statement cannot have a SELECT statement in the VALUES clause as one of the values to be inserted.

A ROLLBACK statement in a stored procedure referenced in an INSERT table EXEC procedure statement causes the INSERT statement to be rolled back, but the batch continues.

A ROLLBACK statement in a stored procedure referenced by an INSERT...EXEC statement causes the entire transaction to be rolled back and the batch stops executing.

Differences Between Lower Compatibility Levels and Level 90

This subsection describes new behaviors introduced with compatibility level 90. For additional behavioral differences that affect compatibility level 80 and lower compatibility levels, see the "Reserved Keywords" section later in this section.

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

Compatibility level setting of 80 or lower

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.

In SQL Server 2005, 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 allowed inside a trigger.

SET XACT_ABORT OFF is not allowed 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 non-nullable for the case where the inputs are non-nullable 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"/>

For more information about changes to AUTO mode, see AUTO Mode Enhancements.

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 non-nullable, if both operands are non-nullable.

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.

ms178653.note(en-US,SQL.90).gifNote:

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

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

90

EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE

80

COLLATE, FUNCTION, OPENXML

70

BACKUP, CONTAINS, CONTAINSTABLE, DENY, FREETEXT, FREETEXTTABLE, PERCENT, RESTORE, ROWGUIDCOL, TOP

65

AUTHORIZATION, CASCADE, CROSS, DISTRIBUTED, ESCAPE, FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES, RESTRICT, RIGHT, SCHEMA, WORK

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 90, all of the keywords listed in the preceding table are reserved. At the lower compatibility levels, level-90 keywords remain valid object names, but the level-90 language features corresponding to those keywords are unavailable.

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

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

Permissions

Only the database owner, members of the sysadmin fixed server role, and the db_owner fixed database role (if you are changing the current database) can execute this procedure.

Examples

A. Changing the compatibility level to SQL Server 2000

The following example changes the compatibility level of the AdventureWorks database to 80.

EXEC sp_dbcmptlevel AdventureWorks, 80;
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 90 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, the default level, the following SELECT... ORDER BY statement produces an error because the column name 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.

sp_dbcmptlevel tempdb, 80
SELECT c1, c2 AS c1
    FROM SampleTable
    ORDER BY c1;
GO

The following SELECT... ORDER BY statement works in both compatibility levels.

sp_dbcmptlevel tempdb, 80
SELECT c1, c2 AS c3
FROM SampleTable
ORDER BY c1;
GO

sp_dbcmptlevel tempdb, 90
SELECT c1, c2 AS c3
FROM SampleTable
ORDER BY c1;
GO

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

In compatibility level 90, the default level, the following SELECT...ORDER BY statement produces an error because there is an additional table prefix in the ORDER BY clause.

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.

sp_dbcmptlevel tempdb, 80
SELECT c1 AS x
    FROM SampleTable
    ORDER BY SampleTable.x;
GO

The following SELECT...ORDER BY statement works in both compatibility levels.

sp_dbcmptlevel tempdb, 80
SELECT c1 AS x
    FROM SampleTable
    ORDER BY x;
GO
sp_dbcmptlevel tempdb, 90
SELECT c1 AS x
    FROM SampleTable
    ORDER BY x;
GO

See Also

Reference

Database Engine Stored Procedures (Transact-SQL)
ALTER DATABASE (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
EXECUTE (Transact-SQL)
Reserved Keywords (Transact-SQL)
SELECT (Transact-SQL)
SET (Transact-SQL)
System Stored Procedures (Transact-SQL)
UPDATE (Transact-SQL)

Other Resources

Database Compatibility Level Option
New FOR XML Features
Setting Database Options
Using Options in SQL Server

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006

Updated content:
  • Removed the statement "The compatibility level of the master database cannot be modified."

14 April 2006

New content:
  • Added "Best Practices" section.
  • Added text about the behavior of GROUP BY in a subquery.