asked
UPDATE under the hood
I have seen conflicting claims made at various places on the internet. Can anyone provide an authoritative answer to whether an UPDATE statement (in SQL Server 2008) updates in place, conducts a DELETE followed by an INSERT, or may do either depending on the circumstances (which begs the next question of how SQL Server makes this choice)? I know that an UPDATE trigger has inserted and deleted tables and that the transaction log has a before and after image of the row. I don't think either of these conclusively answers the question about how an update is hendled by SQL Server, though.
As much as I respect the regular participants on this forum and very much bow to their better knowledge of SQL Server than mine I feel compelled to say: you do realise that this is another of those 'places on the internet' and the info here is still provided "as is" with no assurances etc etc etc. . . !
@Gandalf. Absolutely. Between you and me I would trust my servers with Matt any time. I was just pointing out that you didn't trust other places on the web but came to this site for an answer!! Hope you hang around, that was a good question.
@Gandalf68 - yes, that was a good question :) +1 (not sure why I didn't +1 it yesterday, actually).
It depends!! If you're updating part of the clustered index, then you will see a delete followed by an insert: Current LSN Operation Context Transaction ID 00001e21:0000287b:0003 LOP_BEGIN_XACT LCX_NULL 0000:000ce552 00001e21:0000287b:0004 LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:000ce552 00001e21:0000287b:0005 LOP_SET_BITS LCX_PFS 0000:00000000 00001e21:0000287b:0006 LOP_INSERT_ROWS LCX_CLUSTERED 0000:000ce552 00001e21:0000287b:0007 LOP_COMMIT_XACT LCX_NULL 0000:000ce552 Updates that don't change the clustered index key, and don't **significantly** alter the length of the row change the row in-place: Current LSN Operation Context Transaction ID 00001e21:0000287d:0002 LOP_SET_BITS LCX_PFS 0000:00000000 00001e21:0000287d:0003 LOP_BEGIN_XACT LCX_NULL 0000:000ce553 00001e21:0000287d:0004 LOP_MODIFY_ROW LCX_CLUSTERED 0000:000ce553 00001e21:0000287d:0005 LOP_MODIFY_ROW LCX_CLUSTERED 0000:000ce553 00001e21:0000287d:0006 LOP_MODIFY_ROW LCX_CLUSTERED 0000:000ce553 00001e21:0000287d:0007 LOP_COMMIT_XACT LCX_NULL 0000:000ce553 Operations which do significantly increase the length of a row will result in various operations, involving page formatting, row overflow, etc etc.
I don't think that a conclusive answer is possible. Presence of the inserted and deleted logical tables suggests that SQL Sever may opt to delete the record and insert a new one with values from inserted table, but it does not have to, because as always, **it depends**. If the update statement does not affect the location of other records then it will insert a new record exactly in the original location, otherwise, it will not (like for example is the update causes the page to split). Split page can happen, for example, when you have a wide column, but the values in the existing records are rather narrow meaning that quite few records are packed into a single page. Updating a record with wide value will cause the record to move, so the page split will take place physically moving the updated record to a new location. However, if the update is possible "in place" then it will take place. For example, when updating only fixed column width columns, such as integers, decimals, chars, there is no need to move anything anywhere as the updated record will remain just as wide as it was before and therefore, there is no need to move it anywhere. Oleg
What do you understand the difference to be between updating in place and doing an insert/delete? Logically speaking it is the end result that matters and that end result is presumably the same in each case. So it may depend on just how you define the terms you are using. Better in my view to forget about those over-simplistic terms and just understand how SQL Server actually does work. Read a good book like the Inside SQL Server series.
