T-SQL 中 varchar 和 nvarchar 的奇怪长度 [英] The weird length of varchar and nvarchar in T-SQL
问题描述
我有一个关于Transcat SQL的问题，下面是sql代码；
I had a question about the Transcat SQL, below is the sql code;
DECLARE @main nVARCHAR(max);
--there are over 4000 characters to @main
set @main = '01234567890123456789...';
Print len(@main)
@main 的长度是正确的，例如4007但是，如果我将代码更改为以下内容:
the length of @main is correct, e.g. 4007 however, if I change the code to below:
DECLARE @main nVARCHAR(max);
--there are over 4000 characters to @main
set @main = N'01234567890123456789...';
Print len(@main)
@main 的长度永远是 4000，很奇怪，我不明白.另一件事是，如果我将代码更改为以下内容:
the length of @main will always be 4000, it is weird, I don't understand. Another thing is if I change the code to below:
DECLARE @main nVARCHAR(max), @split nVARCHAR(500);
--there are 500 characters to @split
set @split = '01234567890123456789...';
set @main = @split + @split + @split + @split + @split + @split + @split + @split + @split + @split;
Print len(@main)
@main 的长度是 4000，为什么?无论我在'01234567890123456789 ...'之前添加字母N吗?如果我将@split 更改为 varchar(500)声明@main nVARCHAR(max), @split VARCHAR(500);
The length of @main is 4000, why? No matter I add letter N before '01234567890123456789...'? if I change the @split to varchar(500) DECLARE @main nVARCHAR(max), @split VARCHAR(500);
--there are 500 characters to @split
set @split = '01234567890123456789...';
-- 10 @split
set @main = @split + @split + @split + @split + @split + @split + @split + @split + @split + @split;
Print len(@main)
@main 的长度是正确的，无论我在 ''01234567890123456789...' 前加字母 N，@main 的长度总是 5000
The length of @main is correct, no matter I add letter N before ''01234567890123456789...' or not, the length of @main is always 5000
推荐答案
您遇到了字符串表达式隐式类型的问题.除了 MAX 长度之外，VARCHAR 类型可以容纳 8000 个字符(受 SQL SERVER 中页面大小的限制)，并且因为 NVARCHAR 存储双字节字符，它可以容纳一半的字符数 (4000).默认情况下，使用 varchar 的最大长度 8000 或 nvarchar 的最大长度 4000 来评估字符串表达式(文字、连接字符串等)>.您必须将表达式显式转换为 MAX 类型以获得您期望的行为.
You're running into issues with implicit types of string expressions. With the exception of MAX lengths, a VARCHAR type can hold 8000 chars (limited by the size of a page in SQL SERVER) and, because NVARCHAR stores double-byte chars, it can hold half the number of chars (4000). By default string expressions (literals, concatenated strings etc) are evaluated using either a max length of 8000 for varchar or a max len of 4000 for nvarchar. You have to explicitly cast the expression to a MAX type to get the behavior you expect.
在您的第一个示例中，您的字符串表达式 '01234567890123456789...' 被隐式计算为 VARCHAR(8000) 然后被隐式转换为 NVARCHAR(MAX) 因此它正确保留了字符数，因为您的表达式只有 4007 个字符.
In your first example, your string expression '01234567890123456789...' is implicitly evaluated as a VARCHAR(8000) which is then implicitly converted to NVARCHAR(MAX) so it correctly retains the number of characters since your expression only had 4007 chars.
当您将字符串表达式转换为 unicode(即 N'01234567890123456789...' 时，它会将字符串计算为 NVARCHAR(4000)，然后再将值分配给您的 <代码>NVARCHAR(MAX) 变量.
When you convert your string expression to unicode (i.e. N'01234567890123456789...' it evaluates the string as NVARCHAR(4000) before assigning the value to your NVARCHAR(MAX) variable.
把你的第二个例子改成这样:
Change your second example to this:
DECLARE @main nVARCHAR(max);
--there are over 4000 characters to @main
set @main = CAST(N'01234567890123456789...' AS NVARCHAR(MAX));
Print len(@main)
它会按照您的预期运行.
And it will behave as you expect.
同样，当您连接 @split 变量时，SQL Server 将表达式计算为 NVARCHAR(4000)，您必须将最左边的值转换为 NVARCHAR(MAX) 以便将表达式计算为 NVARCHAR(MAX)
Likewise when you're concatenating the @split variables, SQL Server is evaluating the expression as NVARCHAR(4000) you have to cast the leftmost value as NVARCHAR(MAX) in order to have the expression evaluated as NVARCHAR(MAX)
DECLARE @main nVARCHAR(max), @split nVARCHAR(500);
--there are 500 characters to @split
set @split = '01234567890123456789...';
set @main = CAST(@split as NVARCHAR(MAX)) + @split + @split + @split + @split + @split + @split + @split + @split + @split;
Print len(@main)
它会起作用.
这篇关于T-SQL 中 varchar 和 nvarchar 的奇怪长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
