Início / dba / Perguntas / 114608
Accepted
Vaccano
Asked: 2015-09-10 10:22:49 +0800 CST

Por que a remoção da propriedade Identity em uma coluna não é suportada

  • 772

Eu li que depois do SQL Server 2000, a capacidade de "desidentificar" uma coluna de identidade foi removida. E que isso era "By Design" (não apenas um recurso ausente).

Aqui está um exemplo que encontrei em um blog . Envolve a atualização das tabelas do sistema. (E essa capacidade foi removida após o SQL Server 2000.) Entendo que fazer isso por meio de tabelas do sistema não é uma boa ideia. Só estou me perguntando por que um recurso para fazer isso de outra maneira não existe.

Trabalhar em torno disso vai me causar uma quantidade considerável de trabalho. (Copiando muitas centenas de milhões de linhas para novas tabelas em um ambiente intolerante ao tempo de inatividade.)

Então pensei em perguntar "Por quê".

O que mudou no Sql Server 2005 e versões posteriores que tornaram isso uma coisa ruim? Ou sempre foi ruim e simplesmente não foi bloqueado?

Qual "prática recomendada" (ou princípio semelhante) seria violado ao tornar uma coluna de identidade uma coluna normal novamente?

--

Atualização para responder à solicitação de "por que estou fazendo isso":
Este é um resumo de alto nível: vou começar a adicionar partições às minhas tabelas. (Para que eu possa arquivar/eliminar dados antigos.) Tudo isso é fácil. Mas ocasionalmente preciso mover um registro para uma partição diferente para que ele não seja removido (quando uma partição surge para arquivamento/exclusão). (Estou aumentando minha coluna de particionamento em 2 para que sempre haja espaço para mover a linha para uma partição diferente.)

Mas se a coluna de particionamento for uma coluna de identidade, devo excluir e reinserir o valor (não há como atualizar o valor de uma coluna de identidade). O que causa problemas com a replicação.

Portanto, estou querendo usar uma sequência em vez de uma coluna de identidade. Mas essa opção é muito difícil em grandes bancos de dados.

sql-server identity

1 respostas

  1. Best Answer

    A sua questão é, essencialmente:

    Por que não posso mais fazer essa coisa arriscada que nunca deveria ter sido permitido fazer em primeiro lugar?

    A resposta a essa pergunta é amplamente irrelevante (embora você possa ver alguns comentários da Microsoft nesses itens do Connect solicitando essa funcionalidade: #294193 e #252226 ).

    Para completar, minha sinopse é: A capacidade de remover a propriedade de identidade foi um efeito colateral não intencional de ter a capacidade de mexer nas tabelas do sistema em primeiro lugar. Não era para ser usado das várias maneiras que era, geralmente com consequências muito ruins e, portanto, foi removido.

    Foi um hack de tabela de sistema não documentado e sem suporte. A capacidade de alterar dados nas tabelas do sistema não foi removida porque a Microsoft não queria mais que você abrisse caminho para uma coluna ser uma coluna de identidade; ela foi removida porque mexer nas tabelas do sistema é extremamente arriscado. A remoção da IDENTITYpropriedade em si não era uma remoção de recurso especificamente direcionada, e eu nunca teria confiado totalmente nessa abordagem, mesmo nos tempos antigos, quando era possível.

    Dito isso, que tal respondermos a essa pergunta?

    Como removo a propriedade IDENTITY de uma coluna com tempo de inatividade mínimo ou nenhum?

    Isso você pode fazer facilmente, usando ALTER TABLE ... SWITCH, uma técnica que tenho certeza de ter aprendido primeiro com nosso próprio Paul White nas soluções alternativas para Connect #252226 . Exemplo rápido, dada esta tabela simples:

    CREATE TABLE dbo.Original
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  name SYSNAME
);
GO

INSERT dbo.Original(name) VALUES(N'foo'),(N'bar');
GO

SELECT * FROM dbo.Original;
GO

    Resultados:

    EU IRIA nome
    1 foo
    2 bar

    Agora, vamos criar uma tabela de sombra e mudar para ela, depois descartar a tabela antiga, renomear a nova e retomar a atividade normal:

    CREATE TABLE dbo.New
(
  ID INT PRIMARY KEY,
  name SYSNAME
);
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
  ALTER TABLE dbo.Original SWITCH TO dbo.New;
  DROP TABLE dbo.Original;
  EXEC sys.sp_rename N'dbo.New', N'Original', 'OBJECT';
COMMIT TRANSACTION;
GO

INSERT dbo.Original(ID,name) VALUES(3,N'splunge');
UPDATE dbo.Original SET ID = 6 WHERE ID = 1;
GO

SELECT * FROM dbo.Original;
GO

    Resultados:

    EU IRIA nome
    2 bar
    3 mergulhar
    6 foo

    Agora limpe:

    DROP TABLE dbo.Original;

    Esta é apenas uma operação de metadados, sem movimentação de dados, e só bloqueará outros usuários enquanto os metadados estiverem sendo atualizados. Mas, reconhecidamente, é um exemplo muito simplista. Se você tiver chaves estrangeiras ou estiver usando outros recursos como replicação, Change Data Capture, Change Tracking, etc., talvez seja necessário desabilitar ou remover alguns deles antes de fazer essa alteração (não testei todas as combinações). Para chaves estrangeiras especificamente, veja esta dica que mostra como gerar scripts para eliminar e recriar todas (ou selecionadas) as restrições de chave estrangeira.

    Além disso, você precisará atualizar o código do aplicativo para não esperar que o SQL Server preencha essa coluna e verifique todas as instruções de inserção ou seleção que podem depender da ordem das colunas ou das colunas que precisam especificar. Geralmente, eu usaria grep em toda a sua base de código para qualquer menção a esta tabela.

    Veja também este script de Itzik Ben-Gan (fonte: este artigo antigo ) para outra maneira de lidar com isso, mas há movimentação de dados envolvida aqui, portanto, não atende ao requisito "nenhum ou mínimo tempo de inatividade".

    • 22

relate perguntas