SQL Server 静默截断存储过程中的 varchar

Question

根据 此论坛讨论，SQL Server（我使用的是 2005，但我认为这也适用于 2000 和 2008）会默默地将您指定为存储过程参数的任何 varchar 截断为 varchar 的长度，即使使用 INSERT 实际上会导致错误。例如。如果我创建此表：

CREATE TABLE testTable(
    [testStringField] [nvarchar](5) NOT NULL
)

那么当我执行以下命令时：

INSERT INTO testTable(testStringField) VALUES(N'string which is too long')

我收到错误：

String or binary data would be truncated.
The statement has been terminated.

太棒了。保留数据完整性，并且调用者知道这一点。现在让我们定义一个存储过程来插入：

CREATE PROCEDURE spTestTableInsert
    @testStringField [nvarchar](5)
AS
    INSERT INTO testTable(testStringField) VALUES(@testStringField)
GO

并执行它：

EXEC spTestTableInsert @testStringField = N'string which is too long'

没有错误，1 行受影响。将在表中插入一行，testStringField 为“strin”。 SQL Server 默默地截断了存储过程的 varchar 参数。

现在，这种行为有时可能很方便，但我认为没有办法将其关闭。这非常烦人，因为如果我向存储过程传递的字符串太长，我希望事情出错。似乎有两种方法可以解决这个问题。

首先，将存储过程的 @testStringField 参数声明为大小 6，并检查其长度是否超过 5。这看起来有点像 hack，并且涉及大量令人恼火的样板代码。

其次，只需将所有存储过程 varchar 参数声明为 varchar(max)，然后让存储过程中的 INSERT 语句失败。

后者似乎工作正常，所以我的问题是：如果我实际上希望存储过程也失败，那么对 SQL Server 存储过程中的字符串始终使用 varchar(max) 是个好主意吗？传递的字符串有多长？这可能是最佳实践吗？无法禁用的静默截断对我来说似乎很愚蠢。

Answer 1

它只是是。

但我从未注意到问题，因为我的检查之一是确保我的参数与我的表列长度匹配。在客户端代码中也是如此。就我个人而言，我希望 SQL 永远不会看到太长的数据。如果我确实看到了截断的数据，那么造成它的原因就会很明显。

如果您确实觉得需要 varchar(max)，请注意由于 数据类型而导致的巨大性能问题优先级。 varchar(max) 的优先级高于 varchar(n)（最长的为最高）。因此，在这种类型的查询中，您将得到扫描而不是查找，并且每个 varchar(100) 值都会被强制转换为 varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue

编辑：

有一个 打开关于此问题的 Microsoft Connect 项目。

它可能值得包含在Erland Sommarkog 的严格设置中（以及匹配的 Connect 项目）。

编辑 2，马丁斯评论后：

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX);
SELECT @sql = 'B', @nsql = 'B'; 
SELECT 
   LEN(@sql), 
   LEN(@nsql), 
   DATALENGTH(@sql), 
   DATALENGTH(@nsql)
;

DECLARE @t table(c varchar(8000));
INSERT INTO @t values (replicate('A', 7500));

SELECT LEN(c) from @t;
SELECT 
   LEN(@sql + c), 
   LEN(@nsql + c), 
   DATALENGTH(@sql + c), 
   DATALENGTH(@nsql + c) 
FROM @t;

Answer 2

一如既往地感谢 StackOverflow 引发了这种深入的讨论。我最近一直在仔细研究我的存储过程，使用标准的事务方法和 try/catch 块使它们更加健壮。我不同意 Joe Stefanelli 的观点“我的建议是让应用程序端负责”，并且完全同意 Jez 的观点：“让 SQL Server 验证字符串长度会更好”。对我来说，使用存储过程的全部意义在于，它们是用数据库本机语言编写的，应该充当最后一道防线。在应用程序方面，255 和 256 之间的差异只是一个毫无意义的数字，但在数据库环境中，最大大小为 255 的字段根本不接受 256 个字符。应用程序验证机制应尽可能地反映后端数据库，但维护很困难，因此如果应用程序错误地允许不合适的数据，我希望数据库能够给我良好的反馈。这就是为什么我使用数据库而不是一堆 CSV 或 JSON 等文本文件。

我很困惑为什么我的一个 SP 抛出 8152 错误，而另一个 SP 则默默地被截断。我终于发现：引发 8152 错误的 SP 有一个参数，该参数允许比相关表列多一个字符。表列设置为 nvarchar(255)，但参数为 nvarchar(256)。那么，我的“错误”难道不会解决 gbn 的担忧：“大规模的性能问题”吗？或许我们可以不使用 max，而一致地将表列大小设置为 255，并将 SP 参数设置为仅长一个字符，例如 256。这解决了静默截断问题，并且不会产生任何性能损失。
大概还有一些我没有想到的其他缺点，但对我来说这似乎是一个很好的妥协。

