karthik.nallajalla (2/1/2009)
REPLICATE( N'A', 4000000));what is the meaning of N in N'A'? is it the maximum length.
I executed the below statements,
INSERT #t VALUES (1, REPLICATE( N'A', 4000000));
INSERT #t VALUES (2, REPLICATE( 'A', 4000000));
SELECT DATALENGTH(LongName) FROM #t
The result of the SELECT is
8000
16000
I don't know why i got 16000. Can anyone explain me?
Hi Karthik,
Yes, I can.
The result of REPLICATE depends on the first argument. If it's varchar(MAX) or nvarchar(MAX), the result will also be [n]varchar(MAX) and the string won't be truncated. However, a string constant will never be considered ..(MAX) - well, maybe if the string constant is more than 4000 (nvarchar) or 8000 (varchar) characters long, I never tried that. 🙂
For REPLICATE(N'A', 4000000), the input string is nvarchar(something other than max), so the result can not be longer than the longest nvarchar, which is nvarchar(4000). That's 8000 bytes, since two bytes are used for each Unicode character. The implicit cast to nvarchar(MAX) when storing the result in the table does not affect the data length.
For REPLICATE('A', 4000000), the input string is varchar(something other than max) -- note no N in front of varchar!!--. The result will be the longest varchar, which is varchar(8000). That's 8000 bytes as well, of course. But the implicit cast to nvarchar(MAX) will translate each of the 1-byte non-unicode characters to its two-byte unicode equivalent, bumping the data length to 16,000 bytes. And since nvarchar(MAX) has no problem storing an 8,000 byte unicode string, it will.