SQL Server NText field limited to 43,679 characters?

Question

I working with SQL Server data base in order to store very long Unicode string. The field is from type 'ntext', which theoretically should be limit to 2^30 Unicode characters.

From MSDN documentation:

ntext

Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered. The ISO synonym for ntext is national text.

I'm made this test:

Generate 50,000 characters string.
Run an Update SQL statement

UPDATE [table] SET Response='... 50,000 character string...' WHERE ID='593BCBC0-EC1E-4850-93B0-3A9A9EB83123'
Check the result - what actually stored in the field at the end.

The result was that the field [Response] contain only 43,679 characters. All the characters at the end of the string was thrown out.

Why this happens? How I can fix this?

If this is really the capacity limit of this data type (ntext), which another data type can store longer Unicode string?

You should use Nvarchar(max) instead, but NText fields can hold much larger strings. — Robert McKee, Nov 01 '15 at 08:23
Possible duplicate of [How do you view ALL text from an ntext or nvarchar(max) in SSMS?](http://stackoverflow.com/questions/11897950/how-do-you-view-all-text-from-an-ntext-or-nvarcharmax-in-ssms) — Vladimir Baranov, Nov 01 '15 at 10:26
Related: https://stackoverflow.com/questions/25709069/the-maximum-number-of-characters-a-textbox-can-display — Thomas Weller, Dec 26 '21 at 19:45
Related: https://stackoverflow.com/questions/14355427/is-43679-a-magic-number — Thomas Weller, Dec 26 '21 at 19:45

score 3 · Answer 1 · edited May 23 '17 at 12:02

3

NTEXT datatype is deprecated and you should use NVARCHAR(MAX).

I see two possible explanations:

Your ODBC driver you use to connect to database truncate parameter value when it is too long (try using SSMS)
You write you generate your input string. I suspect you generate CHAR(0) which is Null literal

If second is your case make sure you cannot generate \0 char.

EDIT:

I don't know how you check the length but keep in mind that LEN does not count trailing whitespaces

SELECT LEN('aa     ')        AS length          -- 2
      ,DATALENGTH('aa     ') AS datalength      -- 7

Last possible solution I see you do sth like:

SELECT 'aa                aaaa' 

-- result in SSMS `aa aaaa`: so when you count you lose all multiple whitespaces

Check query below if returns 100k:

SELECT DATALENGTH(ntext_column)

edited May 23 '17 at 12:02

Community

1
1

answered Nov 01 '15 at 08:32

Lukasz Szozda

162,964
23
234
275

1) I'm running the SQL on "Sql Management Studio". 2) No Char(0) in the string - for sure. – No1Lives4Ever Nov 01 '15 at 08:41
@No1Lives4Ever Try `SELECT DATALENGTH(ntext_column)` if it returns 100k everything is ok – Lukasz Szozda Nov 01 '15 at 08:54
1

That is probably the limit for the management studio. What happens when you run the query from an odbc/ado connection? – Luc Nov 01 '15 at 08:58
I changed the NText to nVarChar(Max), tested with Entity Framework (C#) - now it's working. – No1Lives4Ever Nov 02 '15 at 08:27

score 3 · Accepted Answer · answered Sep 01 '16 at 20:07

3

Based on what I've seen, you may just only be able to copy 43679 characters. It is storing all the characters, they're in the db(check this with Select Len(Reponse) From [table] Where... to verify this), and SSMS has problem copying more than when you go to look at the full data.

answered Sep 01 '16 at 20:07

iceman825

46
1

This was my problem solution. I was copying from the DBMS field, but I only got 43679 characters. Why? So I saved the ntext field from datatabase directly to the file and all characters were there. Thanks – Ali reza Soleimani Asl Jan 14 '21 at 07:50

Ali Osman Yavuz · Answer 3 · 2017-04-26T10:22:25.367

1

For all bytes; Grid result on right click and click save result to file.

edited Apr 26 '17 at 10:22

answered Apr 20 '17 at 08:03

Ali Osman Yavuz

389
3
4

score 0 · Answer 4 · answered Sep 03 '19 at 15:17

Can confirm. The actual limit is 43679. Had a problem with a subscription service for a week now. Every data looked good, but it still gave us an error that one of the fields have invalid values, even tho, it got correct values in. It turned out that the parameters was stored in NText and it maxed out at 43679 characters. And because we cannot change the database design, we had to make 2 different subscriptions for the same thing and put half of the entities to the other one.

SQL Server NText field limited to 43,679 characters?

4 Answers4

Linked