MS SQL Server Text Datatype Maxlength = 65,535?

Question

Software I'm working with uses a text field to store XML. From my searches online, the text datatype is supposed to hold 2^31 - 1 characters. Currently SQL Server is truncating the XML at 65,535 characters every time. I know this is caused by sqlserver, because if I add a 65,536th character to the field directly in Management Studio, it states that it will not update because characters will be truncated.

Is the Maxlength really 65,535 or could this be because the database was designed in an earlier version of MS SQL Server (2000) and it's using the legacy text datatype instead of 2005's?
If this is the case, will Altering the datatype to Text in sql server 2005 fix this issue?

Answer 1

MSSQL 2000 should allow up to 2^31 - 1 characters (non unicode) in a text field, which is over 2 billion. Don't know what's causing this limitation but you might wanna try using varchar(max) or nvarchar(max). These store as many characters but allow also the regular string T-SQL functions (like LEN, SUBSTRING, REPLACE, RTRIM,...).

Answer 2

If you're able to convert the column, you might as well, since the text data type will be removed in a future version of SQL Server. See here.

The recommendation is to use varchar(MAX) or nvarchar(MAX). In your case, you could also use the XML data type, but that may tie you to certain database engines (if that's a consideration).

Answer 3

You should have a look at

• XML Support in Microsoft SQL Server 2005
• Beginning SQL Server 2005 XML Programming

So I would rather try to use the data type appropriate for the use. Not make a datatype fit your use from a previous version.

Answer 4

that is a limitation of SSMS not of the text field, but you should use varchar(max) since 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 100000 for me