Início / dba / Perguntas / 169180
Accepted
Alexei
Asked: 2017-04-06 02:28:06 +0800 CST

Como reduzir valores de identidade para evitar estouro de inteiro?

  • 772

Eu tenho algumas tabelas de folhas (sem FK para elas) com algumas centenas de milhares de registros que são usados ​​para sincronizar alguns dados externos usando o Entity Framework ORM. Isso envolve alguns DELETEs seguidos por BULK INSERTs.

Para a maioria das tabelas, alguns valores antigos podem ficar para sempre, então não posso usar SEQUENCEs com CYCLE, como sugerido em um dos comentários.

Um efeito é que os valores de identidade continuarão aumentando ao longo do tempo e eu quero ser capaz de reduzir seus valores.

Esta pergunta e suas respostas explicam que os valores de identidade não podem ser atualizados, mesmo que identity_insertestejam ativados para a tabela.

Uma maneira rápida de fazer isso é transferir todos os dados para uma tabela de buffer e realizar a troca via renomear. Algo como o seguinte:

-- ActiveDirectoryCache_bak is the table I want to reduce identity values for
-- ActiveDirectoryCache_bak_buffer is a buffer table that will be renamed to ActiveDirectoryCache_bak once data transfer is ready

begin tran
select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);  

-- Min UserId = 100, Count = 176041
-- Checking identity information: current identity value '204558', current column value '204558'.    

select * into ActiveDirectoryCache_bak_buffer
from ActiveDirectoryCache_bak
where 1 = 0

DBCC CHECKIDENT('ActiveDirectoryCache_bak_buffer', RESEED, 1)    

insert into ActiveDirectoryCache_bak_buffer
select LoginUsername, GivenName, MiddleName, Surname, EmailAddress
from ActiveDirectoryCache_bak

drop table ActiveDirectoryCache_bak

alter table ActiveDirectoryCache_bak_buffer add constraint PK_ActiveDirectoryCache_bak PRIMARY KEY (UserId)
EXEC sys.sp_rename 'ActiveDirectoryCache_bak_buffer', 'ActiveDirectoryCache_bak';

select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);

-- Min UserId = 1, Count = 176041
-- Checking identity information: current identity value '176041', current column value '176041'.  

-- this should be replaced with commit when not in test mode
rollback

Eu posso fazer esse tipo de operação durante a noite e eles parecem levar apenas alguns segundos para o meu volume de dados (4 segundos para este exemplo).

Pergunta: Existem métodos para evitar a execução de uma cópia completa de dados para obter valores menores para colunas de identidade? Ou esta é uma das melhores abordagens no meu contexto (volume de dados razoável e poder bloquear algumas tabelas por vários segundos).

sql-server sql-server-2014

3 respostas

  1. Best Answer

    Você pode tentar simplesmente descartar e adicionar novamente a coluna Identity sem copiar a tabela inteira. Isso tem o mesmo efeito que você está tentando com o código na pergunta. Nenhuma das abordagens realmente lida com a manutenção de linhas existentes de acordo com a declaração "alguns valores antigos podem permanecer para sempre" da pergunta, mas para quaisquer situações que funcionem com o código na pergunta, o seguinte também deve funcionar.

    Você também deve considerar iniciar o intervalo de identidade no valor mínimo. Começar em 1 fornece pouco mais de 2,1 bilhões de valores, portanto, começar no valor mínimo (ou próximo a ele) fornece 4,2 bilhões de valores. Portanto, é menos provável que você precise de uma operação de reseed no futuro.

    CONFIGURAR

    -- DROP TABLE dbo.IdentityTest;
CREATE TABLE dbo.IdentityTest
(
  ID INT IDENTITY(10203, 7) NOT NULL CONSTRAINT [PK_IdentityTest] PRIMARY KEY,
  SomethingElse UNIQUEIDENTIFIER NOT NULL,
  SomeName NVARCHAR(256) NOT NULL
);

INSERT INTO dbo.IdentityTest ([SomethingElse], [SomeName])
  SELECT NEWID(), so1.[name] + N'~' + so2.[name]
  FROM   master.sys.all_columns so1
  CROSS JOIN master.sys.objects so2;
-- 782,880 rows

SELECT * FROM dbo.IdentityTest;

    Solte e recrie o Identity PK

    BEGIN TRY
  BEGIN TRAN;

  ALTER TABLE dbo.IdentityTest
    DROP CONSTRAINT [PK_IdentityTest];

  ALTER TABLE dbo.IdentityTest
    DROP COLUMN [ID];

  ALTER TABLE dbo.IdentityTest
    ADD [ID] INT
    IDENTITY(-2140000000, 1)
    NOT NULL
    CONSTRAINT [PK_IdentityTest]
      PRIMARY KEY;

  COMMIT TRAN;
END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK TRAN;
  END;

  THROW;
END CATCH;

SELECT * FROM dbo.IdentityTest;
    • 2

  2. Embora (como visto nos comentários), existam outras opções em potencial, parece que esta:

    • funciona;
    • não requer uma interrupção significativa;
    • não requer alterações em seu aplicativo;
    • poderia até ser automatizado, se necessário.

    Acho que é provavelmente a sua melhor opção. É o que eu usaria.

    • 1

  3. Você pergunta "Existem métodos para evitar a execução de uma cópia completa de dados para obter valores menores para colunas de identidade?" Para descobrir se há lacunas na sequência, será necessário ler cada linha (ou índice nessa coluna). Para preencher uma lacuna, será necessária uma atualização em cada linha após a lacuna. (Como você não pode atualizar uma IDENTIDADE, isso se torna uma exclusão seguida de uma inserção, com algumas jiggery-pokery de reseed no meio. Parece que pode ser mais lento do que uma abordagem de "cópia de tabela em massa", embora a justificativa para isso me iluda e provavelmente depende de algoritmos de leitura antecipada e de acesso ao disco.) Supondo que sejam os dados mais antigos que estão sendo excluídos, eles terão valores de identidade mais baixos, de modo que a maior parte da tabela será atualizada a cada vez. Uma cópia completa com leituras e gravações sequenciais parece ser mais eficiente.

    Uma abordagem é tornar a tabela particionada. Resemeie a identidade em uma tabela de trabalho. Alterne os dados antigos e alterne na mesa de trabalho. O tempo de inatividade será medido em (milis) segundos.

    Isenção de responsabilidade: eu não testei, então não tenho certeza de como ele reagirá. Será necessário considerar as gravações que ocorrem enquanto a mesa de trabalho está sendo preparada. Talvez duas partições ativas que passam por manutenção em horários alternativos?

    • 0

relate perguntas