SQL Server 2008 T-SQL 功能不考虑兼容性级别 [英] SQL Server 2008 T-SQL features do not respect compatibility levels

问题描述

我无意中在脚本中使用了几个 SQL Server 2008 T-SQL 功能，该脚本包含一个将部署到众多客户数据库(其中一些将是 SQL 2005)的函数.

I have inadvertently used a couple of the SQL Server 2008 T-SQL features in a script containing a function which will be deployed to numerous customer databases, some of which will be SQL 2005.

违规言论示例:

create function dbo.fnThisWontWorkOnSQL2005
    ()
returns varchar(max)
as
begin
    declare @intCounter int = 2
    return @intCounter
end

select dbo.fnThisWontWorkOnSQL2005()

在 SQL2008 实例上，这将返回 2.

On a SQL2008 instance this will return 2.

在 SQL2005 实例上，这会提示不能为局部变量分配默认值."

On a SQL2005 instance this advises "Cannot assign a default value to a local variable."

但是，在 SQL2008 R2 实例上运行的兼容级别设置的 SQL 2005 数据库上，代码运行愉快并返回 2.

However, on a SQL 2005 database set by compatibility level running on a SQL2008 R2 instance the code runs happily and returns 2.

当我在 SQL2008R2 实例上开发时出现了我的问题，我曾假设将目标数据库的兼容性级别设置为 SQL Server 2005 会标记任何无效的 T-SQL，尽管这不会发生，或者至少我可以找不到任何东西来阻止它发生.

My issue arises as I am developing on a SQL2008R2 instance and I had assumed that setting the compatibility level of the target database to SQL Server 2005 would flag up any invalid T-SQL though this does NOT happen, or at least I can find nothing to prevent it happening.

有没有人知道防止这种情况发生的方法?我进行了各种搜索，但找不到方法，这对我来说就像一个错误，还是我总是需要在我将部署到的最低 SQL 版本上进行开发?

Is anyone aware of a way to prevent this happening? I have searched variously but cannot find a way and it feels like a bug to me or do I always need to develop on the lowest SQL version I will be deploying to?

推荐答案

这是正确的.

兼容性级别并不意味着完全像旧版本一样运行"
它的意思是在我修复代码的时候少中断".

Compatibility level does not mean "run exactly like an older version"
It means "break less often while I fix the code".

永远不要假设:检查.这就是 ALTER DATABASE 在 MSDN 上所说的(我的粗体):

Never assume: check. This is what ALTER DATABASE says on MSDN says (my bold):

兼容性级别仅提供与早期版本的 SQL Server 的部分向后兼容性.使用兼容性级别作为临时迁移辅助工具来解决由相关兼容性级别设置控制的行为中的版本差异.如果现有的 SQL Server 应用程序受到 SQL Server 2008 中行为差异的影响，请转换应用程序以使其正常工作

Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level 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 2008, convert the application to work properly

如果您的目标是 SQL Server 2005，请在 SQL Server 2005 上进行开发.另请参阅使用 SQL 2008 服务器开发 SQL 2005 应用程序

If you target SQL Server 2005, develop on SQL Server 2005. See developing SQL 2005 application using SQL 2008 server too

这篇关于SQL Server 2008 T-SQL 功能不考虑兼容性级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！