In a recent post I discussed using CTEs for managing ordered DELETE operations.

Adventures in TSQL: Using CTEs when performing DELETE operations

However CTEs are also useful for ordered UPDATE operations, as I will hopefully show in this post.

One of the limitations of UPDATE TOP() statement is that an ORDER BY cannot be specified. This means statements such as the following are not permitted:

UPDATE TOP (@processSize) [dbo].[ActivityQueue]
SET [ActivityType] = 2
OUTPUT deleted.[ActivityId], deleted.[ActivityType], deleted.[ActivityMessage]
WHERE [ActivityType] = 0
ORDER BY [ActivityId] ASC

However, once again, CTEs offer an easy solution.

Table Definition

Before getting into the guts of TSQL here is the definition for the TABLE that will be used during the discussion, and a script to populate the table.

USE [Development]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ActivityQueue]') AND type in (N'U'))
DROP TABLE [dbo].[ActivityQueue];

CREATE TABLE [dbo].[ActivityQueue]
[ActivityType] int NOT NULL,
[ActivityMessage] xml NOT NULL

CREATE INDEX IDX_ActivityQueue_ActivityType
ON [dbo].[ActivityQueue]
[ActivityType] ASC

DECLARE @idx int = 0;

WHILE (@idx < 100000)
INSERT INTO [dbo].[ActivityQueue] ([ActivityType], [ActivityMessage])
VALUES (@idx % 10, '<process>Just a test message</process>');

SET @idx = @idx + 1;

Updates with Ordering

The normal process when one needs to updates entries from a table in a defined order, such that they are output for processing, is to first select the required entities, placing them into a temporary table, and locking those entries. The temporary table is then used to perform the UPDATE operation by performing an INNER JOIN:

DECLARE @queue TABLE ([ActivityId] int PRIMARY KEY);
DECLARE @processSize int = 100;

INSERT INTO @queue ([ActivityId])
SELECT TOP(@processSize)
FROM [dbo].[ActivityQueue] WITH (UPDLOCK)
WHERE [ActivityType] = 0
ORDER BY [ActivityId] ASC;

UPDATE [dbo].[ActivityQueue]
SET [ActivityType] = 2
OUTPUT deleted.[ActivityId], deleted.[ActivityType], deleted.[ActivityMessage]
FROM [dbo].[ActivityQueue] AS AQ
INNER JOIN @queue AS QU ON QU.[ActivityId] = AQ.ActivityId;

The INSERT operation first gives us the following query plan:


Followed by the UPDATE and SELECT operation:


Once again using CTEs allows us to greatly simplify this process.

Update using CTEs

CTEs once again allow us, in a single statement, to define an ordered set and update this set with an OUTPUT specification.

WITH [QueueData] ([ActivityId], [ActivityType], [ActivityMessage])
AS (
SELECT TOP(@processSize) [ActivityId], [ActivityType], [ActivityMessage]
FROM [dbo].[ActivityQueue]
WHERE [ActivityType] = 0
ORDER BY [ActivityId] ASC
UPDATE [QueueData]
SET [ActivityType] = 2
OUTPUT deleted.[ActivityId], deleted.[ActivityType], deleted.[ActivityMessage];

If one looks at the resulting query plan you will see that once again it is greatly simplified:


Written by Carl Nolan