I have inherited a system where stored procedures have their variables declared as NVARCHAR(MAX):
DECLARE @test_variable NVARCHAR(max);
SET @test_variable = N'.....'
EXECUTE sp_executesql @test_variable
...
How does memory assignment to a NVARCHAR(max) variable and sp_executesql work?
Does SQL assign 2GB of SQL memory for the variable,
and 2GB for sp_executesql command each time this stored procedure is invoked?
Does SQL assign 2GB of SQL memory for the variable
No, as with other languages with variable length strings that can be huge it will allocate as much as it needs (plus a little for overheads) and if you add more to the string later in your procedure more memory is allocated (this allocation may fail as you hit a memory limit, so your code will error out at that point).
In fact if you add more, a fresh string is made and the data from the smaller one copied in along with the new content. This is why this gets progressively slower if you repeatedly add to a long string in a loop. Some languages optimised for strong processing are more intelligent about this and use more advanced structures to reduce the effect, in others it is common to have (or make your own) string builder class. You could in TSQL try to use a strong builder technique by adding sub-strings to a table variable then using STRING_AGG to join it all together more efficiently, but you'd have to carefully benchmark that to see if it actually makes a positive difference (and if you are doing something the needs this in TSQL, your application design might need a rethink!).
