23

I'm trying to do this:

DECLARE @myVar VARCHAR(MAX)
Loop with cursor
select @myVar = @myVar + bla bla bla
end loop

When the loop ends, @myVar is incomplete, containing only 8000 characters.

I have tryed to use text, but is not allowed to local vars.

What would be a good solution to this case?

xml var?

I have just looked this posts:

How do I pass a string parameter greater than varchar(8000) in SQL Server 2000?

Check if concatenating to a varchar(max) will go beyond max allowable characters

And others through the web.

Regards.

Community
  • 1
  • 1
Eduardo
  • 693
  • 3
  • 9
  • 21
  • 4
    Greater than `varchar(max)` ?? That's 2 GB of data - 2 billion characters - that's more than **200 times** the size of the entire *War and Peace* novel by Leo Tolstoj.... and that's **not big enough for you?!?** – marc_s Aug 23 '12 at 18:16
  • 3
    @marc_s: The question seems to be asking why the poster's variable only contains 8000 characters at the end of the loop; I think the title is misleading. Perhaps the poster can edit to clarify what exactly is being asked here. – Ken White Aug 23 '12 at 18:19
  • @KenWhite: see my response for how to fix this problem. – marc_s Aug 23 '12 at 18:20
  • 1
    How are you testing the length? – Aaron Bertrand Aug 23 '12 at 18:20
  • Hey man, I was thinking that 8000 was the max. I'm sorry. – Eduardo Aug 23 '12 at 18:23
  • IIRC, 8000 bytes is the (default?) limit for retrievals in Query Manager, but it's certainly not the limit for the datatype. – DaveE Aug 23 '12 at 18:24
  • 2
    @marc_s: Saw it, and upvoted. I was just trying to clarify what the poster was actually asking (and trying to get them to edit the question to do so). – Ken White Aug 23 '12 at 18:24
  • DaveE I think that is it man! I was not seeing the total amount of text in query result when I was printing @myVar variable. – Eduardo Aug 23 '12 at 18:31

1 Answers1

40

Seriously - VARCHAR(MAX) can store up to 2 GB of data - not just 8000 characters.....

Try this:

DECLARE @myVar VARCHAR(MAX) = ''

DECLARE @ix INT = 1

WHILE @ix < 1000
BEGIN
    set @myVar = @myVar + CAST('bla bla bla' AS VARCHAR(MAX))
    SET @ix = @ix + 1
END

SELECT DATALENGTH(@myvar)

This will return a value higher than 8000 characters after 1000 iterations.

The point is: if you're using varchar(max), you need to make sure to always cast all your strings to varchar(max) explicitly - as I did in this example. Otherwise, SQL Server will fall back to "regular" varchar processing, and that's indeed limited to 8000 characters....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    This works fine for me without the explicit cast. Where you have to be careful is when using built-ins like REPLICATE. – Aaron Bertrand Aug 23 '12 at 18:22
  • 1
    @AaronBertrand: ahhh.... interesting - yes - it does seem to work just fine - even without the cast ....hmm.... – marc_s Aug 23 '12 at 18:22
  • I think the problem was I was printing @myVar and I was not seeing all of the chars. That is it normal? – Eduardo Aug 23 '12 at 18:28
  • 5
    @Murphy: the UI tools like SQL Server Mgmt Studio have a limit on how much they show - yes, that's normal. – marc_s Aug 23 '12 at 18:29
  • Doesn't Varchar(max) allocate too many locations in the RAM? I mean, nvarchar allocates as much as it needs, unlike the varchar, which may not be efficient. – Samer_Azar Jul 11 '17 at 06:48
  • 1
    @Samer_Azar: varchar(max) **also** allocates just as much memory as it needs – marc_s Jul 11 '17 at 06:49
  • I tried this solution and it stopped at 43680 characters. Is it possible to store more? – A.Rashad Aug 22 '17 at 20:36
  • 3
    @A.Rashad: `varchar(max)` stores **up to 2 GB** (or **2 billion**) characters. But the Management Studio is known to have certain limitations well below that limit - it really depends on *how* you tried to insert that data..... – marc_s Aug 22 '17 at 21:06
  • @marc_s Hi, Is there any way I can see the whole data from management studio? – Sumesh Es Jun 10 '20 at 17:31