How does SQl UPdate
-
I've been mucking around with triggers and the pseudotables inserted and deleted. These 'temporary tables' are created when an update, insert or deletion occurs and can be used in conjuction with triggers to show what changes have been made to the table.
My question is when an update of a row is performed it appears that the whole row is deleted and then reinserted with the changed value. is this correct? does SQL delete the whole row and rewrite with the change or does it just change the column that we want to change. I'm interested with respect to large tables that have lots of little updates.
Thoughts over a cup of tea please?
-
I'm pretty sure the fact that the pseudotables are called deleted and inserted is just a fiction, they are really preimage and postimage.
Physically, an updated row will be replaced within a data page if there is room. Do we care ? If you deleted a row then inserted the same clustered index key, it could be added to the page in memory. On the other hand, altering the value of a clustered index field would force the row to physically move.
-
Interesting stuff Clay_G.
My interest was in very big tables. If the row is particularly big then would an update be quiet a big deal as apposed to if its a single field.
Don't really care; just wondering
-
As for the part about the whole row changing, the underlying engine knows which were expressly changed via the set, so only those could affect any index changes, the others would be ignored.
And just a note, if you look at the transaction log and learn to read it you will see only the range of the change is actually recorded. So say you have a column witfirst name in it and one row has a value of
Jabes
but you update to
James
The transaction log will record the change is
b at offset (n) within the record.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply