Forum Discussion
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’ve verified that the column is NVARCHAR(MAX) and used the N prefix for Unicode. Still, the data seems to be truncated.
What could be causing this? Is there something I'm missing in how SQL Server handles large strings?
Tried this:
CREATE TABLE LargeTextExample ( Id INT PRIMARY KEY IDENTITY(1,1), Content NVARCHAR(MAX) );
DECLARE @LongText NVARCHAR(MAX);
SET @LongText = REPLICATE(N'A', 10000);
INSERT INTO LargeTextExample (Content) VALUES (@LongText);
SELECT LEN(Content) AS CharacterCount FROM LargeTextExample;
Thanks,
Tushar
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
2 Replies
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.
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