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:
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:
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.
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!