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 |