更新：
恐怕这个技术并不一致。进一步的测试表明，我有时会触发 8152 错误，有时数据会被静默截断。如果有人能帮助我找到更可靠的方法来处理这个问题，我将不胜感激。

更新2：
请参阅本页 Pyitoechito 的回答。

Answer 3

在这里可以看到相同的行为：

declare @testStringField [nvarchar](5)
set @testStringField = N'string which is too long'
select @testStringField

我的建议是让应用程序端负责在调用存储过程之前验证输入。

Answer 4

更新：恐怕这种技术并不一致。进一步的测试表明，我有时会触发 8152 错误，有时数据会被静默截断。如果有人能帮助我找到更可靠的方法来处理这个问题，我将非常感激。

发生这种情况的原因可能是字符串中的第 256 个字符是空格。 VARCHAR 将在插入时截断尾随空白并仅生成警告。因此，您的存储过程会默默地将字符串截断为 256 个字符，并且您的插入会截断尾随空白（带有警告）。当所述字符不是空白时，它将产生错误。

也许解决方案是使存储过程的 VARCHAR 具有合适的长度以捕获非空白字符。 VARCHAR(512) 可能足够安全。

Answer 5

一种解决方案是：

1. 将所有传入参数更改为 varchar(max)
2. 拥有正确数据长度的 sp 私有变量（只需复制并粘贴所有参数并在末尾添加“int”
3. 声明一个表列名与变量名相同的变量
4. 在表中插入一行，其中每个变量都进入具有相同名称的列
5. 从表中选择到内部变量

这样，您对现有代码的修改将非常少，如下面的示例。

这是原始代码：

create procedure spTest
(
    @p1 varchar(2),
    @p2 varchar(3)
)

这是新代码：

create procedure spTest
(
    @p1 varchar(max),
    @p2 varchar(max)
)
declare @p1Int varchar(2), @p2Int varchar(3)
declare @test table (p1 varchar(2), p2 varchar(3)
insert into @test (p1,p2) varlues (@p1, @p2)
select @p1Int=p1, @p2Int=p2 from @test

请注意，如果传入参数的长度大于限制，而不是默默地截断字符串，SQL Server 将抛出错误。

Answer 6

您始终可以将 if 语句放入 sp 中来检查它们的长度，如果它们大于指定的长度，则会抛出错误。但这相当耗时，并且如果更新数据大小，更新会很痛苦。

Answer 7

这不是解决您今天问题的答案，但它包括 MSSQL 考虑添加的功能建议，这将解决此问题。
指出这一点是 MSSQL 的一个缺点，这一点很重要，因此我们可以通过提高人们的认识来帮助他们解决这个问题。
如果您想投票的话，这是正式建议：
https://feedback.azure.com/forums/908035-sql-server/suggestions/38394241-request-for-new-rule-string-truncation-error-for

我和你一样感到沮丧。
在参数上设置字符大小的全部意义是让其他开发人员立即知道
   传入数据时的大小限制（通过 Intellisense）是多少。
这就像将您的文档直接写入存储过程的签名中。

看，我明白了，变量赋值期间的隐式转换是罪魁祸首。
尽管如此，没有充分的理由花费如此多的能量来应对各种情况
   您被迫解决此功能。
如果你问我，存储过程和函数应该具有相同的引擎规则，
   用于分配参数，在填充表时使用。这个要求真的太多了吗？

所有这些使用更大字符限制的建议
   然后然后在每个存储过程中添加每个参数的验证是荒谬的。
我知道这是确保避免截断的唯一方法，但真的是 MSSQL 吗？
我不在乎它是 ANSI/ISO 标准还是其他标准，这很愚蠢！

当值太长时 - 我希望我的代码每次都会被破坏。
它应该是：不要通过 go，并修复你的代码。
您可能会遇到多个截断错误，这些错误会持续多年，但永远不会发现它们。
确保数据完整性方面发生了什么？

假设您的 SQL 代码仅在在所有参数均已验证之后才会被调用，这是危险的。
我尝试将相同的验证添加到我的网站和它调用的存储过程中，
   我仍然发现我的存储过程中漏掉了网站的错误。这是一次很棒的健全性检查！
如果您想将您的 Sproc 重新用于 WebSite/WebService 并从其他地方调用它，该怎么办
    Sprocs/Jobs/Deployment/Ad-Hoc 脚本（没有前端来验证参数）？

MSSQL 需要“NO_TRUNC”选项来对任何非最大字符串变量强制执行此操作
    （甚至那些用作存储过程和函数参数的参数）。
它可以是连接/会话范围：
    （就像“TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”选项如何影响所有查询）
或者专注于单个变量：
    （就像“NOLOCK”是仅针对 1 个表的表提示）。
或者打开跟踪标志或数据库属性以将其应用于数据库中的所有存储过程/函数参数。

我并不是要求颠覆数十年的遗留代码。
只是向 MS 询问更好地管理我们的数据库的选项。