THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Did You Know … that not everything you know is actually true!

Software changes, new versions not only add new features, they also change internal behavior of old features,  and not all the changes are documented!  And how often, after a software upgrade, do you go through and test everything you knew was true, to make sure it is STILL true after the upgrade? I write books about the SQL Server software, and although I do verify all the code in my books against the new version, I admit that I don’t always check every single fact mentioned in the entire book to make sure it is still true.

There are a number of such changes that I’ve discovered over the last year or so, that I’ll be telling you about in a couple of blog posts. Plus, there are things that I read on other peoples’ blogs, where the author states something is true that I know is no longer true. Here’s an example:

It used to be true, a very long time ago, that you could not rollback a TRUNCATE TABLE operation. That changed so long ago, I can’t even remember what version it was in. But in all current versions, you can rollback a TRUNCATE TABLE, and it very easy to prove that. All you have to do is question whether or not the fact that “you cannot roll back a TRUNCATE TABLE” is actually true. But if you think it’s true, and never question it, you’ll never know.

Here’s the example I use in my classes to show that you CAN rollback a TRUNCATE TABLE:

USE testdb – or any test database of your choice
GO

IF object_id('smallrows') IS NOT NULL
    DROP TABLE smallrows;
GO

CREATE TABLE smallrows
(
    a int IDENTITY PRIMARY KEY,
    b char(10)
)
GO

INSERT INTO smallrows VALUES
        ('row 1'),
        ('row 2'),
        ('row 3'),
        ('row 4'),
        ('row 5');
GO
SELECT * FROM smallrows;
GO


-- TRUNCATE TABLE can be rolled back
BEGIN TRAN
GO
TRUNCATE TABLE smallrows;
GO
SELECT * FROM smallrows;
GO
ROLLBACK TRAN;
GO
SELECT * FROM smallrows;
GO

In my classes, right after I talk about DELETE and TRUNCATE operations, I talk about UPDATE. You might be aware that UPDATE can be performed in a couple of different ways, SQL Server can do something called an “update-in-place” where it just changes the old bytes to the new bytes, and logs one simple update operation. Or, the UPDATE can be performed as two separate operations: DELETE the entire old row (and update all the indexes and log the DELETE plus all the index changes) and then INSERT an entire new row (and update all the indexes and log the INSERT plus all the index changes.)  Obviously, update-in-place is preferred, but there are some conditions that must be met in order for SQL Server to perform an update-in-place. And one of the conditions I had on my list for years turned out not to be true any more when I tested it several months ago.

The main reason that SQL Server might not do an update-in-place is because you are updating the clustered key value. And since the clustered key value determines where the row goes in the table, changing that value will change the row location. It cannot be done in-place (even when the row doesn’t actually have to move, as you’ll see in the example.) Another reason for not doing an update-in-place used to be because the table had an UPDATE TRIGGER. Prior to SQL Server 2005, the contents of the special ‘inserted’ and ‘deleted’ tables that were available inside a trigger were generated from the transaction log. So in order for these rows to be available, the entire new row and old row had to be logged. But in SQL Server 2005, with the introduction of the version store, mainly used to support row version for snapshot isolation, SQL Server started using the version store technology to get the old and new versions of updated rows if there was a trigger that needed them.

So did having an update trigger still preclude update-in-place?

I decided to test it.

This first block creates a table with no clustered index and no trigger.


-- no clustered index, no trigger
IF object_id('test1') IS NOT NULL DROP TABLE test1;
GO
CREATE TABLE test1
(a int,
  b char(200) );
GO
INSERT INTO test1 values (1, replicate('a',200));
GO
SELECT * FROM fn_dblog(null, null)
WHERE AllocUnitName like '%test1%';
GO

You should see 5 rows in the log, with the last one being an operation LOP_INSERT_ROWS, which indicated the single row inserted.

Now update the row:


UPDATE test1 SET a=2;
GO
SELECT [Current LSN], Operation, Context, [Log Record Length] as Length, AllocUnitName
FROM fn_dblog(null, null)
WHERE AllocUnitName like '%test1%';
GO

You’ll see something like this:

image

There is one additional row, with operation LOP_MODIFY_ROW, for the update-in-place. Notice the length of that log record is 100 bytes.

