Adicionar autoincremento ao PK existente

A maneira como eu entendo sua pergunta é que você tem uma tabela existente com uma coluna que até agora foi preenchida com valores manuais e agora você deseja (1) transformar essa coluna em uma IDENTITYcoluna e (2) certificar-se de que o IDENTITYinício do valor mais recente nas linhas existentes.

Primeiro, alguns dados de teste para brincar:

CREATE TABLE dbo.ident_test (
    id    int NOT NULL,
    xyz   varchar(10) NOT NULL,
    CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);

INSERT INTO dbo.ident_test (id, xyz)
VALUES (1, 'test'),
       (2, 'test'),
       (5, 'test'),
       (6, 'test'),
       (10, 'test'),
       (18, 'test'),
       (19, 'test'),
       (20, 'test');

O objetivo é tornar a coluna de chave primária da tabela, id, uma IDENTITYcoluna que começará em 21 para o próximo registro que for inserido. Para este exemplo, a coluna xyzrepresenta todas as outras colunas da tabela.

Antes de fazer qualquer coisa, por favor, leia os avisos na parte inferior deste post.

Primeiro, caso algo dê errado:

BEGIN TRANSACTION;

Agora, vamos adicionar uma coluna de trabalho temporária id_tempe definir essa coluna para os idvalores da coluna existente:

ALTER TABLE dbo.ident_test ADD id_temp int NULL;
UPDATE dbo.ident_test SET id_temp=id;

Em seguida, precisamos descartar a idcoluna existente (você não pode simplesmente "adicionar" IDENTITYa uma coluna existente, você precisa criar a coluna como um IDENTITY). A chave primária também tem que ir, porque a coluna depende dela.

ALTER TABLE dbo.ident_test DROP CONSTRAINT PK_ident_test;
ALTER TABLE dbo.ident_test DROP COLUMN id;

... e adicione a coluna novamente, desta vez como um IDENTITY, junto com a chave primária:

ALTER TABLE dbo.ident_test ADD id int IDENTITY(1, 1) NOT NULL;
ALTER TABLE dbo.ident_test ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id);

Aqui é onde fica interessante. Você pode habilitar IDENTITY_INSERTna tabela, o que significa que você pode definir manualmente os valores de uma IDENTITYcoluna ao inserir novas linhas (mas não atualizando as linhas existentes).

SET IDENTITY_INSERT dbo.ident_test ON;

Com esse conjunto, DELETEtodas as linhas da tabela, mas as linhas que você está excluindo, estão OUTPUTna mesma tabela - mas com valores específicos para a idcoluna (da coluna de backup).

DELETE FROM dbo.ident_test
OUTPUT deleted.id_temp AS id, deleted.xyz
INTO dbo.ident_test (id, xyz);

Feito isso, desligue IDENTITY_INSERTnovamente.

SET IDENTITY_INSERT dbo.ident_test OFF;

Solte a coluna temporária que adicionamos:

ALTER TABLE dbo.ident_test DROP COLUMN id_temp;

E, finalmente, resemente a IDENTITYcoluna, para que os próximos registros idsejam retomados após o maior número existente na idcoluna:

DECLARE @maxid int;
SELECT @maxid=MAX(id) FROM dbo.ident_test;
DBCC CHECKIDENT ("dbo.ident_test", RESEED, @maxid)

Verificando a tabela de exemplo, o idnúmero mais alto é 20.

SELECT * FROM dbo.ident_test;

Adicione outra linha e verifique seu novo IDENTITY:

INSERT INTO dbo.ident_test (xyz) VALUES ('New row');
SELECT * FROM dbo.ident_test;

No exemplo, a nova linha terá id=21. Por fim, se estiver satisfeito, confirme a transação:

COMMIT TRANSACTION;

Importante

Esta não é uma operação trivial e traz alguns riscos dos quais você deve estar ciente.

• Faça isso em um ambiente de teste dedicado. Tenha backups. :)

• Eu gosto de usar BEGIN/COMMIT TRANSACTIONporque evita que outros processos mexam com a tabela enquanto você está no meio de alterá-la, e dá a possibilidade de reverter tudo se algo der errado. No entanto, qualquer outro processo que tente acessar sua mesa antes de você confirmar sua transação acabará esperando. Isso pode ser muito ruim se você tiver uma tabela grande e/ou estiver em um ambiente de produção.

• OUTPUT .. INTOnão funcionará se sua tabela de destino tiver restrições de chave estrangeira ou qualquer um dos vários outros recursos que não consigo lembrar de cabeça. Em vez disso, você pode descarregar os dados em uma tabela temporária e inseri-los novamente na tabela original. Você pode usar a alternância de partição (mesmo que não use partições).

