插入的记录是否总是接收连续的标识值 [英] Do Inserted Records Always Receive Contiguous Identity Values

问题描述

考虑以下 SQL:

CREATE TABLE Foo
(
    ID int IDENTITY(1,1),
    Data nvarchar(max)
)

INSERT INTO Foo (Data)
SELECT TOP 1000 Data
FROM SomeOtherTable
WHERE SomeColumn = @SomeParameter

DECLARE @LastID int
SET @LastID = SCOPE_IDENTITY()

我想知道我是否可以依赖我插入到表 Foo 中的 1000 行具有连续标识值.换句话说，如果这个 SQL 块产生 2000 的 @LastID，我能确定我插入的第一条记录的 ID 是 1001 吗?我主要对同时将记录插入表 Foo 的多个语句感到好奇.

I would like to know if I can depend on the 1000 rows that I inserted into table Foo having contiguous identity values. In order words, if this SQL block produces a @LastID of 2000, can I know for certain that the ID of the first record I inserted was 1001? I am mainly curious about multiple statements inserting records into table Foo concurrently.

我知道我可以在插入语句周围添加一个可序列化的事务以确保我想要的行为，但我真的需要这样做吗?我担心引入一个可序列化的事务会降低性能，但是如果 SQL Server 在这个语句运行时不允许其他语句插入到表 Foo 中，那么我不必担心.

I know that I could add a serializable transaction around my insert statement to ensure the behavior that I want, but do I really need to? I'm worried that introducing a serializable transaction will degrade performance, but if SQL Server won't allow other statements to insert into table Foo while this statement is running, then I don't have to worry about it.

推荐答案

我不同意接受的答案.这可以通过运行以下命令轻松地进行测试和反证.

I disagree with the accepted answer. This can easily be tested and disproved by running the following.

设置

USE tempdb

CREATE TABLE Foo
(
    ID int IDENTITY(1,1),
    Data nvarchar(max)
)

连接 1

USE tempdb

SET NOCOUNT ON
WHILE NOT EXISTS(SELECT * FROM master..sysprocesses WHERE context_info = CAST('stop' AS VARBINARY(128) ))
 BEGIN
 INSERT INTO Foo (Data)
 VALUES ('blah')
 END

连接 2

USE tempdb

SET NOCOUNT ON
SET CONTEXT_INFO 0x

DECLARE @Output TABLE(ID INT)

WHILE 1 = 1
BEGIN
    /*Clear out table variable from previous loop*/
    DELETE FROM  @Output

    /*Insert 1000 records*/
    INSERT INTO Foo (Data)
    OUTPUT inserted.ID INTO @Output
    SELECT TOP 1000 NEWID()
    FROM sys.all_columns

    IF EXISTS(SELECT * FROM @Output HAVING MAX(ID) - MIN(ID) <> 999 )
        BEGIN
        /*Set Context Info so other connection inserting 
          a single record in a loop terminates itself*/
        DECLARE @stop VARBINARY(128) 
        SET @stop = CAST('stop' AS VARBINARY(128))
        SET CONTEXT_INFO @stop

        /*Return results for inspection*/
        SELECT ID, DENSE_RANK() OVER (ORDER BY Grp) AS ContigSection
        FROM 
          (SELECT ID, ID - ROW_NUMBER() OVER (ORDER BY [ID]) AS Grp
           FROM @Output) O
        ORDER BY ID

        RETURN
        END
END

这篇关于插入的记录是否总是接收连续的标识值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！