In the INSERT log record, the length is 304 bytes.

Now run the same test on a table with a clustered index on column a.


--  clustered index, no trigger
IF object_id('test2') IS NOT NULL DROP TABLE test2;
GO
CREATE TABLE test2
(a int primary key clustered,
  b char(200) );
GO
INSERT INTO test2 values (1, replicate('a',200));
GO
SELECT * FROM fn_dblog(null, null)
WHERE AllocUnitName like '%test2%';
GO
UPDATE test2 SET a=2;
GO
SELECT [Current LSN], Operation, Context, [Log Record Length] as Length, AllocUnitName
FROM fn_dblog(null, null)
WHERE AllocUnitName like '%test2%';
GO

This time, after the UPDATE,  you’ll see these log records:

image

Notice there is no LOP_MODIFY_ROW for the clustered index. There is a LOP_DELETE_ROWS and LOP_INSERT_ROW and their lengths are much longer than the length of the LOP_MODIFY_ROW. The entire old row and entire new row are being logged. This may not seem like a big deal, but imagine instead of one row, that you are updating thousands or even millions of rows. Also note that there is just the single row in the table, so the row doesn’t have to move when updated. There’s only one place for it to be! But SQL Server only knows that the UPDATE is changing a clustered key value, so it is performed as the two step operation.

Finally, I’ll do the test one more time, on a table with no clustered index, but with an UDPATE trigger.


-- no clustered index, update trigger
IF object_id('test3') IS NOT NULL DROP TABLE test3;
GO
CREATE TABLE test3
(a int,
  b char(200) );
GO
CREATE TRIGGER upd_test3 on test3 for UPDATE
AS
  SELECT * FROM inserted;
  SELECT * FROM deleted;
RETURN
GO
INSERT INTO test3 values (1, replicate('a',200));
GO
SELECT * FROM fn_dblog(null, null)
WHERE AllocUnitName like '%test3%';
GO
UPDATE test3 SET a=2;
GO
SELECT [Current LSN], Operation, Context, [Log Record Length] as Length, AllocUnitName
FROM fn_dblog(null, null)
WHERE AllocUnitName like '%test3%';
GO

Again, you’ll see only the one LOP_MODIFY_ROW, even though both the inserted and deleted rows were accessed in the trigger.

image

So it was obvious that I had to update my course material.

In my class two weeks ago, I found out another old ‘fact’ that is no longer true, but that will have to wait until next time. And of course, a whole new version is coming out in just a few months. I wonder what old facts will no longer be facts?

Have fun!

~Kalen

p.s. Right after I published this, I noticed I had already written up the update-in-place with triggers, just about a year ago. So if you missed it then, you know about it now!

Published Wednesday, November 27, 2013 3:36 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Golam Kabir said:

If you don't use Begin tran, rollback is not possible

December 6, 2013 3:23 PM
 

Kalen Delaney said:

Hi Golam

Of course that is true, but it is true for any statement, not just TRUNCATE TABLE.

Thanks

Kalen

December 6, 2013 3:48 PM
 

Chris Shaw said:

Hi Kalen,

Question is if you update key columns for non clustered index does it still do delete + insert?

December 15, 2013 9:16 AM
 

Kalen Delaney said:

That's a good question Chris! Did you figure out the answer yet?

December 17, 2013 6:38 PM
 

Chris Shaw said:

Yes, just tweak your demo and it appears to me that if you update key columns of NC index it will do DELETE + INSERT.

December 18, 2013 10:13 AM
 

Andreas Wolter said:

If you are using Implicit Transactions, you can indeed ROLLBACK the TRUNCATE without a BEGIN TRANSACTION

:-D

March 11, 2014 2:51 PM
 

Kalen Delaney said:

I like to think of Implicit Transactions as adding an implied BEGIN TRANSACTION to every command.  And like my response to Golam, that it true for any change, not just TRUNCATE TABLE.  But mostly, I don't like to think about Implicit Transactions at all!

~Kalen

March 11, 2014 3:43 PM
 

Andreas Wolter said:

Absolutely.

I didn't really consider both of those statements too seriously ;-)

Andreas

March 11, 2014 3:51 PM
 

Kalen Delaney said:

But you never know who's going to be reading this!

Thanks

Kalen

March 11, 2014 4:02 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement