兼容级别 80 的实际行为是什么？

从文档：

将某些数据库行为设置为与指定版本的 SQL Server 兼容。
...
兼容级别仅提供与早期版本的 SQL Server 的部分向后兼容性。使用兼容性级别作为临时迁移辅助来解决由相关兼容性级别设置控制的行为中的版本差异。

在我的解释中，兼容模式是关于行为和语法解析，而不是解析器说“嘿，你不能使用ROW_NUMBER()！”之类的东西。有时，较低的兼容性级别允许您继续使用不再受支持的语法，有时它会阻止您使用新的语法结构。该文档列出了几个明确的示例，但这里有一些演示：

将内置函数作为函数参数传递

此代码适用于兼容级别 90+：

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL);

但在 80 中，它产生：

消息 102，级别 15，状态 1
'(' 附近的语法不正确。

这里的具体问题是，在 80 中，您不允许将内置函数传递给函数。如果您想保持 80 兼容模式，可以通过以下方式解决此问题：

DECLARE @db_id INT = DB_ID();

SELECT * 
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL);

将表类型传递给表值函数

与上述类似，在使用 TVP 并尝试将其传递给表值函数时，您可能会遇到语法错误。这适用于现代兼容级别：

CREATE TYPE dbo.foo AS TABLE(bar INT);
GO
CREATE FUNCTION dbo.whatever
(
  @foo dbo.foo READONLY
)
RETURNS TABLE
AS 
  RETURN (SELECT bar FROM @foo);
GO

DECLARE @foo dbo.foo;
INSERT @foo(bar) SELECT 1;
SELECT * FROM dbo.whatever(@foo);

但是，将兼容级别更改为 80，并再次运行最后三行；您收到此错误消息：

Msg 137, Level 16, State 1, Line 19
必须声明标量变量“@foo”。

除了升级兼容级别或以不同的方式获得结果之外，我并没有真正想到任何好的解决方法。

在 APPLY 中使用限定的列名

在 90 兼容模式及更高版本中，您可以毫无问题地执行此操作：

SELECT * FROM sys.dm_exec_cached_plans AS p
  CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t;

但是，在 80 兼容模式下，传递给函数的限定列会引发通用语法错误：

消息 102，级别 15，状态 1
'.' 附近的语法不正确。

ORDER BY 恰好与列名匹配的别名

考虑这个查询：

SELECT name = REVERSE(name), realname = name 
FROM sys.all_objects AS o
ORDER BY o.name;

在80兼容模式下，结果如下：

001_ofni_epytatad_ps   sp_datatype_info_100
001_scitsitats_ps      sp_statistics_100
001_snmuloc_corps_ps   sp_sproc_columns_100
...

在90兼容模式下，结果大相径庭：

snmuloc_lla      all_columns
stcejbo_lla      all_objects
sretemarap_lla   all_parameters
...

原因？SELECT在 80 兼容模式下，表前缀被完全忽略，因此它是按照列表中别名定义的表达式排序的。在较新的兼容性级别中，会考虑表前缀，因此 SQL Server 将实际使用表中的该列（如果找到的话）。如果ORDER BY在表中未找到别名，则较新的兼容性级别不会容忍歧义。考虑这个例子：

SELECT myname = REVERSE(name), realname = name 
FROM sys.all_objects AS o
ORDER BY o.myname;

结果按myname80 中的表达式排序，因为表前缀再次被忽略，但在 90 中会生成以下错误消息：

消息 207，级别 16，状态 1，第 3 行
列名“myname”无效。

这在文档中也得到了解释：

将列表中的列引用绑定到ORDER BY列表中定义的SELECT列时，会忽略列歧义，有时会忽略列前缀。这可能会导致结果集以意外的顺序返回。

例如，可以ORDER BY接受具有单个两部分列 ( <table_alias>.<column>) 的子句，该子句用作对 SELECT 列表中列的引用，但忽略表别名。考虑以下查询。

SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1

执行时，列前缀在ORDER BY. 排序操作未按x.c1预期在指定的源列 ( ) 上发生；相反，它发生在派生的c1查询中定义的列。此查询的执行计划显示，首先计算派生列的值，然后对计算值进行排序。

ORDER BY SELECT 列表中没有的东西

在 90 兼容模式下，您不能这样做：

SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
UNION ALL
SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
ORDER BY a.name;

结果：

如果语句包含 UNION、INTERSECT 或 EXCEPT 运算符，则消息 104、级别 16、状态 1 ORDER BY 项必须出现在选择列表中。

但是，在 80 中，您仍然可以使用这种语法。

旧的、讨厌的外连接

80 模式还允许您使用旧的、已弃用的外连接语法 ( *=/=*)：

SELECT o.name, c.name
FROM sys.objects AS o, sys.columns AS c
WHERE o.[object_id] *= c.[object_id];

在 SQL Server 2008 / 2008 R2 中，如果您在 90 或更高版本中，您会收到以下详细消息：

