Larger string size now for sure, but how long can it be?
Get ready for some really great stuff
I’m fascinated by sp_executesql these days. Not necessarily by what it does – we all know the advantages of using it over EXEC (plan caching, parameter changing on the fly, etc.) – but rather by how widespread its use is. I see it in most reporting tools, in .Net, in the ORM tools (such as NHibernate, Entity Framework), LINQ, and even scripting out a database in SQL Server 2005/2008, which generates the TSQL syntax in a string called by sp_executesql. Since when did all of this happen? Everything seems to be firing TSQL using sp_executesql to get to SQL Server. No doubt that at the release of SQL 2005 much of this began. Checking Books Online over the past ten years or so below, not much has changed in the specification of the syntax, but under the covers much appears to have changed. My question is…I see that the string size has been expanded, but to exactly what? Read below and you’ll see what I mean. The specification in Books Online does not match with what I come up with. Oh, and one caveat here - This is a completely worthless blog as far as answering any questions that I raise, so don’t be disappointed at the end.
In another life
I have pasted a portion of Books Online (BOL) from versions 7, 2000, 2005, and 2008. Check it out and see the differences for sp_executesql:
SQL Server 7 Books Online
sp_executesql (T-SQL)
Executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.
Syntax
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
Arguments
- [@stmt =] stmt
- Is a Unicode string containing a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. More complex Unicode expressions (such as concatenating two strings with the + operator) are not allowed. Character constants are not allowed. If a constant is specified, it must be prefixed with an N. For example, the Unicode constant N’sp_who’ is legal, but the character constant ‘sp_who’ is not. The size of the string is limited only by available database server memory.
SQL Server 2000 Books Online
sp_executesql
Executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.
Syntax
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
Arguments
[@stmt =] stmt
Is a Unicode string containing a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. More complex Unicode expressions (such as concatenating two strings with the + operator) are not allowed. Character constants are not allowed. If a constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is legal, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory.
SQL Server 2005 Books Online
sp_executesql [ @stmt = ] stmt
[ {, [@params=] N'@parameter_name data_type [ [ OUT [ PUT ][,...n]' } {, [ @param1 = ] 'value1' [ ,...n ] }
]Arguments
[ @stmt = ] stmt
Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed. If a constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. 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).
SQL Server 2008 Books Online
sp_executesql [ @stmt = ] stmt
[ {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' } {, [ @param1 = ] 'value1' [ ,...n ] }
]Arguments
[ @stmt = ] stmt
Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a Unicode variable. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed. If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).
I know what you’re thinking - just get to the good part already
What I am interested in here, specifically, is the size of the string that can be executed. It appears to me that SQL Server 7 and 2000 look about the same. Both require an nvarchar data type of which the maximum length is 4000. Let’s compare the verbiage centered on ‘stmt’ for the above:
SQL 7 - Is a Unicode string containing a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext.
SQL 2000 - Is a Unicode string containing a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext.
SQL 2005 - Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. Note: On 64-bit servers, the size of the string is limited to the size of nvarchar(max).
SQL 2008 - Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a Unicode variable. The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).
SQL 7 and SQL 2000 do in fact read the same as far as the length of the string goes. SQL 2005 and SQL 2008 are a bit different, with the latter telling me to make the variable unicode, probably just making this clearer to the reader since you can’t intermingle unicode and non-unicode strings here. How about some examples, fiddling with the string size? Note that my TSQL string that I am using is 103 characters if you’d like to count along.
Example 1: SQL 2000 - “Do we really get 4000 bytes?”
Uh, no, we don’t. I get 3894 here. Add one more space to the replicate function and you get an error as it chops off the SQL string. Notice that I do not have an “N” prefixed to the string. If I add this national character N identifier in front of my string (just before “select @max_titleOUT”), it still runs ok. Keep this in mind.
DECLARE @IntVariable int
DECLARE @SQLString nvarchar(4000)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @max_title varchar(30)
SET @IntVariable = 1
SET @SQLString = REPLICATE(' ', 3894) + 'select @max_titleOUT = max(JobTitle)
FROM my_deebee.dbo.Employee
WHERE Organizationlevel = @level';
SET @ParmDefinition = N'@level smallint, @max_titleOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;
Example 2: SQL 2008
Notice now that we can open up this string to be much longer than before. Previously SQL 2000 and earlier you were limited to 4000 bytes using an nchar or nvarchar (4000). Notice here that I don’t add the N again, but if I do, I get to use a max only of 3874. WTF? I don’t see this in the specification in BOL do you? What is happening with the adding the N? The string is still unicode right? By definition it has to be since that’s the requirement for a string to sp_executesql. You can test this by simply making it a varchar – it won’t run! Wait – didn’t we already know that it was an nvarchar type as defined by @SQLString? Note also that the N appears to be limiting me by and even 4000 bytes (7847). Good news nonetheless as we now have more room to work with.
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(max);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);
SET @IntVariable = 1;
SET @SQLString = REPLICATE(' ', 7874) + 'select @max_titleOUT = max(JobTitle)
FROM AdventureWorks2008.HumanResources.Employee
WHERE Organizationlevel = @level';
SET @ParmDefinition = N'@level smallint, @max_titleOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;
Example 3: SQL 2008 using 64 bit
So now I want to test the 64 bit blurb, “On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).” So I move over to my Hyper-V box, 64 bit, with SQL Server 2008 dev edition. I normally run it with 2GB RAM, so just to make certain I shut off my other VM and up it to 4GB RAM. So, based on the definition here, you would think that I could get a string even larger than 7874 bytes right (which we know is really that x 2 since it’s unicode). Uh, wrong. I get a lousy 9 bytes more! WTH??? Additionally, if I add the N to denote unicode I get a max of 3883, that same lousy 9 bytes for moving to 64 bit and adding 2GB RAM. Is 14k bytes = 2GB? Seems to be here.
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(max);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);
SET @IntVariable = 1;
SET @SQLString = REPLICATE(' ', 7883) + 'select @max_titleOUT = max(JobTitle)
FROM my_deebee.HumanResources.Employee
WHERE Organizationlevel = @level';
SET @ParmDefinition = N'@level smallint, @max_titleOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;
Conclusion
None! I have raised questions that I cannot answer: 1) What is the N really doing? It appears that it is not necessary to use this designator in order to tell the SQL engine that a unicode string. Matter of fact, adding it in SQL 2005/08 actually shortens the usable string length, 2) What is the maximum length of the string for any version? We saw that 2000-2008 have varying results, and 3) what’s this garbage about the max length of the string is 2GB, or nvarchar (max)? Clearly from my example, I get a string of 15,748 bytes, which is way, way less than the maximum that they tell me that I have. Moreover, a 64 bit machine did running Microsoft SQL Server Developer Edition (64-bit) had little bearing on additional bytes that could be added to the string, and 4) Where are my missing bytes? I was told I’d get 4000 but get neither in either SQL 2000 or SQL Server 2008.
Lee
--------------------------------------
Apparently I need one of those “MVPs” to explain this to me
