SQL Server: update on single record only updated half the varchar content

Question

update table1 
set comments = @comments 
where id = @id

In a web application, I had a single record update query for a varchar(MAX) column that only updated 40K characters of the 80K characters I was sending. So it chopped off the last 40K or so.
I don't see how a column can part-update like that. Does it stream large values? ie. if the connection broke half way, and only half was transferred across? But surely it would wait for the full command and parameters to load in a buffer before executing?
What if the table was busy with many users, and locks occurred and it was a victim of a deadlock? Once it started, I assume it would be all or nothing. But any possibility it could part-update the column?
Edit: it's definitely not in the database because the customer complained about the missing data, which I verified is in fact missing.
Edit: save would come via Javascript call to .NET web service to an EF save function. so could any of those truncate the data on a transmission/thread interruption type event?
Note this is a one off event. But severely disrupting to the customer. So I really need to audit a cause. Many times this has worked fine over a long period of time. I am assuming it's some infrequent occurrence due to thread or internet interrupt somewhere on the pipe.

Moe Sisko · Answer

I agree with David Browne.
You could try something like this in SSMS to "see" what is in the column easier:
select comments, '(' + comments  + ')' as comments2, CAST(comments as varbinary(max)) as CommentsAsBytes,
 LEN(comments) as CommentsLength, DATALENGTH(CAST(comments as varbinary(max))) as CommentsNumBytes
 from table1 where id = @id

What does it return? Do CommentsLength and CommentsNumBytes look right?

David Browne - Microsoft · Answer

but any possibility it could part-update the column?

No.  No possibility that that would happen other than a bug somewhere, perhaps in your controller code.
One possibility is that there's a 0x00 character in the middle.  SQL Server can handle that, but many, many client programs will refuse to display anything after a 0x00 code point, thinking it's a null-terminated string.
EG
create table #x(data varchar(max))

insert into #x(data) values (cast(0x414141410041414141 as varchar(max)))

select data, DATALENGTH(data) 
from #x

will display in the SSMS data grid output like this:

but in text output like this:
data     
---------
AAAA AAAA

SQL Server: update on single record only updated half the varchar content

2 Answers

Add your own answers!

Ask a Question