July 27, 2007 at 12:14 am
Suppose I have a employee table such as :
TblEmp(
id int,
date_joined datetime,
dept int,
emp_photo varbinary(max))
If I have a transaction like this:
begin trans
update TblEmp set dept = 100
if some_value = true
commit trans
else
rollback trans
My question is: As it is a transaction, the contents will be written into the transaction log file so that it can be committed or rollbacked. But will the varbinary column content also go into the transaction log? Suppose if the image is too big, in terms of megabytes. I really dont know how the transaction log will work in this case.
July 27, 2007 at 7:02 am
Hi Vinoo,
Short answer: No.
Long answer:
binary large objects, if they are not inlined, are stored on database pages that are different from the pages the referring row data is on. If you modify the row, only the differences are written to the transaction log, so if you do not modify the LOB, the transaction log will not contain it. The same applies to normal rows. For example, if you have a row with ten char(100) in it, and you modify only a single character in one of these char(100), only the single character difference will go into the transaction log (so probably less than 100 bytes will be used by this operation), and not the old and the new version of the 1000 bytes of data stored in the modified row.
Regards,
Andras
July 30, 2007 at 11:06 pm
Thanks a lot for the detailed explanation.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply