Nvarchar(max) shrinks to 4000 chars in SQL Server

Question

I am creating a dynamic query in stored procedure, and when I try to print that dynamic query, it only prints a part of the whole query.

My variable that holds the whole dynamic query is declared like below

DECLARE @SQL NVARCHAR(MAX)

When I print the variable's length like below, It gives the length of the whole query, which is good.

PRINT LEN(@SQL)

But, when I print the script itself, It prints only a part of the whole query.

PRINT @SQL

I also tried to print it like below

PRINT CONVERT(NVARCHAR(MAX),@SQL)

Why it only prints first 4000chars? What am I doing wrong?

Why not read the docs? msdn.microsoft.com/en-us/library/ms176047.aspx A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000). — Vladimir Baranov
– Vladimir Baranov, Commented May 26, 2016 at 12:45
Your data isn't being shrunk/truncated. SSMS has a limit to how many characters it will display for certain fields. Using another application to interpret the SELECT query results will yield the full value of the field. — Siyual
– Siyual, Commented May 26, 2016 at 12:48
Why it only prints first 4000chars? Because this is how PRINT works, which is written in the docs very clearly. — Vladimir Baranov
– Vladimir Baranov, Commented May 26, 2016 at 12:52

Randy Minder · Accepted Answer · 2016-05-26 12:47:28Z

You are not doing anything wrong. The Print command is limited to outputting 4000 characters (see BOL - Books Online, for more details). It does not mean nvarchar(max) has shrunk to 4000 characters.

Community · Accepted Answer · 2017-05-23 11:58:41Z

try as below to print entire string,

SET @Query = 'SELECT ....' [Up To 4,000 characters, then rest of statement as below] SET @Query = @Query + [rest of statement] Now run your query as normal i.e. EXEC ( @Query )

got the answer from below link

nvarchar(max) still being truncated

Paul Andrew · Accepted Answer · 2016-05-26 12:51:16Z

It's PRINT that is limited to 4000 characters, not the NVARCHAR(MAX) variable.

A workaround would be to just something like this PRINT BIG function with your string if you want to output everything.

https://www.richardswinbank.net/doku.php?id=tsql:print_big

You could also just SELECT the variable which isn't limited and copy the contents from the results.

Marco Merola · Accepted Answer · 2025-02-15 17:18:02Z

A message string can be up to 8,000 characters long if it's a non-Unicode string, and 4,000 characters long if it's a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/print-transact-sql?view=sql-server-ver16

However you could print your variable in chunks

PRINT SUBSTRING(@SQL, 1, 4000); PRINT SUBSTRING(@SQL, 4001, 8000); ...

Collectives™ on Stack Overflow

Nvarchar(max) shrinks to 4000 chars in SQL Server

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related