• Execute essas instruções uma a uma, não como um lote ou em um procedimento armazenado.

• Tente pensar em outras coisas que podem depender da idcoluna que você está descartando e recriando. Quaisquer índices terão que ser descartados e recriados (como fizemos com a chave primária). Lembre-se de criar scripts de cada índice e restrição que você precisará recriar antecipadamente.

• Desabilite qualquer INSERTe DELETEacionadores na mesa.

Se recriar a tabela for uma opção:

Se recriar a tabela é uma opção para você, tudo é muito mais simples:

• Crie a tabela vazia, com a idcoluna como IDENTITY,
• Colocado IDENTITY_INSERT ONpara a mesa,
• Preencha a tabela,
• Definir IDENTITY_INSERT OFF, e
• Resemeie a identidade.

Usar UPDATE, DELETE ou INSERT para mover dados pode levar muito tempo e usar recursos (IO) em dados e arquivos/discos de log. É possível evitar o preenchimento do log de transações com potencialmente muitos registros ao trabalhar em uma grande tabela: Usando a troca de partição, apenas os metadados são alterados.

Não há movimentação de dados envolvida e, portanto, isso é realizado muito rapidamente (quase instantâneo).

Tabela de amostra

A questão não mostra a tabela original DDL. O seguinte DDL será usado como exemplo nesta resposta:

CREATE TABLE dbo.idT(
    id int not null
    , uid uniqueidentifier not null
    , name varchar(50)
);
ALTER TABLE dbo.idT ADD CONSTRAINT PK_idT PRIMARY KEY CLUSTERED(id);

Meia dúzia de IDs aleatórios fictícios de 0 a 15 são adicionados com esta consulta:

WITH ids(n) AS(
    SELECT x1.n+x2.n*4
    FROM (values(0), (3)) as x1(n)
    CROSS JOIN (values(0), (2), (3)) as x2(n)
)
INSERT INTO idt(id, uid, name)
SELECT n, NEWID(), NEWID() 
FROM ids

Dados de exemplo emIdT

id  uid                                     name
0   65533096-5007-43EA-88AD-D6776B3B94FA    6A69D4F2-D682-4168-A92F-4CD2E2DBC21D
3   CE87F1ED-BE1A-4F2D-8D62-E1ECA822D35B    AF0524D9-0DBB-41E1-883B-003CB4E4F012
8   34A1DBFD-4F92-4F34-9F04-4CDC824AB15A    02B4BDA4-D515-4262-9031-0BE496AC24CE
11  51606C95-9DE8-4C30-B23B-F915EEA41156    93258103-9C22-4F9C-85CF-712ED0FB3CE6
12  CEC80431-0513-4751-A250-0EB3390DACAB    2DA6B8AF-3EBC-42B3-A76C-028716E24661
15  5037EA83-286F-4EBC-AD7C-E237B570C1FF    095E51E9-8C38-4104-858F-D14AA810A550

Nova mesa comIDENTITY(0, 1)

O único problema idTé a falta do IDENTITY(0, 1)imóvel no id. Uma nova tabela com uma estrutura semelhante e IDENTITY(0, 1)é criada:

CREATE TABLE dbo.idT_Switch(
    id int identity(0, 1) not null
    , uid uniqueidentifier not null
    , name varchar(50)
);
ALTER TABLE dbo.idT_Switch ADD CONSTRAINT PK_idT_Switch PRIMARY KEY CLUSTERED(id);

Além de IDENTITY(0, 1), idT_Switché idêntico a idT.

Chaves estrangeiras

As chaves estrangeiras idTdevem ser removidas para permitir que essa técnica seja usada.

Interruptor de partição

As tabelas idTe têm uma estrutura compatível. Em vez de idT_Switchusar instruções e para mover linhas de ou para si , pode ser usado:DELETEUPDATEINSERTidTidT_SwitchidTALTER TABLE ... SWITCH

ALTER TABLE dbo.idT
SWITCH TO dbo.idT_Switch;

A única 'partição' de PK_idT(toda a tabela) é movida para PK_idT_Switch(e vice-versa). idTagora contém 0 linhas e idT_Switchcontém 6 linhas.

Você pode encontrar a lista completa de requisitos de compatibilidade de origem e destino aqui:

Transferindo dados com eficiência usando a alternância de partição

Observe que esse uso de SWITCHnão requer Enterprise Edition, pois não há particionamento explícito. Uma tabela não particionada é considerada uma tabela com uma única partição do SQL Server 2005 em diante.

