محمدخرم شھزاد محمدخرم شھزاد - 1 month ago 5
SQL Question

How to avoid duplicate values in SQL Server

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

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.