SQL Server的相关身份 - 是有这样的事情？(SQL Server dependent Id

我使用SQL Server 2008 R2。

我在寻找我形容为依赖的身份特征。

我将通过一个例子来说明。

考虑这样一个表：

脚本

CREATE TABLE [dbo].[Rooms](
    [RoomID] [int] NOT NULL,
    [ItemID] [int] NOT NULL,
    [ItemDescription] [nvarchar] (250))
GO

数据：

RoomID  ItemID  ItemDescription
------  ------  --------------- 
7       1       Door
7       2       Window (West)
7       3       Window (North)
8       1       Door
8       2       Table #1
8       3       Table #2
7       4       Table #1 
8       4       Chair #1 
7       5       Table #2 
7       6       Table #3 
8       5       Chair #2

（谁能告诉秘密这里怎么形式的例表？）

我会喜欢能够申报相关的标识列如下：

ItemID [int] Identity(RoomID,1,1) NOT NULL

在一个新行[Rooms]应该触发测试的最大值ItemID其中RoomID = @roomID并添加1。

相反的，在一个变化更新RoomID使用删除和插入所需的数据。

现在我做的编程是这样的：

DECLARE @roomID INT
SET @roomID = 7
INSERT INTO [Allocation].[dbo].[Rooms]
    ([RoomID], [ItemID], [ItemDescription]) VALUES (@roomID,
    (SELECT max([ItemID])+1 FROM [Allocation].[dbo].[Rooms] WHERE [RoomID]=@roomID)
    ,'Chair #1')
GO

那么，有没有这样的功能？

在可能的情况下是没有的，我可以编程设定下一个依赖的身份自动为我的服务器上，提供一个表，父列和相关标识列？

Answer 1:

您可以使用触发器和索引，以提高性能，并确保有没有重复。

改变你的表有一个主键，并允许空值的ItemID

CREATE TABLE [dbo].[Rooms](
    [RoomID] [int] NOT NULL,
    [ItemID] [int] NULL,
    [ItemDescription] [nvarchar](250) NULL,
    [Id] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [PK_Rooms] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )
)

然后添加一个触发器

CREATE TRIGGER RoomTrigger
   ON  Rooms
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    update Rooms
    set 
        ItemID = (select coalesce(MAX(itemid), 0) + 1 
                      from Rooms r where r.RoomID = inserted.RoomID )
    from
        inserted where Rooms.Id = inserted.Id

END

然后，你可以这样做

insert into Rooms (RoomID, ItemDescription) values (1, 'Test')
insert into Rooms (RoomID, ItemDescription) values (1, 'Test')

这导致

RoomID  ItemID ItemDescription Id
2       0      Test            1
2       1      Test            2

正如marc_s建议我使用SQL查询应力与10个线程，看看负载的情况下这种触发会发生什么。 我没有在所有任何重复（使用默认的隔离级别），但我没有得到死锁的负载，因为我本来期望。

使用来自问题原始查询我得到了很多重复的。

使用触发的方法，我得到死锁和结果是这样的：

RoomID ItemID ItemDescription Id
1      6      Test            6
1      7      Test            9
1      8      Test            902
1      9      Test            903

这里的ItemID是连续的，但约900出1000行未能插入留下编号较大的差距。

如果再加上以下指标：

CREATE UNIQUE NONCLUSTERED INDEX [IX_Rooms] ON [dbo].[Rooms] 
(
    [RoomID] ASC,
    [ItemID] ASC
)

为了保证没有重复，提高现在计算最大值（项目Id）特定RoomID，然后的性能：

• 从问题的原始查询导致重复的，只有设法插入500行。
• 使用默认的隔离级别触发版本成功，没有任何死锁或错误 ，运行速度非常快。

使用具有隔离级别触发=序列化带回死锁所以只有40％的刀片的成功（但没有例外，由于重复）。

作为最后的测试试图与触发+ 50个线程+隔离级别=默认。 没有错误 。

文章来源: SQL Server dependent Identity - is there such a thing?