如何避免SQL Server中的重复值 [英] How to avoid duplicate values in SQL Server

问题描述

我有近10个令牌机器，客户从表令牌中获取令牌号码。我正在使用存储过程访问此表并更新它

  Id名称TokenFrom TokenTo LastUsedToken 
 ---- ------------------------------------------------ 
 1令牌551 999 562 
  

我注意到，在高峰时间，很多客户正在获得重复数字。也许这个问题出现了，因为10个客户在同一时间/小时/秒的时间内获得了令牌。

这里是已发表表

  Id PosId条形码日期状态号码为
 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 
  

即使有时相同的数字也在两台收银机上。我正在使用这个更新语句

获得和更新POS上的下一个令牌号码

  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'
  

我在一组中有3-3个POS，这就是为什么选择 cGroup ，在表中它是 PosId 。 / p>

在这个问题中，我曾经问过有关这个问题的问题，有人帮助我轻松地写入访问令牌编号的存储过程。

但是我仍然遇到重复的问题。任何人都可以告诉我什么是避免重复的最佳方法？

什么是自动加密的最佳方法

解决方案

这里是在SQL 2008中工作的，但不考虑分组，不会重置，并且具有不同的条形公式。

这是令牌颁发的表。在这里插入一个记录储备令牌号码：

  CREATE TABLE [dbo]。[issuedToken2]（
 [Token] [int] IDENTITY（1,1）NOT NULL，
 [条形码] AS（（（（6820000000。）+ [令牌]）*（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 
  

这是一个可用于获取令牌号的存储过程。您可以同时拥有100个系统调用这些数据，并且它们都会有不同的数字：

  CREATE PROC [dbo]。[pGetToken ] 
 @PosID INT 
 AS 
 BEGIN 
 SET NOCOUNT ON 
 
 insert into issuedToken2（PosID）
 VALUES（@PosID）
 
返回scope_identity（）
 END 
 GO 
  

是你如何使用它：调用存储过程与一个posid（在这个例子7）保留令牌号，然后使用它来获取条形码：

  DECLARE @Token INT 
 
 EXEC @Token = pGetToken 7 
 
 SELECT @Token，[条形码] 
 FROM issuedToken2 
 WHERE Token = @ Token 
  

基本上这可以通过使用标识 - 递增数字。我知道你现有的系统不能像这样工作，但你没有解释为什么需要它。

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

解决方案

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.

这篇关于如何避免SQL Server中的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！