主页 / dba / 问题 / 120756
Accepted
Erik
Asked: 2015-11-12 08:04:11 +0800 CST

为什么 TVP 必须是 READONLY，其他类型的参数为什么不能是 READONLY

  • 772

根据此博客，函数或存储过程的参数如果不是参数，则本质上是按值传递，如果它们是OUTPUT参数，则本质上被视为更安全的按引用传递版本OUTPUT。

起初我以为强制声明 TVP 的目的是READONLY向开发人员明确表明 TVP 不能用作OUTPUT参数，但肯定还有更多的事情要做，因为我们不能将非 TVP 声明为READONLY. 例如以下失败：

create procedure [dbo].[test]
@a int readonly
as
    select @a

消息 346，级别 15，状态 1，过程测试
参数“@a”不能声明为 READONLY，因为它不是表值参数。

  1. 由于统计数据不存储在 TVP 上，阻止 DML 操作的原因是什么？
  2. OUTPUT是否与出于某种原因不希望 TVP 成为参数有关？
sql-server parameter

2 个回答

  1. Best Answer

    解释似乎与以下组合相关：a）链接博客中未在此问题中提及的详细信息，b）TVP 的语用学适合参数始终如何传入和传出，c）和性质表变量。

    1. 链接的博客文章中缺少的细节正是变量如何传入和传出存储过程和函数（这与“如果它们是 OUTPUT 参数，则传递引用的更安全版本”的问题中的措辞有关） ：

      TSQL 使用复制输入/复制输出语义将参数传递给存储过程和函数......

      ...当存储过程完成执行（没有遇到错误）时，会进行复制，以使用在存储过程中对其进行的任何更改来更新传入的参数。

      这种方法的真正好处在于错误情况。如果在存储过程的执行过程中发生错误，对参数所做的任何更改都不会传播回调用者。

      如果 OUTPUT 关键字不存在，则不会进行复制。

      底线：
      如果遇到错误，存储过程的参数永远不会反映存储过程的部分执行。

      这个难题的第 1 部分是参数总是“按值”传递。并且，只有当参数被标记为OUTPUT 并且存储过程成功完成时，当前值才真正被发回。如果OUTPUT值真的是“通过引用”传递的，那么指向该变量内存位置的指针将是传递的东西，而不是值本身。如果您确实传入了指针（即内存地址），那么所做的任何更改都会立即反映，即使存储过程的下一行导致错误并中止执行。

      总结第 1 部分：始终复制变量值；它们没有被它们的内存地址引用。

    2. 考虑到第 1 部分，当传入的变量非常大时，始终复制变量值的策略可能会导致资源问题。我还没有测试过如何处理 blob 类型（VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)、XML以及那些不应再使用的类型：TEXT、NTEXT和IMAGE），但可以肯定地说，传入的任何数据表都可能非常大。对于那些开发 TVP 功能的人来说，希望真正的“通过引用”能力来防止他们的酷新功能破坏健康数量的系统（即想要一种更具可扩展性的方法）是有意义的。正如您在文档中看到的那样，他们所做的就是：

      Transact-SQL 通过引用将表值参数传递给例程，以避免复制输入数据。

      此外，这种内存管理问题并不是一个新概念，因为它可以在 SQL Server 2005 中引入的 SQLCLR API 中找到（TVP 是在 SQL Server 2008 中引入的）。在将数据传递NVARCHAR到VARBINARYSQLCLR 代码（即 SQLCLR 程序集中的 .NET 方法上的输入参数）时，您可以选择使用“按值”方法通过使用其中一个SqlString或SqlBinary分别使用，或者您可以使用“按引用” " 分别使用SqlChars或SqlBytes分别使用的方法。SqlCharsand类型允许将SqlBytes数据完全流式传输到 .NET CLR 中，这样您就可以提取小块的大值，而不是复制整个 200 MB（最多 2 GB，对）的值。

      总结第 2 部分：TVP 就其本质而言，如果停留在“始终复制价值”模型中，就会倾向于消耗大量内存（从而降低性能）。因此，TVP 做了真正的“参考传递”。

    3. 最后一点是为什么第 2 部分很重要：为什么真正“通过引用”传递 TVP 而不是复制它会改变任何事情。第 1 部分的基础设计目标回答了这一点：未成功完成的存储过程不应以任何方式更改任何输入参数，无论它们是否标记为OUTPUT。允许 DML 操作将对 TVP 的值产生直接影响，因为它存在于调用上下文中（因为通过引用传递意味着您正在更改传入的内容，而不是传入内容的副本）。

      现在，某个地方的某个人此时可能正在对他们的监视器说：“好吧，只需构建一个自动设备，用于回滚对 TVP 参数所做的任何更改（如果有任何更改被传递到存储过程）。嗯。问题解决了。” 没那么快。这就是表变量的本质所在：对表变量所做的更改不受事务的约束！所以没有办法回滚更改。事实上，如果需要回滚，这是一种用于保存事务中生成的信息的技巧:-)。

      总结第 3 部分：表变量不允许在导致存储过程中止的错误的情况下“撤消”对它们所做的更改。这违反了让参数从不反映部分执行的设计目标（第 1 部分）。

    Ergo：需要该READONLY关键字来防止对 TVP 的 DML 操作，因为它们实际上是“通过引用”传递的表变量，因此对它们的任何修改都会立即反映，即使存储过程遇到错误，也没有防止这种情况的其他方法。

    此外，其他数据类型的参数不能使用READONLY，因为它们已经是传入内容的副本，因此它不会保护任何尚未受保护的内容。那个，以及其他数据类型的参数的工作方式是为了读写，所以改变这个 API 以包含一个只读概念可能会做更多的工作。

    • 21

  2. 根据Martin Smith对问题的评论生成的社区 Wiki 答案

    为此，有一个活动的 Connect 项目（由 Erland Sommarskog 提交）：

    放宽SP相互调用时表参数必须为只读的限制

    到目前为止，微软的唯一回应是（强调）：

    感谢您对此的反馈。我们从大量客户那里收到了类似的反馈。允许读取/写入表值参数涉及 SQL 引擎端以及客户端协议的大量工作。由于时间/资源限制以及其他优先事项，我们将无法将这项工作作为 SQL Server 2008 版本的一部分进行。但是，我们已经调查了这个问题，并在我们的雷达中坚定地考虑作为 SQL Server 下一个版本的一部分来解决。我们感谢并欢迎这里的反馈。

    Srini Acharya
    高级项目经理
    SQL Server 关系引擎

    • 7

相关问题