消息 4147，级别 15，状态 1
查询使用非 ANSI 外连接运算符（“ *=”或“ =*”）。要在不修改的情况下运行此查询，请使用 ALTER DATABASE 的 SET COMPATIBILITY_LEVEL 选项将当前数据库的兼容级别设置为 80。强烈建议使用 ANSI 外连接运算符（LEFT OUTER JOIN、RIGHT OUTER JOIN）重写查询。在 SQL Server 的未来版本中，即使在向后兼容模式下也不支持非 ANSI 连接运算符。

在 SQL Server 2012 中，这根本不再是有效的语法，并产生以下结果：

消息 102，级别 15，状态 1，第 3 行
'*=' 附近的语法不正确。

当然，在 SQL Server 2012 中，您不能再使用兼容级别解决此问题，因为不再支持 80。如果您在 80 兼容模式下升级数据库（通过就地升级、分离/附加、备份/恢复、日志传送、镜像等），它将自动为您升级到 90。

没有 WITH 的表格提示

在 80 兼容模式下，您可以使用以下内容，将观察到表格提示：

SELECT * FROM dbo.whatever NOLOCK; 

在 90+ 中，这NOLOCK不再是表提示，而是别名。否则，这将起作用：

SELECT * FROM dbo.whatever AS w NOLOCK;

但它没有：

消息 1018，级别 15，状态 1
'NOLOCK' 附近的语法不正确。如果打算将其作为表提示的一部分，则现在需要 WITH 关键字和括号。有关正确的语法，请参阅 SQL Server 联机丛书。

现在，为了证明在第一个示例中未观察到在 90 兼容模式下的行为，请使用 AdventureWorks（确保它处于更高的兼容级别）并运行以下命令：

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader UPDLOCK;
SELECT * FROM sys.dm_tran_locks 
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 0
COMMIT TRANSACTION;

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader WITH (UPDLOCK);
SELECT * FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 2
COMMIT TRANSACTION;

这个问题特别严重，因为行为发生变化而没有错误消息甚至错误。这也是升级顾问和其他工具甚至可能没有发现的东西，因为据它所知，这是一个表别名。

涉及新日期/时间类型的转换

SQL Server 2008 中引入的新日期/时间类型（例如date和）支持的范围比原来的和）datetime2大得多。无论兼容级别如何，超出支持范围的值的显式转换都将失败，例如：datetimesmalldatetime

SELECT CONVERT(SMALLDATETIME, '00010101');

产量：

消息 242，级别 16，状态 3
将 varchar 数据类型转换为 smalldatetime 数据类型导致值超出范围。

但是，隐式转换将在较新的兼容性级别中自行解决。例如，这将适用于 100+：

SELECT DATEDIFF(DAY, CONVERT(SMALLDATETIME, SYSDATETIME()), '00010101');

但在 80（以及 90）中，它会产生与上述类似的错误：

消息 242，级别 16，状态 3
将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。

触发器中的冗余 FOR 子句

这是出现在这里的一个晦涩的场景。在 80 兼容模式下，这样会成功：

CREATE TABLE dbo.x(y INT);
GO
CREATE TRIGGER tx ON dbo.x
FOR UPDATE, UPDATE
------------^^^^^^ notice the redundant UPDATE
AS PRINT 1;

在 90 及更高的兼容性中，这不再解析，而是您收到以下错误消息：

消息 1034，级别 15，状态 1，过程 tx
语法错误：触发器声明中操作“更新”的重复规范。

枢轴/取消枢轴

某些形式的语法在 80 岁以下不起作用（但在 90 岁以上就可以正常工作）：

SELECT col1, col2
FROM dbo.t1
UNPIVOT (value FOR col3 IN ([x],[y])) AS p;

这产生：

消息 156，级别 15，状态 1
关键字“for”附近的语法不正确。

对于一些解决方法，包括CROSS APPLY，请参阅这些答案。

新的内置功能

尝试在兼容级别 < 110 的数据库中使用新功能TRY_CONVERT()。在那里根本无法识别它们。

SELECT TRY_CONVERT(INT, 1);

结果：

消息 195，级别 15，状态 10
'TRY_CONVERT' 不是可识别的内置函数名称。

推荐

仅在您确实需要时才使用 80 兼容模式。由于在 2008 R2 之后的下一个版本中将不再可用，所以你最不想做的就是在这个兼容级别编写代码，依赖你看到的行为，然后当你不能再有一大堆损坏时使用该兼容级别。要有远见，不要试图通过争取时间继续使用旧的、已弃用的语法将自己逼入绝境。

兼容性级别仅用于允许从早期版本的 SQL Server 进行受控迁移。Compat Level 90 不排除使用新功能，它只是意味着以与 SQL Server 2005 工作方式兼容的方式保留数据库的某些方面。

有关详细信息，请参阅http://msdn.microsoft.com/en-us/library/bb510680.aspx。