SQL Server自定义标识列 [英] SQL Server Custom Identity Column

问题描述

我想生成与产品类型相关的自定义标识列。
此查询可以保证身份的顺序并解决并发性。
这是一个示例查询：

 
开始交易
 
插入TBLKEY 
值（（从TBLKEY中选择（SELECT'A-'+ CAST（MAX（CAST（ID AS INT）））+ 1 AS NVARCHAR）），'EHSAN'）
 
 COMMIT 
 

解决方案

尝试一下：

 开始将TRAN 
 
插入到TBLKEY 
值中（（使用（UPDLOCK）从TBLKEY中选择（SELECT MAX（ID）+1 AS NVARCHAR）），'EHSAN'）
 
提交
  

选择最大ID时，您会在该行上获得U锁。 U锁与U锁不兼容，后者将尝试使用同一查询同时运行来获取另一个会话。在给定的时间只执行一个查询。 ID将是有序且连续的，并且它们之间没有任何间隙。

更好的解决方案是创建一个额外的表，专用于存储当前或下一个ID和使用

您可以通过执行以下操作来了解不同之处：

准备表格

 创建表T（id int不为null主键簇）
插入T值（1）
  

然后在两个不同的会话中依次运行以下查询，相距不到10秒

  BEGIN TRAN 
声明@idv int 
 SELECT @idv = max（id）FROM T 
 WAITFOR DELAY'0 ：0：10'
插入T值（@ idv + 1）
提交
  

等待一段时间，直到两个查询完成。观察其中一个成功，另一个失败。

现在对以下查询进行相同操作

  BEGIN TRAN 
声明@idv int 
 SELECT @ idv = max（id）FROM T WITH（UPDLOCK）
 WAITFOR DELAY'0：0：5'
插入T值（@ idv + 1）
提交
  

查看内容T

使用 DROP TABLE T
清理T表

I want to generate a custom identity column related to type of product. Can this query guaranty the order of identity and resolve concurrency. This is a sample query:

BEGIN TRAN

INSERT INTO TBLKEY
VALUES((SELECT 'A-' + CAST(MAX(CAST(ID AS INT)) + 1 AS NVARCHAR) FROM TBLKEY),'EHSAN')

COMMIT

解决方案

Try this:

BEGIN TRAN

INSERT INTO TBLKEY
VALUES((SELECT MAX(ID) + 1 AS NVARCHAR) FROM TBLKEY WITH (UPDLOCK)),'EHSAN')

COMMIT

When selecting the max ID you acquire a U lock on the row. The U lock is incompatible with the U lock which will try to acquire another session with the same query running at the same time. Only one query will be executed at a given time. The ids will be in order and continuous without any gaps between them.

A better solution would be to create an extra table dedicated only for storing the current or next id and use it instead of the maximum.

You can understand the difference by doing the following:

Prepare a table

CREATE TABLE T(id int not null PRIMARY KEY CLUSTERED)
INSERT INTO T VALUES(1)

And then run the following query in two different sessions one after another with less than 10 seconds apart

BEGIN TRAN
DECLARE @idv int
SELECT @idv = max (id) FROM T
WAITFOR DELAY '0:0:10'
INSERT INTO T VALUES(@idv+1)
COMMIT

Wait for a while until both queries complete. Observe that one of them succeeded and the other failed.

Now do the same with the following query

BEGIN TRAN
DECLARE @idv int
SELECT @idv = max (id) FROM T WITH (UPDLOCK)
WAITFOR DELAY '0:0:5'
INSERT INTO T VALUES(@idv+1)
COMMIT

View the contents of T

Cleanup the T Table with DROP TABLE T

这篇关于SQL Server自定义标识列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！