Update data in SQL Server is a simple task. Using the command update table set column = value where column = value. Don’t forget the where clause :).
But, what SQL Server does internally? Delete plus Insert? Modify? Well, the answer depends.
Let’s see examples how that works.
First, create a database and table for the test.
As you can see, I didn’t create an index so I will update a heap table. Before update the row, I will drop the log register and then update the row I want.
Ok, now the result of the DBCC LOG
As we can see, the result is a modified row in the log, what about using an index? Creating a nonclustered index in the column I’m updating.
The result is very different, now we can see a modified row in the heap and in the nonclustered index I got a delete/insert row. Very nice!
What if was a clustered index?
I dropped the nonclustered index and created a clustered and result is:
Again, only the modified row in the index clustered. And, if we try to update our key in the clustered index?
I got the result with delete/insert as I was expecting because I changed the key like the nonclustered index. Now, if I try to update the row with the same value, what SQL Server will do?
Nothing, no change at all.