Por que você não pode adicionar uma identidade a uma coluna existente no SQL Server?

Então, o que há de tão especial em uma coluna de identidade que não pode ser facilmente adicionada em retrospectiva?

Uma identidade não insere valores em uma tabela por si só, ela depende de um INSERTcomando e as linhas antigas da sua tabela exigiriam um UPDATE. Se você tentar UPDATEuma coluna de identidade, receberá um erro como este:

Msg 8102, Level 16, State 1, Line 8
Não é possível atualizar a coluna de identidade 'ColumnName'.

Cada recurso tem um propósito e limitações. A finalidade da identidade não é atualizar valores.

Por que isso não é apenas um único sinalizador int/bigint em algum lugar que está sendo incrementado toda vez que você insere uma nova linha?

Na verdade é algo assim. O CREATE TABLE (Transact-SQL) IDENTITY (Propriedade) diz:

O SQL Server pode armazenar em cache valores de identidade por motivos de desempenho e alguns dos valores atribuídos podem ser perdidos durante uma falha de banco de dados ou reinicialização do servidor.

Portanto, sua IDENTITYcoluna tem algo como um sinalizador que solicita ao SQL Server um desses valores ao inserir uma nova linha .

Na seção Comentários do IDENTITYdocumento, você pode ver que o recurso tem algumas restrições e o motivo é:

Essas restrições fazem parte do projeto para melhorar o desempenho e porque são aceitáveis ​​em muitas situações comuns.

Se entendi sua pergunta corretamente, você não está perguntando como criar uma nova coluna com novos IDENTITYvalores. Você está perguntando por que não podemos emitir ALTER TABLE tbl ALTER COLUMN col int IDENTITYem uma coluna existente.

Como você disse, tudo isso é efetivamente uma DEFAULTrestrição com um contador.

A resposta que eu acho é simplesmente que nunca foi implementado , claramente poucas pessoas pediram por isso. Então temos que usar soluções alternativas.

Eu não acho que haja nenhuma grande dificuldade em adicionar esse recurso, além do fato de que ele teria que ser especificado, implementado e testado. Portanto, se você deseja esse recurso, vote no problema de feedback existente no Azure.

Tendo em mente o primeiro e o último parágrafo da resposta de Ronaldo, outra coisa a notar é que simplesmente não há mecanismo para adicionar um IDENTITYa uma tabela após o fato. A IDENTITYcoluna tem alguns parâmetros em torno dela, como o valor de semente e incremento, que podem até ser números negativos. Uma IDENTITYcoluna não precisa fazer parte da chave primária nem depende dela.

Se alguém pudesse adicionar uma IDENTITYcoluna a uma tabela após o fato, não haveria como correlacionar a quais linhas os IDENTITYvalores deveriam pertencer. Seria necessário haver mecanismos adicionais programados no SQL Server para fornecer uma maneira de especificar como o IDENTITYpode gerar o valor apropriado para cada linha, talvez com uma ORDER BYcláusula.

Mas mesmo isso não resolve todos os problemas, porque se o usuário especificar um conjunto não exclusivo de campos para ORDER BY(mas quiser IDENTITYque seja exclusivo), o que deve IDENTITYfazer? ... ele poderia escolher um aleatoriamente como a ROW_NUMBER()função de janela faz, mas isso tornaria a IDENTITYfunção não determinística, o que não é uma ótima característica para uma IDENTITYcoluna. Ou poderia apenas gerar valores dupe IDENTITYe não garantir exclusividade, o que é bom, mas não resolve o problema do usuário.

Para encurtar a história, se a Microsoft quisesse fornecer uma maneira de adicionar uma IDENTITYcoluna a uma tabela depois de preenchida com linhas, é possível, mas exigiria um pouco de reflexão sobre como isso deveria ser feito. Também é um recurso que não é frequentemente necessário, portanto, o design atual que restringe a capacidade de fazê-lo é geralmente aceitável.

Você não pode adicionar uma identidade a uma coluna existente, mas pode começar a usar uma sequência.

https://learn.microsoft.com/it-it/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15

Talvez isso possa lhe dar uma alternativa.

Você pode criar uma nova tabela vazia com uma IDENTITYcoluna e usar ALTER TABLE SWITCHpara alternar as linhas para a nova definição de tabela como uma operação somente de metadados, para que isso possa ser usado para alternar efetivamente a propriedade identity de maneira complicada.

A IDENTITYpropriedade é apenas uma propriedade de metadados em algumas tabelas do sistema e isso pode ser facilmente atualizável se implementado. Enquanto isso, o fato de ALTER TABLE ... SWITCHnão exigir que essa propriedade seja a mesma na origem e no destino pode ser usado para conseguir isso efetivamente.

Você ainda precisa descartar e renomear, mas ajuda porque apenas altera os metadados da tabela. Não há movimento de linhas e, no final, você acaba com exatamente os mesmos dados e páginas de índice associadas a uma tabela que agora contém uma IDENTITYcoluna.

Consulte Por que não há suporte para remover a propriedade Identity em uma coluna para obter um exemplo.