Simplifying Archival Processes In SQL Server Using OUTPUT

Long List Of Demands


Many suggestions for making changes or improvements to a process in a database are met like orders that mean, if not certain, then likely death.

EXT. A WEBEX MEETING

… Erik is smiling, happy to be wrapping up and moving on. Client DBAs and Developers are sweating profusely, mechanical keyboards all heavily armed with Cherry MX Blue switches in an attempt to drown out the part where he talks about NOLOCK hints. For some reason everyone is smoking cigarettes and finalizing their wills

ERIK

You should enable the remote DAC, it’ll be helpful next time you hit THREADPOOL.

There’s a script included to turn it on, and a bunch of examples on how to use it.

IT Manager

By God, there’s no telling who of us will survive this, but we’ll see’er through.

Oh, Now You Need Consistency


Well, friends, I have good news for you. This is an easy one to implement.

Let’s say that in Stack Overflow land, when a user deletes their account we also delete all their votes. That’s not how it works, but it’s how I’m going to show you how to condense what can normally be a difficult process to isolate into a single operation.

First, we need an archive table. So let it be done.

CREATE TABLE dbo.Votes_Archive
(
    Id int NOT NULL,
    PostId int NOT NULL,
    UserId int NULL,
    BountyAmount int NULL,
    VoteTypeId int NOT NULL,
    CreationDate datetime NOT NULL,
    CONSTRAINT PK_VotesArchive_Id
        PRIMARY KEY CLUSTERED (Id ASC)
);

Next, we need a query to, like, do things. I guess.

Normally, you’d insert the rows you want to keep into the archive table, and then remove them from the source table by using a delete … where exists  type query. Usually in a transaction, because you heard you need them.

Savvier practitioners might even use the output clause with their delete to dump things into a #temp table.

But the savviest practitioners will do it all in one go.

Sauvignon Blanc


What a lot of people miss about output is that the results are tabular, like many other things in a database.

Just like you can select from deleted and inserted virtual tables in a trigger, you can select from them in a… select statement.

I’m using a transaction here because I want to roll it back.

BEGIN TRAN

INSERT dbo.Votes_Archive
    (Id, PostId, UserId, BountyAmount, VoteTypeId, CreationDate)
SELECT 
    v.*
FROM 
(
    DELETE v
        OUTPUT
            Deleted.Id,
            Deleted.PostId,
            Deleted.UserId,
            Deleted.BountyAmount,
            Deleted.VoteTypeId,
            Deleted.CreationDate
    FROM dbo.Votes AS v
    WHERE v.UserId = 190597
) AS v;

--Validate stuff
SELECT * FROM dbo.Votes AS v WHERE v.UserId = 190597;

SELECT * FROM dbo.Votes_Archive AS vi;
--Stop validating stuff, nutso
ROLLBACK

So when whoever user 190597 is deletes their account, all their votes are inserted into the archive table at the same time they’re deleted.

The execution plan looks like this:

SQL Server Query Plan
polar seltzer

Which is pretty nifty, and eliminates the need for a lot of jumping around.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



13 thoughts on “Simplifying Archival Processes In SQL Server Using OUTPUT

  1. Assuming both tables have same structure,

    DELETE FROM dbo.Votes
    OUTPUT deleted.* INTO dbo.Votes_Archive
    WHERE v.UserId = 190597;

  2. Great post, I am in the middle of an archiving project and now looking at reengineering to use this instead.
    Cheers mate

  3. I’m currently in the process of changing some of our tables to use a sequence to generate ID values instead of identity and I was hoping to use this pattern to get the inserted value without having to resort to any sort of temp table (either first class or table variable). I was disappointed with the resulting error message – “A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.”. FWIW, I tried these two methods:

    SET @FooID = (
    SELECT id
    FROM (
    INSERT INTO dbo.foo
    OUTPUT Inserted.id
    DEFAULT VALUES
    ) AS i
    )

    SELECT @FooID = id
    FROM (
    INSERT INTO dbo.foo
    OUTPUT Inserted.id
    DEFAULT VALUES
    ) AS i

Comments are closed.