SQL Server Misconception – NVARCHAR(MAX) Cannot Store More Than 4000 Characters

Posted on : 07-06-2012 | By : Devi Prasad | In : SQL Server Misconceptions

0



In this post Lets discuss about the SQL Server Misconception – NVARCHAR(MAX) Cannot Store More Than 4000 Characters.

Lets analyse this first
Take the below query.

DECLARE @String NVARCHAR(MAX)
DECLARE @i INT
SELECT @i = 5000,@String=''

WHILE @i>0
BEGIN
SELECT @String = @String + 'A'
SET @i = @i - 1
END

PRINT @String

When you run this query you will get the following output.
Output:

Print Output

Find the length of the output:
When you move the cursor to the end of the output in the Messages window you can notice that the column number (as shown in above image) to be 4001(So length is 4000 characters).

This leads to the misconception
– NVARCHAR(MAX) Cannot Store More Than 8000 Characters

Actuality:
– Print statement has a limit of 4000 characters (unicode) hence the output is truncated.
– The nvarchar(max) statement can store up to 2gb of data(2^31-1 bytes).

Take the below SQL Code

DECLARE @String NVARCHAR(MAX)
DECLARE @i INT
SELECT @i = 5000,@String=''

WHILE @i>0
BEGIN
SELECT @String = @String + 'A'
SET @i = @i - 1
END

SELECT LEN (@String) AS Length

Output:
Length
——————–
5000

(1 row(s) affected)

LEN statement returns 5000 and it proves that nvarchar(max) can store more than 4000 characters.




Reference : Devi Prasad (sqlserverlearner.com)

Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.



Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.




Your Ad Here


Tags: , ,