SQL - Generate Round robin number on insert

Question

I am deploying an existing bespoke windows service (C#) multiple instances that will reads from a single queue table.

The queue is based on the below simple SQL table.

Record-Id (int auto id)

Added-Date (Date)

Added-By (Text)

Data-To-be-Processed (Text)

**Pool-Number (int)**

How would I create a round robin sequence number for each insert given a max pool size? here I use a pool size of 3 (This can be hard coded). e.g.

1 | 31/10/2014 | DATA | Pool 1

2 | 31/10/2014 | DATA | Pool 2

3 | 31/10/2014 | DATA | Pool 3

4 | 31/10/2014 | DATA | Pool 1

5 | 31/10/2014 | DATA | Pool 2

6 | 31/10/2014 | DATA | Pool 3

7 | 31/10/2014 | DATA | Pool 1

I have thought about using a Sequence table and increment it on each insert and reset it to 1 when it gets to the max pool size e.g.

TbSeq

  dbSeq (int) (Will contain 1-3 depending last insert)

Is there a better way to do this?

`CREATE SEQUENCE`. There's a reason this was baked in the engine: implementing sequences yourself in a way that's safe under concurrency but doesn't collapse under load is not trivial. (The same applies to queue tables, incidentally, which is why Service Broker exists.) — Jeroen Mostert, Oct 31 '17 at 12:46

SqlZim · Answer 1 · 2017-10-31T12:56:20.153

2

If you can use the RecordId as an aid, then you could use Modulo (%)

select *, 1+((RecordId-1)%3) as Pool
from t

rextester demo: http://rextester.com/WNEIQM50851

returns:

+----------+------------+-------------------+------+
| RecordId | AddedDate  | DataToBeProcessed | Pool |
+----------+------------+-------------------+------+
|        1 | 2014-10-31 | DATA              |    1 |
|        2 | 2014-10-31 | DATA              |    2 |
|        3 | 2014-10-31 | DATA              |    3 |
|        4 | 2014-10-31 | DATA              |    1 |
|        5 | 2014-10-31 | DATA              |    2 |
|        6 | 2014-10-31 | DATA              |    3 |
|        7 | 2014-10-31 | DATA              |    1 |
+----------+------------+-------------------+------+

You could add this as a computed column (persisted optional, but recommended)

alter table t
  add Pool as (1+((RecordId-1)%3)) persisted;

edited Oct 31 '17 at 12:56

answered Oct 31 '17 at 12:51

SqlZim

37,248
6
41
59

It's worth pointing out that `IDENTITY` doesn't guarantee a contiguous sequence (jumps occur on rollbacks or server restarts, for example). Depending on the application, this may or may not matter. – Jeroen Mostert Oct 31 '17 at 12:54
@JeroenMostert True, but a `sequence` doesn't either. – SqlZim Oct 31 '17 at 12:55
True, although a sequence with `NO CACHE` should leave gaps only on rollbacks or *abnormal* server restarts. An identity can jump even on a clean shutdown. – Jeroen Mostert Oct 31 '17 at 12:57

score 0 · Answer 2 · edited Oct 31 '17 at 13:01

0

I wouldn't depend on the RecordId per se. It might have gaps. The simplest way to do a real round-robin is to use row_number() and modulo arithmetic:

select 1 + (row_number() over (order by id) - 1) % 3 as poolnum
from t;

If you know that RecordId has no gaps, then you can use that instead. Using RecordId is more efficient, because you can do the full calculation within one row, and even add a computed column:

alter table t add poolnum as (1 + (row_number() over (order by id) - 1) % 3)

edited Oct 31 '17 at 13:01

SqlZim

37,248
6
41
59

answered Oct 31 '17 at 12:54

Gordon Linoff

1,242,037
58
646
786

You would need to add a custom function to use a window function in a computed column. – SqlZim Oct 31 '17 at 12:58
@Gordon : I think that second example is suffering a copy/paste? the text reads like recordID would be within it and you mean't the more basic recordID -1 % 3 version. – Andrew Oct 31 '17 at 12:59

score 0 · Answer 3 · answered Nov 01 '17 at 10:38

You can use SECQUENCE as:

CREATE TABLE MyTable (
    Record_Id INT IDENTITY(1,1), Added_Date DATE,
    Added_By VARCHAR(50), Data_To_be_Processed VARCHAR(50), Pool_Number INT);
GO    
CREATE SEQUENCE Pool
AS INT
START WITH 1
MINVALUE 1
MAXVALUE 3
CYCLE
GO

INSERT INTO MyTable VALUES
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool);

SELECT *
FROM MyTable;

Result:

+-----------+---------------------+----------+----------------------+-------------+
| Record_Id |     Added_Date      | Added_By | Data_To_be_Processed | Pool_Number |
+-----------+---------------------+----------+----------------------+-------------+
|         1 | 31.10.2014 00:00:00 | DATA     | Pool                 |           1 |
|         2 | 31.10.2014 00:00:00 | DATA     | Pool                 |           2 |
|         3 | 31.10.2014 00:00:00 | DATA     | Pool                 |           3 |
|         4 | 31.10.2014 00:00:00 | DATA     | Pool                 |           1 |
|         5 | 31.10.2014 00:00:00 | DATA     | Pool                 |           2 |
|         6 | 31.10.2014 00:00:00 | DATA     | Pool                 |           3 |
|         7 | 31.10.2014 00:00:00 | DATA     | Pool                 |           1 |
|         8 | 31.10.2014 00:00:00 | DATA     | Pool                 |           2 |
|         9 | 31.10.2014 00:00:00 | DATA     | Pool                 |           3 |
+-----------+---------------------+----------+----------------------+-------------+

SQL - Generate Round robin number on insert

3 Answers3