Use a Table as a Message Queue

Featured Article(s)
Installing and Configuring Reporting Services (Part 1 of 3)
In this session, readers will learn the basics of installing Microsoft SQL Server Reporting Services. This session will cover common installation situations and will discuss some of the more common pitfalls that surround the installation. This session will also cover the basics of Reporting Services configuration options, including basic database configuration and security implications.

Featured Script
Handling Divide by zero in SQL Reporting Services
Simple function to handle divide by zero gracefully. Simpler than using the IIF() function, which is not well suited to the t… (read more)

Use a Table as a Message Queue
Here is a tip for the day. You can have SQL Server participate in a Microsoft Message Queue through the Broker Service. But that has a lot of overhead if you are only using and processing data in SQL Server itself.

Instead, you can create a table for queuing. Simply insert records into a self designed table with the information needed for the process you wish to enable.

I use the Delete statement to process the queue table with a couple extra phrases allowing me to extract rows from the queue table without multi-process contention, and return to me rows that have been removed from the queue to a only a single process.

Let’s say I have a queue table like the following that was used for the shipping department to get the next customer order to process:

CREATE TABLE CustomerOrderToShip (
QDateTime DATETIME NOT NULL
,CustomerID INT NOT NULL
,OrderID INT NOT NULL)

CREATE CLUSTERED INDEX idxCustomerOrderToShip ON CustomerOrderToShip (QDateTime)

Now I can process the orders with using "first in first out" by getting customer orders in a couple simple queries.

DECLARE @Order TABLE (
CustomerID INT NOT NULL
,OrderID INT NOT NULL)

This Delete statement does three things:

• First, it bypasses any rows in the queue table that have a lock from any other process "WITH (READPAST)". This resolves multi-threading contention.
• Second, it can be used to get as many rows as you wish to process in a batch by changing the 1 in TOP (1) to a variable or a larger fixed number.
• Third, the rows are deleted from the queue table so they will not be picked up by another process, and the contents inserted into the temp memory table @Order.

The table @Order now contains the rows that were deleted from CustomerOrderToShip. Selecting the rows from @Order provides you with a fresh batch of records to process. If you want it to be a "Last In First Out queue", simply create your index in descending order on QDateTime, or use a query that orders the rows to be deleted in a descending order. If you really don’t care what order the rows are processed, don’t create any index at all.

So, there’s your queue. You handled multi-threading issues, and provide your database with a mechanism to handle processing one or more tasks in a sequential order.

Do you like these kinds of tips in the newsletter once in a while? If so, drop me a note with feedback regarding what is helpful to you, or any other suggestions you may wish to provide for future content. Send your comments to btaylor@sswug.org.

Cheers,

Ben