How to avoid duplicate values in SQL Server

Question

I have almost 10 token machines where customers are getting token numbers from table Token. I am using a stored procedure for accessing this table and updating it

Id    Name       TokenFrom   TokenTo   LastUsedToken
----------------------------------------------------
1     Token         551        999         562

I have notices that during rush hours a lot of customers are getting duplicate numbers. Maybe this problem is showing up because 10 customers are getting tokens at the same time/hour/second.

Here is issuedToken table

Id  PosId   Barcode        Date                      Status  Number isTaken 
1   2       6282019214317  2016-10-20 09:41:45.020    1      366        1
2   2       6282019215918  2016-10-20 09:42:15.020    1         367     1
3   2       6282019225016  2016-10-20 09:42:45.020    1         368     1
4   3       6282019230812  2016-10-20 09:42:55.020    1         369     1

Even sometimes same number is coming on two cashier machines also. I am getting and updating Next Token Number on POS using this Update statement

UPDATE POS 
SET tNo = (SELECT TOP 1 NUMBER 
           FROM Tickets 
           WHERE STATUS = 1 
             AND isTaken = 1 
             AND PosId = (SELECT CGROUP 
                          FROM POS 
                          WHERE NAME='ABC')) 
WHERE NAME = 'ABC'

I have 3-3 POS in one group that's why selecting cGroup and in table it's PosId.

I have asked question related to this question before in this question, where someone help me to write a stored procedure for accessing Token Number easily.

But still I am having duplication issue. Can anyone tell me what the best way to avoid duplication ?

What is Best Approach for Auto Increament

Answer 1

This assumes you have SQL Server 2012 (please clarify)

Not a complete answer but I can expand if you wish.

First create a sequence (just run this once):

create sequence CustomerCare 
    as integer
    start with 51
    increment by 1
    minvalue 51
    maxvalue 350
    cycle;

now get the next sequence from it (run this as often as you like):

select next value for CustomerCare

This method can't hand out the same number to two different requests so you won't get duplicates. It will automatically wrap around when it gets to 350. You can create and use sequences for your other groupings. Much simpler than the other solution and 100% reliable.

Again I need to advise against creating magic number ranges for specific groups.

Answer 2

I got solution by changing my Stored Procedure that is accessing Next Number. Now logic is to lock Token table and getting next number. Now i am getting only unique numbers.

Thanks everyone for your kind responses.

Answer 3

Here is something that works in SQL 2008 but does not take into account groupings, does not reset, and has a different formula for barcode

This is the token issued table. Inserting a record in here 'reserves' the token number:

CREATE TABLE [dbo].[issuedToken2](
    [Token] [int] IDENTITY(1,1) NOT NULL,
    [Barcode]  AS (((6820000000.)+[Token])*(100)+[PosID]),
    [GenerationDate] [smalldatetime] NOT NULL,
    [PosID] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[issuedToken2] 
    ADD  CONSTRAINT [DF_issuedToken_GenerationDate]  
    DEFAULT (getdate()) FOR [GenerationDate]
GO

This is a stored procedure that you can use to get a token number. You can have 100 systems calling this simultaneously and they'll all get a different number:

CREATE PROC [dbo].[pGetToken]
@PosID INT
AS
BEGIN
SET NOCOUNT ON

insert into issuedToken2 (PosID) 
VALUES(@PosID)

RETURN scope_identity()
END
GO

This is how you use it all: call the stored proc with a posid (in this example 7) to reserve the token number, then use it to get the barcode:

DECLARE @Token INT

EXEC @Token = pGetToken 7

SELECT @Token, [Barcode]
FROM issuedToken2
WHERE Token=@Token

Basically this works by using an identity - an incrementing number. I know your existing system doesn't work like this but you haven't explained why it needs to.