4

Can someone please explain why this is happening on SQL Server 2008:

declare @sql Nvarchar(max);

set @sql =N'';

select @sql = @sql +replicate('a',4000) + replicate('b', 6000);

select len(@sql)

Returns: 8000

Multiple sites suggest that as long as first variable is of type NVARCHAR(MAX), truncation should not occur, but it still does.

ps2goat
  • 8,067
  • 1
  • 35
  • 68
Tomato Guy
  • 43
  • 1
  • 5
  • From google search you'd get "Varchar(Max) field cutting off data after 8000 characters SQL Server 2008. I have a field to store some data, the field is declared as varchar(Max) . To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest off" – Mukus Apr 28 '15 at 23:32
  • 1
    This may help: http://dba.stackexchange.com/questions/18483/varcharmax-field-cutting-off-data-after-8000-characters-sql-server-2008 – Felix Pamittan Apr 28 '15 at 23:34

2 Answers2

4

Because 'a' and 'b' are not of type NVARCHAR(MAX)

Like this, it should work:

declare @sql Nvarchar(max),
   @a nvarchar(max),
   @b nvarchar(max);

select @sql =N'', @a = N'a', @b = N'b';

select @sql = @sql +replicate(@a,4000) + replicate(@b, 6000);

select len(@sql)

This is the link to Microsoft's REPLICATE function information: https://msdn.microsoft.com/en-us/library/ms174383.aspx

In there, it says:

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

DavidG
  • 113,891
  • 12
  • 217
  • 223
ps2goat
  • 8,067
  • 1
  • 35
  • 68
  • In my example both REPLICATEs return strings that are less than 8000 bytes already so there should not be an issue of truncation occuring inside of replicate itself. Does this mean that if I am concatenating 100+ strings in T-SQL to construct final dynamic SQL NVARCHAR(MAX) string, all 100+ strings have to be cast to NVARCHAR(MAX) to avoid 8000+ truncation issue? – Tomato Guy Apr 28 '15 at 23:47
  • @TomatoGuy, without the `N`, they are of regular type `varchar` and max out at a length of 8000. Without defining the variables as `nvarchar(max)`, but as `N`, they would be regular `nvarchar`, which max out at a length of 4000. Most of the TSQL functions will give you the smallest of the input types when they return the result. I'll see if I can pull up a link with that info. – ps2goat Apr 28 '15 at 23:50
  • 1
    `REPLICATE` will return a `VARCHAR(8000)` so when you `+` them together, the result will not go beyond `VARCHAR(8000)` – DavidG Apr 28 '15 at 23:51
  • `Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation will occur if the large value is too big for the specified length of the smaller data type.` https://msdn.microsoft.com/en-us/library/ms187928.aspx – ps2goat Apr 28 '15 at 23:52
1

As to why, I don't know. I just know NVARCHAR likes to cut off at 8000 unless you cast things to NVARCHAR(MAX) or you can use CONCAT():

DECLARE @sql Nvarchar(max);

SELECT @sql = CONCAT(@sql,replicate('a',4000),replicate('b', 6000)) --do it this way

--SELECT @sql = CAST(replicate('a',4000) AS NVARCHAR(MAX)) + CAST(replicate('b', 6000) AS NVARCHAR(MAX)) --or this way

Both result in a LEN(@sql) of 10,000

Stephan
  • 5,891
  • 1
  • 16
  • 24
  • -1 because `nvarchar` is limited to 4000 without the `max` for size. `varchar` is limited to 8000 without the `max`. +1 for the `concat` approach, but casting the replicated strings also works: `replicate( cast('a' as nvarchar(max)), 4000) + replicate( cast('b' as nvarchar(max)), 6000)`. The OP could choose whichever route works best at the given time. – ps2goat Apr 29 '15 at 04:33
  • link for the size of nvarchar: https://msdn.microsoft.com/en-us/library/ms186939.aspx – ps2goat Apr 29 '15 at 04:33
  • Thank you. That's why I listed both options. – Stephan Apr 29 '15 at 16:59