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
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.
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.
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:
Which is pretty nifty, and eliminates the need for a lot of jumping around.
Thanks for reading!