'This' is a
N'This' is an
I'll guess that, after
@tblUpdateStats_List, the remainder of your string was more than 4000 characters.
I did some experiments (in SQL 2016), and it looks like when a
varchar string is implicitly converted to
nvarchar, it is converted to
I ran the following query:
DECLARE @myNVar NVARCHAR(MAX);
SET @myNVar = 'ABCDEFG';
SET @myNVar = 'QQQ' + REPLICATE(@myNVar, 1000)
SELECT LEN(@myNVar), SUBSTRING(@myNVar, 6990, 25), RIGHT(@myNVar, 20);
<literal> initially being an nvarchar string constructed as follows:
- The character 'Z', repeated 3 times
- the character 'z', repeated 1027 times
- the character 'X', repeated 3 times
- The resulting string copied, and pasted in 3 more times (wit the fourth EOL removed)
This string was 4,138 characters long (on Windows, where EOL is CR+LF), so the total string length should have been 3 + 7000 + 4138 + 3, or 11,144 characters
When run with the literal as an
nvarchar string, got the following results:
11144 ABCDEFGABCDEFGZZZzzzzzzzz zzzzzzzzzzzzzzXXXQQQ
So, correct length, and expected values at end of replicated string, at start of literal, and at end of literal.
Then, I changed the literal to a
varchar string, and got this:
11006 ABCDEFGABCDEFGZZZzzzzzzzz zzzzzzzzzzzzzzzzzQQQ
So, length shows 3 + 7000 + 4000 + 3 = 11006 - what we'd expect if the varchar value was truncated down to just 4,000 characters. And, we don't see
XXX before the final
QQQ, also as we'd expect under these circumstances.
FYI - If I
varchar literal to
nvarchar(MAX), my results went back to the first ones, so an explicit cast avoids this problem.