For this month’s T-SQL Tuesday, He of Perfect Hair, Bert Wagner asked, “What code have you written that you would hate to live without?”
There are a lot of pieces of code I frequently need to reuse when working on the First Responder Kit, like string splitting, XML PATH-ing, and converting milliseconds to some human consumable elements.
But deep down inside, I know that I couldn’t live without dynamic SQL, and dynamic SQL couldn’t live without the humble + operator.
Sure, 2012 brought us CONCAT, and much more recently, 2017 brought us CONCAT_WS, but I can’t really use those regularly.
Why? Because I have to wait until 2019 for SQL Server 2008 and SQL Server 2008R2 to be out of mainstream support before I can develop for 2012 onward, and it’s a Monkey Knife Fight getting you all to raise your compatibility level, never mind upgrade to a version that isn’t old enough to do basic math.
In that spirit, this code will only run on SQL Server 2016+ (GET IT?!)
Going Going Back Back
Plus (GET IT?!), for all of my early years with SQL Server, I had to write dynamic SQL the old fashioned way.
And boy did I write a lot of dynamic SQL.
This was often a source of pain and confusion. The errors are terribly unhelpful — Incorrect syntax near ''. — and displaying longer strings is a plain nuisance.
But I’m not going to harp on that stuff today, I’m going to harp on something much more fundamental: SQL is not a strongly typed language, no matter how hard you press the keyboard.
That means that even if you declare a string variable as a certain length (or MAX), it may not always stay that way.
For instance, you might expect this to return 8000, but it doesn’t.
|
1 2 3 4 5 6 7 |
DECLARE @sql NVARCHAR(MAX) = N''; DECLARE @something NVARCHAR(4000) = REPLICATE(N'.', 4000); DECLARE @about_stuff NVARCHAR(4000) = REPLICATE(N'_', 4000); SET @sql = @something + @about_stuff; SELECT LEN(@sql) AS [@sqlLength]; |
One way to get our expected result is to do this:
|
1 2 3 4 5 6 7 |
DECLARE @sql NVARCHAR(MAX) = N''; DECLARE @something NVARCHAR(4000) = REPLICATE(N'.', 4000); DECLARE @about_stuff NVARCHAR(4000) = REPLICATE(N'_', 4000); SET @sql = @sql + @something + @about_stuff; SELECT LEN(@sql) AS [@sqlLength]; |
Triviality
Of course, that’s a silly example, but it should help you understand a longer example.
The code is a simplified block from sp_BlitzCache, and it’s far too long to post here. But if you follow the link to that GitHub Gist, and run it in SSMS, you’ll get some funny results.
Going one step further, if you try to execute the first piece of dynamic SQL, you’ll get a Very Unhelpful Error Message®
|
1 2 |
Msg 4145, Level 15, State 1, Line 82 An expression of non-boolean type specified in a context where a condition is expected, near 'exe'. |
The reason that the second string is able to successfully concatenate beyond 4000 characters, and successfully execute a query, is this little break:
|
1 2 3 4 |
' + QUOTENAME(@SortOrder, '''') IF 1 = 1 BEGIN SET @ScalarFunctions += N' |
Without it, concatenating the second string gets into trouble.
|
1 2 |
' + QUOTENAME(@SortOrder, '''') + N' FROM (SELECT TOP 10 x.*, xpa.*, |
If we check how long each block is, we can figure out the problem:
Just like in the simpler example, the first concatenation is what broke things. We could have avoided it by writing our code like this:
|
1 |
SET @ScalarFunctions = @ScalarFunctions + N' |
To Recap:
- The first string has a length of 3875 characters
- It’s typed as NVARCHAR 4000
- The second string gets cut off at the difference
Long Strings
So, the next time you’re writing dynamic SQL that produces large output, don’t get caught off guard by thinking that just declaring the string as a MAX type will carry over. Other concatenations might reduce the typing to a lower number, and result in difficult to track down error messages.
If you have to do this kind of stuff all the time, check out Helper_LongPrint by Yildirim Kocdag.
5 Comments. Leave new
You can also solve the issue by declaring your intermediate variables as nvarchar(max) as well.
DECLARE @sql NVARCHAR(MAX) = N”;
DECLARE @something NVARCHAR(MAX) = REPLICATE(N’.’, 4000);
DECLARE @about_stuff NVARCHAR(MAX) = REPLICATE(N’_’, 4000);
SET @sql = @something + @about_stuff;
SELECT LEN(@sql) AS [@sqlLength];
I believe this explains it somewhat https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-2017
“When two nchar or nvarchar expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 4,000 characters, whichever is less.”
The article does not provide an explanation for when the expression is declared as nvarchar(max).
Kevin,
Yeah, but that’s not the way most people write dynamic SQL. The code at the GitHub link is a better representation.
Thanks!
[…] Darling posts one solution to this problem in his T-SQL Tuesday #104 entry (as well as some other problems/solutions for lengthy SQL variables). Specifically he links to a […]
[…] Darling posts one solution to this problem in his T-SQL Tuesday #104 entry (as well as some other problems/solutions for lengthy SQL variables). Specifically he links to a […]
I’m sure I would fall into that trap 🙁 but this approach seems to work:
SET @sql = CAST(@something AS NVARCHAR(MAX)) + CAST(@about_stuff AS NVARCHAR(MAX));
Perhaps it would have been better if Microsoft hadn’t called it NVarchar(MAX) and instead used BigNVarchar or somesuch such that the mix of datatypes was more obvious and DEVs more likely ?!! to use a CAST
Maybe there is a good reason for the naming inconsistency?
tinyint, smallint, int, bigint
smalldatetime, datetime, datetime2
varchar, varchar(MAX)
Maybe datetime2 was chosen to allow for unlimited series datetime3, … whereas bigint is more restrictive with megaint, ultraint, hyperint, etc. 🙂
Time to dust off Hungarian Notation maybe …