I have a field to store some data, the field is declared as varchar(max)
. To my understanding this should be storing 2^31 - 1
characters but when I enter some content over 8000 chars it cuts the rest off.
I have verified that all the data is included in my update statement and the query looks fine everywhere else but when I select the data back out it has been cut off.
The data is truncated when I display it on my website and also when I use SSMS to select content from table
.
select DATALENGTH (content) from table
comes back as 8000.
I set the data using this: update table set content = 'my long content' where id = 1
. The content does have lots of HTML in but I can't see that causing issues. The only thing I can see that I am doing is replacing all "
with ''
as this is user entered content (can't remember why I did that now).
I did manage to get the content to enter correctly by removing all single quotes in the content so I think something odd is going on with my data rather than the database.
Should I be doing something special with the query to use a varchar(max)
field?
Using: SQL Server 2008 (10.50) 64 bit.
Best Answer
If you are building the string up via string concatenation make sure that you cast one of the string literals explicitly to
varchar(max)
if none of the component strings are > 8,000 characters anyway. Otherwise it will be treated as a non max datatype and the concatenation will be truncated at 8,000 bytes.And it would be best to make it the first string literal that is cast to
varchar(max)
to avoid truncation should the combined length exceed 8,000 before thevarchar(max)
literal is reached.