Systems Engineering and RDBMS

Length of a ntext data type or nvarchar(max) data type column

Posted by decipherinfosys on May 14, 2009

We have discussed before about the differences between the functions: LEN() and DataLength() in SQL Server – you can access that post over here.  Got a question from a reader yesterday about calculating the length of the data in columns defined with data types of ntext, nvarchar(max) and sql_variant.  We have covered sql_variant before and you can read more on that here.

Now, let’s see how we can compute the lengths of the data for columns of data type: ntext and nvarchar(max) – let’s create a table first:CREATE TABLE dbo.DEC_TEST
(
COL1 NTEXT
,COL2 NVARCHAR(MAX)
);

INSERT INTO dbo.DEC_TEST (COL1, COL2)
VALUES (REPLICATE (‘X’, 10000), REPLICATE (‘X’, 10000))

Now, if you try to use the LEN() function on COL1, it will fail with this error:

Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of len function.

And if you try to use it on COL2, you will get a value of 8000 instead of 10000:

SELECT LEN(COL2) FROM dbo.DEC_TEST

——————–
8000

And if you use the DATALENGTH() function, you will get a value of 16000 for each one of them:

SELECT DATALENGTH(COL1), DATALENGTH(COL2) FROM dbo.DEC_TEST

———– ——————–
16000       16000

And that is not surprising since datalength() function is used to give out the total number of bytes stored for a particular field.  However, it is being maxed out at 16000 (2 bytes per character since the maxed out length for the character is really 8000) – you can see that if you use LEN() function on COL2:

SELECT LEN(COL2) FROM dbo.DEC_TEST

——————–
8000

So, we still haven’t solved the problem in scenarios where the length of a string is more than 8000.  If it is less than or equal to 8000, Datalength() would work fine for both a ntext data-type column as well as a nvarchar(max) data type column.  So, how can we go about solving this issue and get the real length?

The answer becomes apparent if we insert another record into this table but this time around, we cast it to be of data type varchar(max).  If we do not do that, then the length restrictions of a varchar(n) column apply and those are limited to 8000 and hence the issue.

INSERT INTO dbo.DEC_TEST (COL1, COL2)
VALUES (REPLICATE (cast(‘X’ as nvarchar(max)), 10000), REPLICATE (cast(‘X’ as nvarchar(max)), 10000));

And now, let’s check the length again using the Datalength() function:

SELECT DATALENGTH(COL1), DATALENGTH(COL2) FROM dbo.DEC_TEST;

———– ——————–
16000       16000                     ==> Older Record showing incorrect 16000 bytes per value for both ntext and nvarchar(max) columns
20000       20000                    ==> Newer Record showing the correct number of bytes used

So, if the length of the string is what you are looking for, then just divide it by 2 if the data type is a unicode data type and if it is a non-unicode data-type, then you can just use it as is in order to get the length of the string in an ntext/text and nvarchar(max)/varchar(max) data type columns.

References:

  • Our post on the basics of LEN() and Datalength() – here.
  • BOL enteries for LEN() function – here and Datalength() function – here.

3 Responses to “Length of a ntext data type or nvarchar(max) data type column”

  1. Swapnil said

    Len() of a Varchar(max) variable on 64 bit SQL Server 2005 returns the number of characters without removing the trailing blanks. Can you try this at your end and let me know if this is a bug?

    • Swapnil,

      We were not able to reproduce it…

      INSERT INTO dbo.DEC_TEST (COL1, COL2)
      VALUES (REPLICATE (cast(‘X’ as nvarchar(max)), 10000), REPLICATE (cast(‘X’ as nvarchar(max)), 10000) + REPLICATE(‘ ‘, 100)) ;

      SELECT LEN(COL2), DATALENGTH(COL2) FROM dbo.DEC_TEST;

      ——————————
      10000 20200

      And it is this build: 9.00.3077 – 64 bit.

  2. Mark Emerson said

    I encountered the hidden 8000-character limit when working with nvarchar(max) and various functions. Your posting helped me to isolate and resolve the problem – I needed to recast the calculated values as nvarchar(max) in a couple of places. Thanks! Mark

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: