Length of varchar(max) for variables
-
I'm dynamically creating long SQL statemnet in varchar(max) variable, but at the output it looks like it produces not antire string, and same is for when I save it to a file. The length of this string measured with either len() or datalength() functions is 10242, which is actually 10kB + 2 characters for (') at the beginning and the end.
Is real capacity for varchar(max) variables 10 kB, or I am doing something wrong ?
Thanks
-
If by "at the output" you mean when you try to display it in the SSMS query window, you may find that there is a limit to the number of characters that will be displayed.
However, I may be on the wrong track here as I would have thought that you could successfully output it to a file. Though there might be some limitation around this.
From Books On Line
- varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length.
I should think that is more than enough space for your SQL statement.
If you look into the <Tools><Options> menu in SSMS, then look at the query results section you will find where you can configure the output length.
HTH
-
Hi Steve,
Yes, by the output I mean SSMS results window, but when I save it to a file it cuts this string too.
Here is quick script to test it:
set nocount on
declare
@num int,
@stmt varchar(max)
set @num = 1
set @stmt = ''
while @num <= 1000
begin
set @stmt = @stmt + cast(@num as varchar(4)) +
' - Long string to prove that varchar(max) has limitation ' +
char(13) + char(10)
set @num = @num +1
end
select datalength(@stmt)
print @stmt
go
The only difference that in this case it shows length of the string as 61 k in my test.
And BTW, in SSMS Tools/Options we can set maximum number of characters displayed IN EACH COLUMN. I have just ONE column in my string, with many rows.
-
So what you are actually getting is conflicting messages between the visible output and the actual value within the variable.
Firstly, you are saying the string size is about 61K. This seems reasonable form your query as the string you apply is from 60 to 62 characters long depending on the value of @num. The query iterates for 1000 times, giving you 1000 * 61 (give or take a little bit)
So, this information conlcudes that you are loading the varchar(max) variable as expected.
Secondly, to show you some inconsistency, when you do a SELECT on @stmt instead of a PRINT, you get a different output. In my case, the SELECT displays the value into line 134 of the variable, while the PRINT statement only displays into line 131 of the variable.
Also, I tried it with 10000 iterations. The results were only slightly different. The displays were the same, but the size of the variable had changed to 628890 bytes (from 61893).
This suggests that the limitation here is with the ability of SSMS to display the full contents of the variable rather than a problem with varchar(max) as a setting.
Still, this doesn't help if you need to verify the contents of the variable manually.
-
Print only displays about the first 8000 chars or so then gives up.
I wrote this sp a while back for printing large multi-line varchars.
It will try to print a line at a time, if the line is over 8000 chars it will find the nearest space and split there, if there are no spaces it will just split the text at 8000 chars.
CREATE PROC PrintMax
@text varchar(max)
AS
DECLARE
@part varchar(8000),
@length int,
@remaining int,
@count int
--SELECT @count = (len(@text) / 7000)
SELECT @remaining = len(@text)
WHILE @remaining > 0 BEGIN
--find the next new line
SELECT
@length = charindex(char(13) + char(10), @text, 7000) - 1,
@remaining = -2
IF @length < 1 OR @length > 8000 BEGIN
--no new line found, find the next space
SELECT
@length = charindex(char(32), @text, 7000) - 1,
@remaining = -1
IF @length < 1 OR @length > 8000 BEGIN
--no space found, just use split at our max
SELECT @length = 8000, @remaining = 0
END
END
--remaining will have been preset with a negative modifier
--in the statements above to remove the delimiter (cr lf, or space)
SELECT @remaining = @remaining + len(@text) - @length
SELECT @part = left(@text, @length)
IF @remaining > 0
SELECT @text = right(@text, @remaining)
ELSE
SELECT @text = ''
PRINT @part
END
-
Yes Steve, it looks as it keeps entire string internally, but does not print it to output, even to a file.
So I will probably use methodology suggested by Chris, to print it by pieces.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply