The Length Limitations of LEN()?

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.

Great idea!

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:

Just to prove that it printed in its entirety!

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!

Printing the output when I pass 7997 as the number to REPLICATE(). Notice the exclamation point is now gone.

Even stranger – the data truncates at different points, though it shows the same number of characters!

Output when I pass 9000 to the REPLICATE() function – and a different cutoff point. No “en!” at the end now.

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….

The Length Limitations of LEN()?

Rate

Share

Share

Rate