I was using LEN() to troubleshoot an issue I was having with a dynamically constructed string truncating while inserting into an NVARCHAR(MAX) column. Since I know that NVARCHAR(MAX) has a 2 GB limit (goodness only knows how many characters that is!), I couldn’t explain the truncation. A colleague suggested doing a test with another dynamically constructed string. Maybe then, I could find where the cutoff was occurring.
So, I came up with a plan. I created a large string using the REPLICATE function to save myself the trouble of testing strings of different lengths. I first checked the length of the output of the function, then inserted the output of that into a temp table, and from there, into a table. Why, you ask? Because it closely mirrors what I needed to troubleshoot when I was needing to dynamically construct a string, hold it in memory and then cursor through it and insert into a table. (Before you ask why in the world I was doing that – yes, it’s an icky process. Trust me; I wouldn’t do it if I didn’t have a good reason.)
But I digress.
After the inserts are done, I checked the length of the string passed to the temp table and compare it to the insert into the table to see if I could find where the truncation might occur. Here is the code:
USE AdventureWorks2012; CREATE TABLE dbo.LenTest (FinishedScript NVARCHAR(MAX)); GO TRUNCATE TABLE dbo.LenTest; DECLARE @FinScriptSQL NVARCHAR(MAX); DECLARE @StringName NVARCHAR(MAX) = ( SELECT 'W' + REPLICATE('h', 7996) + 'en!' ); DECLARE @FinScriptName NVARCHAR(MAX) = @StringName; SELECT LEN(@FinScriptName) AS InitialLengthFromFunction; DROP TABLE IF EXISTS #t; CREATE TABLE #t ( FinishedScript NVARCHAR(MAX) ); INSERT INTO #t ( FinishedScript ) SELECT @FinScriptName AS BeginningScriptLength; SET @FinScriptSQL = N'INSERT INTO [dbo].[LenTest] SELECT FinishedScript FROM #t;'; PRINT @FinScriptSQL; EXECUTE sp_executesql @FinScriptSQL; SELECT LEN(FinishedScript) AS TempTableScriptLength FROM #t; SELECT * FROM [dbo].[LenTest]; SELECT LEN(FinishedScript) AS FinScriptLengthInTable FROM [dbo].[LenTest];
Then I took the FinishedScript from the table and copied and pasted it to another window to ensure that it had inserted in its entirety. I commented out the CREATE TABLE after the first run, of course!
And it was great. I ran it over and over, increasing the number passed to the REPLICATE() function by 1000 each time. When I got to 8000, data truncation started, so I backtracked to find where the cutoff was. I found it when I passed 7996 to the REPLICATE function. That ran okay. Notice the character length:
And now, we come to the weird part. Any number over 7996 passed to the REPLICATE() function still returns 8000 characters when measured by the LEN() function!
Even stranger – the data truncates at different points, though it shows the same number of characters!
You might ask my version and maximum characters retrieved settings. I ran this on SQL Server 2016, and the max characters retrieved was wide open: 65535 for non XML data, and unlimited for XML data.
This is my message in a bottle. Has anyone else had experience with this?
Sounds like a job for SQLKiwi to me….