Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 982
craigmj Avatar asked Feb 03 '23 04:02

craigmj


1 Answers

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

like image 92
SQLMenace Avatar answered Feb 14 '23 02:02

SQLMenace