SubstituiridT

idTagora está vazio e inútil e pode ser descartado:

DROP TABLE idT;

idT_Switchpode ser renomeado e substituirá a idTtabela antiga:

EXECUTE sys.sp_rename
    @objname = N'dbo.idT_Switch',
    @newname = N'idT', -- note lack of schema prefix
    @objtype = 'OBJECT';

Chaves estrangeiras

As chaves estrangeiras podem ser adicionadas novamente à nova idTtabela. Qualquer outra coisa removida anteriormente idTpara tornar as tabelas compatíveis para comutação também precisará ser refeita.

Resemente

SELECT IDENT_CURRENT( 'dbo.idT');

Este comando retorna 0. A tabela idT contém 6 linhas com MAX(id) = 15. DBCC CHECKIDENT ( table_name ) pode ser usado:

DBCC CHECKIDENT ('dbo.idT');

Como 15 é maior que 0, ele será propagado automaticamente sem procurar por MAX(id):

Se o valor de identidade atual de uma tabela for menor que o valor máximo de identidade armazenado na coluna de identidade, ele será redefinido usando o valor máximo na coluna de identidade. Consulte a seção 'Exceções' a seguir.

IDENT_CURRENT agora retorna 15 .

Testar e adicionar dados

Uma declaração simples INSERT:

INSERT INTO idT(uid, name) SELECT NEWID(), NEWID();

Adiciona esta linha:

id  uid                                     name
16  B395D692-5D7B-4DFA-9971-A1497B8357A1    FF210D9E-4027-479C-B5D8-057E77FAF378

A idcoluna agora está usando a identidade e o valor recém-inserido é de fato 16 (15+1).

Mais Informações

Há uma pergunta e resposta relacionadas com mais informações sobre a SWITCHtécnica aqui:

Por que a remoção da propriedade Identity em uma coluna não é compatível

Se você quiser começar com um novo valor de identidade, precisará propagar novamente sua identidade. Dê uma olhada na documentação paraCHECKIDENT

DBCC CHECKIDENT (yourtable, reseed, starting point)

ATIVAR e DESATIVAR IDENTITY_INSERT

Se sua tabela for TABLE_A então

1. CREATE TABLE TABLE_B semelhante a TABLE_A com a coluna de identidade
2. SET IDENTITY_INSERT TABLE_B ON
3. INSERIR em TABLE_B de TABLE_A
4. SET IDENTITY_INSERT TABLE_B OFF
5. DROP TABLE TABLE_A e renomeie a tabela B Exec sp_rename 'TABLE_B', 'TABLE_A'

Se você tem uma situação como eu, então você pode fazer isso muito mais fácil

Etapa 1: obter um bom conjunto de dados, incluindo os valores do campo de chave (no meu caso, o problema foi que a chave primária não pôde ser sincronizada corretamente com duas outras tabelas)

Etapa 2: use a capacidade do SSMS para criar uma tabela de descarte e criação da que você precisa criar. Além disso, certifique-se de que a tabela NOVA tenha uma chave de identidade primária adicionada

Nota:
Isso foi necessário para mim, pois usei os dados bons com uma chave primária semeada, que era a chave primária correta incrementada, mas não consegui mais incrementar automaticamente esse campo

Etapa 3: use a seguinte consulta para inserir o incremento idêntico do PK

• INSERT INTO [dbo].[fact_table2]
      ([dim_order_id]
      ,[dim_inventory_id]
      ,[primary_location_velocity_code]
      ,[profit_ordered]
      ,[fill_rate]
      ,[profit_unfilled]
      ,[dollar_weighted_fill_rate]
      ,[missed_sales])
  
   SELECT [dim_order_id]
      ,[dim_inventory_id]
      ,[primary_location_velocity_code]
      ,[profit_ordered]
      ,[fill_rate]
      ,[profit_unfilled]
      ,[dollar_weighted_fill_rate]
      ,[missed_sales]
      from
      [dbo].[fact_table1]
      order by
      [fact_table1_id] asc
  

Etapa 4: testar se as tabelas são idênticas

• select top 10 *
  from [dbo].[fact_table1]
  where
  profit_unfilled > 0
     order by
     [fact_table1_id] desc
  

• select top 10 *
  from [dbo].[fact_table2]
  where
  profit_unfilled > 0
     order by
     [fact_table1_id] desc
  

Último passo: Observe que a instrução insert não estava usando o campo PK.... Ele foi usado apenas para classificar os dados corretamente para a inserção :)