Updating a column to itself in TSQL

Interesting finding, I reckon.

I see some posts on the internet about the cost of updating a column to itself; you might find a statement (or maybe you have used one) that looks like so:

UPDATE TABLE
SET id = ISNULL(@var,id)

Essentially, if the variable is NULL, you update the column to itself. (Real tricky, I know right?).  Someone asked me the other day what exactly happens here, and I told them that I wasn't quite sure to be honest.  Although I've heard of folks doing experiments on and claiming this or that, my best bet is to always test it myself regardless; glad I tested this myself as well. Let's check this out and see what's up.

To clarify, the person's exact question was whether or not the transaction log grows during an update such as this.  This is really a good place to start for this action, because if the column updates itself, the log should grow in theory - a DML statement in Transact-SQL (save SELECT) will write to the transaction log by definition.  I first run this to create my table, and then run sp_helpfile:

DROP TABLE TLOG_TEST
GO
CREATE TABLE TLOG_TEST
    (Id INT IDENTITY (1,1) NOT NULL PRIMARY KEY
    ,CharValue char (8000) default ' '
    )
GO

Figure 1.  sp_helpfile before we've done anything

Now, let's run our statement to make the TLog go!  Run the following slick statement to insert a few records:

DECLARE @count int=0
START:
INSERT INTO TLOG_TEST DEFAULT VALUES
SET @count+=@@ROWCOUNT

IF (@count <= 50000)
    GOTO START

Now we run sp_helpfile again. 

Figure 2.  sp_helpfile after running the above statement.

Here we see that the datafile grows for sure, nothing yet for the tlog.  Now, run the following statement.

UPDATE TLOG_TEST
SET CharValue = CharValue

For the sake of not pasting the screenshot again (you'll have to trust me on this one) the log did not grow. Somewhat unexpected, but sort of makes sense, really. While a SQL Transaction shows up in Profiler, SQL Server really isn't doing much here.   Just for grins and giggles...try this one.

DECLARE @var char(1) = 'a'
UPDATE TLOG_TEST
SET CharValue = ISNULL(@var,'a')

Figure 3. sp_helpfile after updating the row to something else

Based on my test here, I don't see the log grow when you update a column to itself. To verify, Profiler revealed Reads, CPU, and duration, but no Writes;  I conclude here that the operation takes a toll on the server but that's about it. Test this for yourself under different conditions and see what you can come up with.  A follow-up might be to see what locks SQL Server actually takes out on the table.

Thanks for reading,

Lee

-----------------------

Sarah Connor: Kyle, the women in your time, what are they like?
Kyle Reese: Good fighters.