Some people will never learn anything, for this reason, because they understand everything too soon.

Alexander Pope

Recently I was working on adding support for sending notifications to mobile devices and came across a cool feature in SQL Server that I didn't know about. I won't go into the details, but what I doing was basically a producer-consumer problem but distributed across multiple machines. When I started, I knew exactly what to do; when I finished, I had learned something new.

First Attempt

Starting out, I already knew exactly what I needed to do and how to do it. The SQL was very straight forward.

UPDATE dbo.NotificationQueue
SET IsActive = 0,
    LockedUntilUtc = DATEADD(SECOND, 10, SYSUTCDATETIME()),
    LockedBy = @MachineName
WHERE Id IN (
    SELECT Id
    FROM dbo.NotificationQueue q
    WHERE q.IsActive = 1
    ORDER BY q.CreatedOnUtc 
        OFFSET 0 ROWS 
        FETCH FIRST @BatchSize ROWS ONLY
);

SELECT Id, TargetApplication, SystemType, IsDevelopment, AlertMessage, Payload, Token
FROM dbo.NotificationQueue
WHERE LockedBy = @MachineName;

There are problems with this approach though.

  • It requires multiple operations (one to find the items to update, one to update, and one to select the items that are now locked)
  • You need multiple indexes (one for looking up the items that are active, one to look up who has locked the notification)

Refactoring

I don't remember exactly how I ended up on the documentation for UPDATE statements exactly, but I noticed something I haven't ever payed attention to before. Towards the bottom of the page was the section "Capturing the Results of the UPDATE Statement".

How this works is very similar to how triggers work: you get fake tables called inserted and deleted which hold the values that are modified by the query. The inserted table holds the new values for the records. The deleted table holds the previous values for the records.

So, this allows us to remove our second query we had above that was just retrieving the information about our notification. I also ended up using another trick that I had run across previously which is that you can update using a Common Table Expression. This doesn't change the efficiency of the query, I just find it easier to read. 

WITH pending AS (
    SELECT *
    FROM dbo.NotificationQueue q
    WHERE q.IsActive = 1
    ORDER BY q.CreatedOnUtc OFFSET 0 ROWS FETCH FIRST @BatchSize ROWS ONLY
)
UPDATE pending
SET IsActive = 0,
    LockedUntilUtc = DATEADD(SECOND, 10, SYSUTCDATETIME()),
    LockedBy = @MachineName
OUTPUT inserted.Id
    , inserted.TargetApplication, inserted.SystemType, inserted.IsDevelopment
    , inserted.AlertMessage, inserted.Payload, inserted.Token;

Now that I know about this, I can think of a lot of other situations where it will be extremely helpful. This output clause isn't just limited to UPDATE statements either; it's supported on INSERT and DELETE as well.

If you aren't using SQL Server, there may be an equivalent of this technique supported by your database. The RETURNING clause is the most common variant and it's supported by PostgreSQL and Oracle.