SQL Server Text Datatype Maxlength = 65,535?

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 SQL Server, bec开发者_JAVA百科ause if I add a 65,536th character to the column directly in Management Studio, it states that it will not update because characters will be truncated.

Is the max length really 65,535 or could this be because the database was designed in an earlier version of 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?

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

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,...).

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).

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.

Here's a little script I wrote for getting out all data

    SELECT @data = N'huge data';

    DECLARE @readSentence NVARCHAR (MAX) = N'';


    DECLARE @dataLength INT = ( SELECT LEN (@data));


    DECLARE @currIndex INT = 0;

    WHILE @data <> @readSentence
        BEGIN

            DECLARE @temp NVARCHAR (MAX) = N'';


            SET @temp = ( SELECT SUBSTRING (@data, @currIndex, 65535));


            SELECT @temp;

            SET @readSentence += @temp;


            SET @currIndex += 65535;


        END;