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.

createdatebase

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.

firstupdate

Ok, now the result of the DBCC LOG

updateCol2

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.

nonclusteredindexupdate

nonclusteredindexupdateresult

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?

clusteredindex

I dropped the nonclustered index and created a clustered and result is:

clusteredindexresult

Again, only the modified row in the index clustered. And, if we try to update our key in the clustered index?

clusteredindexkey

clusteredindexkeyresult

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?

noupdate

Nothing, no change at all.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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