Last Friday I was talking at the SoCal DevDay event. Like most of these
events, there's usually a fair number of good questions, but one of
them stood out for me.
In SQL Server 2000, the maximum length of an internally constructed
dynamic SQL Statement is limited to the maximum size of an NVarChar
instance, or a mere 4000 characters (or 8000 bytes). Since SQL Server
2005 has the new NVarChar(max) data type, can we use that for
sp_executesql?
The answer is a yes.
For example, this works in SQL Server 2005 (note that for it to work in
SQL Server 2000, reduce the first 1024 to 3.90625):
USE MASTER
GO
DECLARE @CMD NVARCHAR(MAX)
SET @CMD = 'SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA;'
WHILE LEN(@CMD) < (1024*1024)
BEGIN
SET @CMD = @CMD + @CMD
END
SELECT @CMD,LEN(@CMD)
EXEC SP_EXECUTESQL @CMD
GO
So we can obviously have much larger dynamic SQL statements, but is
there a finite limit to the size of the commands we build this way?
Turns out, interestingly enough, that its both memory and CPU-architecture dependent.
Books on-line provides this guidance:
...The size of the string is limited only by available database server memory. Note: On 64-bit servers, the size of the string is limited to the size of NVARCHAR(MAX).
Hopefully you'll never find yourself needing to write a batch query
that's more than two-gigabytes long, but its better than a skimpy
four-thousand bytes limit we have today.