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

Solution 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 
    asintegerstartwith51
    increment by1
    minvalue 51
    maxvalue 350cycle;

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

selectnext 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.

Solution 2:

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:

CREATEPROC[dbo].[pGetToken]
@PosIDINTASBEGINSETNOCOUNTONinsertintoissuedToken2 (PosID) 
VALUES(@PosID)

RETURNscope_identity()
ENDGO

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@TokenINTEXEC@Token= pGetToken 7SELECT@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.

Solution 3:

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.