'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, because 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?

sql-servertexttypes


Solution 1:[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,...).

Solution 2:[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).

Solution 3:[3]

You should have a look at

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.

Solution 4:[4]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Koen
Solution 2 Jon Seigel
Solution 3
Solution 4 Saurav Shah

Related Questions