SQL Server: How to design table like a queue

Question

Please guide me how can i use a table like a queue. i want then moment i will select or read a records then that records will not be able to select by another session.

i got a little direction from this link https://stackoverflow.com/questions/2177880/using-a-database-table-as-a-queue
https://dba.stackexchange.com/questions/20399/fifo-queue-table-for-multiple-workers-in-sql-server
https://stackoverflow.com/questions/11333527/what-sql-server-2005-2008-locking-approach-should-i-use-to-process-individual-ta/11333725

https://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition/940001#940001
https://www.codeproject.com/Articles/110931/Building-High-Performance-Queue-in-Database-for-st
https://www.sqlshack.com/using-the-sql-server-service-broker-for-asynchronous-processing/

DECLARE @NextID INTEGER

BEGIN TRANSACTION

-- Find the next queued item that is waiting to be processed
SELECT TOP 1 @NextID = ID
FROM MyQueueTable WITH (UPDLOCK, READPAST)
WHERE StateField = 0
ORDER BY ID ASC

-- if we've found one, mark it as being processed
IF @NextId IS NOT NULL
    UPDATE MyQueueTable SET Status = 1 WHERE ID = @NextId

COMMIT TRANSACTION

-- If we've got an item from the queue, return to whatever is going to process it
IF @NextId IS NOT NULL
    SELECT * FROM MyQueueTable WHERE ID = @NextID

looking for good example. thanks

Answer 1

Hi @Sudip Bhatt ,

You could also try with adding WAITFOR DELAY so that you could loop and check for new rows every several seconds as you defined.

Please refer another similar example from below or here.

-- Create table  
CREATE TABLE [dbo].[work_queue](  
 [work_queue_id] [int] IDENTITY(1,1) NOT NULL,  
 [name] varchar(255) NOT NULL, -- a simple name for each item  
 [processed_flag] bit NOT NULL, -- 0: Ready for processing, 1: Processed  
 CONSTRAINT [PK_work_queue] PRIMARY KEY CLUSTERED   
(  
 [work_queue_id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
)  
-- Add data to table  
INSERT INTO work_queue (name, processed_flag)  
SELECT 'A', 0  
UNION ALL SELECT 'B', 0  
UNION ALL SELECT 'C', 0  
UNION ALL SELECT 'D', 0  
UNION ALL SELECT 'E', 0  
UNION ALL SELECT 'F', 0  
UNION ALL SELECT 'G', 0  
UNION ALL SELECT 'H', 0  
UNION ALL SELECT 'I', 0  
UNION ALL SELECT 'J', 0  
UNION ALL SELECT 'K', 0  
  
DECLARE @work_queue_id int, @name varchar(255)  
BEGIN TRANSACTION  
 -- select 1 queue item to process that hasn't been processed yet  
 SELECT TOP(1)  
 @work_queue_id = work_queue_id, @name = name  
 FROM  
 work_queue WITH (UPDLOCK, READPAST)  
 WHERE processed_flag = 0  
 PRINT 'Processing ' + @name + ', work_queue_id = ' + CONVERT(varchar, @work_queue_id)  
 -- simulate delay  
 WAITFOR DELAY '00:00:15'  
 -- flag item as processed  
 UPDATE work_queue SET processed_flag = 1 WHERE work_queue_id = @work_queue_id  
COMMIT  

You could also refer this link and this link for more details about using a table as a queue which may be helpful to you.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Please see:
https://rusanu.com/2010/03/26/using-tables-as-queues/