How Update works?

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s