Adventures in TSQL: Using CTEs when performing UPDATE operations

  • Article

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]
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ActivityQueue]') AND type in (N'U'))
    DROP TABLE [dbo].[ActivityQueue];
GO
 
CREATE TABLE [dbo].[ActivityQueue]
(
    [ActivityId]         int        IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [ActivityType]       int        NOT NULL,
    [ActivityMessage]    xml        NOT NULL
);
GO
 
CREATE INDEX IDX_ActivityQueue_ActivityType
    ON [dbo].[ActivityQueue]
    (
        [ActivityType]    ASC
    );
 
DECLARE @idx int = 0;
 
WHILE (@idx < 100000)
BEGIN
    INSERT INTO [dbo].[ActivityQueue] ([ActivityType], [ActivityMessage])
    VALUES (@idx % 10, '<process>Just a test message</process>');
    
    SET @idx = @idx + 1;
END
GO
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)
    [ActivityId]
    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:

image

Followed by the UPDATE and SELECT operation:

image

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:

image

Written by Carl Nolan