SQL Server Text Data Type Maxlength = 65,535?

Question

The software I'm working with uses a text box to store XML. From my searches on the Internet, the data type should contain 2 ^ 31 - 1 characters. Currently, SQL Server truncates XML at 65,535 characters each time. I know that this is caused by SQL Server because if I add the 65536th character to the column directly in Management Studio, it will say that it will not be updated because the characters will be truncated.

Is the maximum length really 65.535, or could it be because the database was developed in an earlier version of SQL Server (2000) and uses the legacy text data type instead of 2005?

If so, will changing the data type to text in SQL Server 2005 fix this problem?

Answer 1

this is a limitation of SSMS not a text field, but you should use varchar (max) as the text is deprecated

Here is also a quick test

 create table TestLen (bla text) insert TestLen values (replicate(convert(varchar(max),'a'), 100000)) select datalength(bla) from TestLen 

Returns 100,000 for me

Answer 2

MSSQL 2000 must allow up to 2 ^ 31 - 1 characters (not unicode) in a text field that is more than 2 billion. I don't know what causes this limitation, but you can try using varchar (max) or nvarchar (max). They store as many characters as possible, but also allow you to use regular T-SQL string functions (e.g. LEN, SUBSTRING, REPLACE, RTRIM, ...).

Answer 3

If you can convert the column, you can also, since the text data type will be removed in a future version of SQL Server. See here .

Using varchar(MAX) or nvarchar(MAX) recommended. In your case, you can also use the XML data type, but this can bind you to certain database mechanisms (if you take this into account).

Answer 4

You should take a look at

• XML support in Microsoft SQL Server 2005
• Start SQL Server 2005 XML Programming

Therefore, I would prefer to use a data type suitable for use. Do not use a data type that is suitable for use with a previous version.