SQL Server - Adicionando coluna não anulável à tabela existente - Publicação SSDT

Vou compartilhar como eu fiz isso no passado. Ele foi projetado para resolver a limitação específica de scripts de pré-implantação que você chama em seu segundo ponto:

Adicionar a coluna em um script de pré-implantação falhará na publicação quando tentar criar automaticamente a mesma coluna, uma segunda vez (mesmo que o script de pré-implantação seja escrito para ser idempotente)

Por que os scripts de pré-implantação não funcionam para isso

Quando você implanta um projeto SSDT, a maneira como ele une as coisas é assim (um pouco simplificada, mas em geral):

1. Faça a "comparação de esquema" entre a fonte (arquivo dacpac) e o destino (banco de dados)
2. Gere um script de implantação com base nos resultados dessa comparação
3. Processe qualquer script de pré-implantação no dacpac (fazendo substituição de token, etc.) e insira o conteúdo no início do script de implantação
4. Faça o mesmo para scripts pós-implantação, anexando ao final do script de implantação

Quando existe uma nova coluna no dacpac e não no banco de dados de destino, a etapa 2 gerará o código para adicionar essa coluna. Portanto, se o script de pré-implantação adicionar essa coluna, a parte principal do script falhará (porque assume que a coluna não existe, com base nos resultados da comparação de esquema na etapa 1)

Solução: script pré-SSDT

Martin Smith mencionou essa opção em um comentário e é a solução que funcionou melhor para mim até agora:

Usamos scripts de pré-modelo em nosso pipeline de distribuição. Isso não é algo que faz parte do SSDT, mas uma etapa que é executada antes da publicação do dacfx. Portanto, neste caso, o script do premodel poderia adicionar a coluna com os valores desejados e torná-la não nula e, no momento da publicação, ela já está no estado esperado pelo SSDT, portanto, não tem nada a ver. Ainda estou para encontrar muita utilidade para scripts de pré-implantação. – Martin Smith 01 de junho às 21:45

Os passos para implementar esta solução em geral são:

1. Crie um script no projeto SSDT para manter seu código T-SQL "pré-SSDT"
   • Dependendo de como seu processo de implantação funciona, o código nesses arquivos provavelmente deve ser idempotente
2. Certifique-se de definir este script como "Build Action=None" e "Copy to Output Directory=Copy Always"
   • a opção "copiar sempre" é especialmente importante, pois o processo de implantação precisa encontrar esse script em seus artefatos de implantação
3. Em seu processo de implantação, localize e execute este script (ou scripts) antes que ocorra a comparação do esquema SSDT
4. Uma vez que o script foi executado com sucesso, você pode envolver DacServices / DacFx / qualquer coisa para completar sua implantação como de costume

No final, isso permite que você adicione a coluna usando qualquer código personalizado que desejar, preenchido usando lógica de negócios complexa, no script pré-SSDT.

Você também adiciona a definição de coluna no projeto SSDT (para que o controle de origem ainda corresponda ao estado real do banco de dados). Mas quando a comparação de esquema é executada, ela não vê alterações relacionadas a essa coluna (porque você já a implantou).

Outros usos do pré-SSDT

Muitas vezes, ao testar implantações, vejo que o SSDT executa uma operação de "reconstrução de tabela"* quando é completamente desnecessária. É aqui que uma nova tabela é criada com o esquema atualizado, todos os dados são copiados para essa tabela, a tabela antiga é eliminada e a nova tabela é renomeada para substituir a tabela antiga.

Isso pode levar ao crescimento massivo do arquivo de log de transações e outros problemas se a tabela for grande. Se eu perceber que uma alteração de esquema está causando isso, eu mesmo farei a alteração no pré-SSDT (que geralmente é uma ALTER TABLEinstrução simples) e evitarei a reconstrução da tabela.

isso é uma boa ideia?

Eu penso que sim. Se você ler Criticando duas abordagens diferentes para entregar bancos de dados: Migrations vs state por Alex Yates, isso é essencialmente combinar um pouco as duas abordagens. O SSDT é baseado em estado, mas incorporamos uma etapa de migração (antes do SSDT) ​​para lidar com alguns dos cenários mais complexos com os quais o SSDT simplesmente não tem como lidar de maneira geral.

Ao fazer algumas pesquisas enquanto escrevia esta resposta, essa é realmente uma abordagem muito comum discutida na comunidade de usuários do SSDT quando você sabe o que procurar. Eu vi isso chamado:

• pré-comparar
• pré-modelo
• pré-DAC
• pré-SSDT

Etc. Aqui está um ótimo artigo que cobre muitos dos pontos que mencionei acima:

Scripts de pré-comparação e pré-implantação para SSDT

E um do Red Gate (na seção #4 – Mudando do tipo de sistema para o tipo definido pelo usuário ) que também se refere a isso como pré-comparação:

Como corrigir dez problemas de implantação SSDT, com ou sem ReadyRoll

Então, qual é o objetivo dos scripts de pré-implantação?

Martin aponta que ele não encontrou " muito uso para scripts de pré-implantação ". Eu tendo a me sentir da mesma maneira. Mas há cenários em que eles podem ser úteis.

Um exemplo que um colega de trabalho apontou para mim foi armazenar alguns dados em uma tabela temporária para serem usados ​​no script de pós-implantação (digamos que você esteja movendo uma coluna de uma tabela para outra).

*A reconstrução da tabela se parece com isso, o que é horrível, certo?

GO
PRINT N'Starting rebuilding table [dbo].[MyTable]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
    [Id] BIGINT IDENTITY (1, 1) NOT NULL,
    -- etc, other columns
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[MyTable])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] ON;
        INSERT INTO [dbo].[tmp_ms_xx_MyTable] ([Id], ...)
        SELECT   [Id],
                 -- etc, other columns
        FROM     [dbo].[MyTable]
        ORDER BY [Id] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] OFF;
    END

DROP TABLE [dbo].[MyTable];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_MyTable]', N'MyTable';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;