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.
Click through for the plan, but be sure to read Pamela’s comment at the bottom as there’s a bit more to the story.