Início / dba / Perguntas / 159229
Accepted
Felix Pamittan
Asked: 2016-12-27 20:54:17 +0800 CST

Alterando a coluna de identidade de INT para BIGINT

  • 772

Eu tenho uma tabela com uma coluna de identidade que também é uma chave primária. Atualmente, possui 50 milhões de linhas, com o valor mais alto da coluna de identidade em 148.921.803. A mesa tem muitos DELETEse INSERTSrealizados nela, daí o alto valor.

Queremos alterar o tipo de dados de INTpara BIGINTpara preparar a adição de mais linhas. Observe que não há referências à coluna PK.

Qual é a melhor maneira de fazer isso, com o mínimo de tempo de inatividade? Eu tenho duas opções.

  1. Solte o PK e altere a coluna; ou
  2. O método copy-drop-rename, conforme descrito aqui :
sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Como há uma chave primária definida na coluna de identidade, você não poderá alterar diretamente esta coluna.

    Ambas as abordagens que você mencionou em sua pergunta podem ser usadas e o tempo de inatividade depende do desempenho do servidor e do número de linhas que residem nessa tabela.

    1. Solte o PK e altere a coluna; ou

    Primeiro solte o PK

    /****** Object: DROP Index [PK_DatabaseLog_DatabaseLogID]******/

ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_TableName_ID]
GO

    Alterar coluna

    ALTER TABLE [dbo].[TableName] ALTER COLUMN [dbo.ID] BIGINT

    Adicionar chave primária

    /****** Object: ADD Index [PK_DatabaseLog_DatabaseLogID]******/
ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [PK_TableName_ID] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)

    Essa abordagem geralmente não leva muito tempo. No meu ambiente, leva alguns segundos em grandes tabelas com mais de 5 milhões de linhas.

    1. O método copy-drop-rename, conforme descrito

    Você também pode usar essa abordagem. No entanto, para essa abordagem, você precisa de mais tempo de inatividade do que o Approach, pois precisa sincronizar as tabelas.

    • 15

  2. Aaron Bertrand tem uma série de 4 partes sobre este tópico, começando com:

    Minimizando o impacto do alargamento de uma coluna IDENTITY – parte 1

    Se você realmente precisa mudar para o bigint, deve minimizar o tempo de inatividade e tem bastante tempo para planejar, a abordagem que ele documenta na parte 4 é:

    Em um nível muito alto, a abordagem é criar um conjunto de tabelas sombra, onde todas as inserções são direcionadas para uma nova cópia da tabela (com o tipo de dados maior), e a existência dos dois conjuntos de tabelas é tão transparente possível para o aplicativo e seus usuários.

    Mais detalhadamente, Aaron diz:

    1. Crie cópias de sombra das tabelas, com os tipos de dados corretos.
    2. Altere os procedimentos armazenados (ou código ad hoc) para usar bigint para parâmetros. (Isso pode exigir modificações além da lista de parâmetros, como variáveis ​​locais, tabelas temporárias, etc., mas não é o caso aqui.)
    3. Renomeie as tabelas antigas e crie exibições com os nomes que unem as tabelas antigas e novas.
      • Essas exibições terão em vez de gatilhos para direcionar adequadamente as operações DML para as tabelas apropriadas, para que os dados ainda possam ser modificados durante a migração.
      • Isso também requer que SCHEMABINDING seja descartado de quaisquer exibições indexadas, exibições existentes tenham uniões entre tabelas novas e antigas e procedimentos que dependem de SCOPE_IDENTITY() sejam modificados.
    4. Migre os dados antigos para as novas tabelas em partes.
    5. Limpeza, composta por:
      • Eliminando as visualizações temporárias (que eliminarão os gatilhos INSTEAD OF).
      • Renomeando as novas tabelas de volta para os nomes originais.
      • Corrigindo os procedimentos armazenados para reverter para SCOPE_IDENTITY().
      • Descartando as mesas antigas, agora vazias.
      • Colocando SCHEMABINDING de volta em exibições indexadas e recriando índices clusterizados.
    • 12

relate perguntas