Forum Discussion

tuspatil's avatar
tuspatil
Copper Contributor
Oct 08, 2025
Solved

Why is SQL Server only storing 4000 characters in an NVARCHAR(MAX) column?

Hi Guys,   I'm trying to insert a string with 10,000 plain characters (just repeated 'A's) into a column defined as NVARCHAR(MAX) in SQL Server. But LEN(Content) always returns 4000, not 10,000. I...
large database used size
nvarchar
sql
sql server
  • Viorel's avatar
    Viorel
    Oct 08, 2025

    Try to cast the N'A'. The next example shows the differences:

    declare @LongText1 nvarchar(max)
declare @LongText2 nvarchar(max)

set @LongText1 = replicate(N'A', 10000)
set @LongText2 = replicate(cast(N'A' as nvarchar(max)), 10000)

select len(@LongText1), len(@LongText2) -- returns 4000 and 10000

     

Djones16's avatar
Djones16
Copper Contributor
Oct 28, 2025

This issue isn’t with the table or column itself — it’s caused by how SQL Server Management Studio (SSMS) or T-SQL variables handle string literals and display results. When you assign a long string using SET @LongText = REPLICATE(N'A', 10000), it works fine, but if you use a literal string longer than 4000 characters (for NVARCHAR), SQL Server silently truncates it because T-SQL string literals are limited to 4000 characters for NVARCHAR (or 8000 for VARCHAR). To confirm, try this instead:

DECLARE @LongText NVARCHAR(MAX);
SELECT @LongText = REPLICATE(N'A', 10000);
INSERT INTO LargeTextExample (Content) VALUES (@LongText);
SELECT LEN(Content) FROM LargeTextExample;

If you still see 4000, the issue may be in SSMS grid output truncation (SSMS limits displayed text). In that case, check Tools → Options → Query Results → SQL Server → Results to Grid → Maximum Characters Retrieved, or use PRINT LEN(Content) or Results to Text instead. In short, SQL Server stores the full 10,000 characters — the apparent truncation comes from SSMS display or T-SQL literal limits, not the database itself.

Resources