Início / dba / Perguntas / 249625
Accepted
Daniel James Bryars
Asked: 2019-09-26 12:32:56 +0800 CST

Qual é a solução idiomática no SQL Server para reservar um bloco de IDs para uso em uma inserção em massa?

  • 772

Eu tenho uma tabela com uma coluna de identidade e quero reservar um bloco de ids que posso usar para inserção em massa, permitindo que as inserções ainda aconteçam nessa tabela.

Observe que isso faz parte de uma inserção em massa de várias tabelas, onde essas outras tabelas se relacionam a esses ids por meio de um FK. Portanto, eu preciso bloqueá-los para que eu possa preparar os relacionamentos de antemão.

Eu encontrei uma solução que funciona travando a mesa em uma transação e depois faz a nova propagação (que é bem rápida). Mas parece um pouco hacky para mim - existe um padrão geralmente aceito para fazer isso?

create table dbo.test
(
    id bigint not null primary key identity(1,1),
    SomeColumn nvarchar(100) not null
)

Aqui está o código para bloquear (abrir espaço para) alguns ids:

declare @numRowsToMakeRoomFor int = 100

BEGIN TRANSACTION;

        SELECT  MAX(Id) FROM dbo.test WITH (  XLOCK, TABLOCK ) -- will exclusively lock the table whilst this tran is in progress, 
        --another instance of this query will not be able to pass this line until this instance commits

        --get the next id in the block to reserve
        DECLARE @firstId BIGINT = (SELECT IDENT_CURRENT( 'dbo.test' )  +1);

        --calculate the block range
        DECLARE @lastId BIGINT = @firstId + (@numRowsToMakeRoomFor -1);

        --reseed the table
        DBCC CHECKIDENT ('dbo.test',RESEED, @lastId);

COMMIT TRANSACTION;    

select @firstId;

Meu código está processando em lote blocos de dados em pedaços de cerca de 1.000. Tenho cerca de um bilhão de linhas para inserir no total. Tudo está funcionando bem - o banco de dados não é o gargalo, o processamento em lote em si é computacionalmente caro e exige que eu adicione alguns servidores para serem executados em paralelo, então preciso acomodar mais de um processo de "inserção em lote" no mesmo tempo.

sql-server sql-server-2016

1 respostas

  1. Best Answer

    Você pode usar o procedimento (introduzido no SQL Server 2012):
    sp_sequence_get_range

    Para usá-lo, você precisa criar um objeto SEQUENCE e usá-lo como valor padrão em vez da coluna IDENTITY.

    Há um exemplo: 

    CREATE SCHEMA Test ;  
GO  

CREATE SEQUENCE Test.RangeSeq  
    AS int   
    START WITH 1  
    INCREMENT BY 1  
    CACHE 10  
;

CREATE TABLE Test.ProcessEvents  
(  
    EventID int PRIMARY KEY CLUSTERED   
        DEFAULT (NEXT VALUE FOR Test.RangeSeq),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EventCode nvarchar(5) NOT NULL,  
    Description nvarchar(300) NULL  
) ;


DECLARE 
   @range_first_value_output sql_variant ;  

EXEC sp_sequence_get_range  
@sequence_name = N'Test.RangeSeq'  
, @range_size = 4  
, @range_first_value = @range_first_value_output OUTPUT ;

SELECT @range_first_value_output;

    Documentação: sp_sequence_get_range

    • 13

relate perguntas