Friday, March 9, 2012

local VARCHAR and memory allocation in SP

I have an sp that includes something like this:
DECLARE @.s varchar(8000), @.sw varchar(128)
SET @.s = ''
WHILE ([some condition]) BEGIN
SELECT @.sw = [some value]
SET @.s = @.s + @.sw
END
Question: When I declare @.s, are all possible 8000(+) bytes allocated?
And every time I append to it's value, is the memory freed, and another
8000+ bytes are allocated? Obviously this would not bode well for
something that runs often, with many iterations, which it is.My concern, by the way, is memory fragmentation in the server due to
the large number of allocations/frees that are happening. However, I
would imagine that using a larger block for each alloc/free would be
better than a smaller block, in this respect. But, I'm no guru on such
things.
Does anyone have advice?

No comments:

Post a Comment