CODEWITHSUNDEEP
sql-servertexttypes
craigmj
craigmj

Reputation: 788

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?

Upvotes: 20

Views: 71220

Answers (5)

Saurav Shah
Saurav Shah

Reputation: 11

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;

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 134961

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

alt text

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

Upvotes: 21

Adriaan Stander
Adriaan Stander

Reputation: 166376

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.

Upvotes: 0

Jon Seigel
Jon Seigel

Reputation: 12401

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

Upvotes: 0

Koen
Koen

Reputation: 3686

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

Upvotes: 0

Related Questions