SQL Server - 向现有表添加非空列 - SSDT 发布

Question

SQL Server - 向现有表添加非空列 - SSDT 发布

18

由于业务逻辑的需要，我们需要在一个对保证始终有值至关重要的表中添加一列。因此，该列应作为NOT NULL添加到表中。与之前解释如何手动完成此操作的先前的问题不同，这需要由SSDT发布进行管理。

由于一些认识上的问题，我已经为这个听起来简单的任务苦思冥想了一段时间。

默认值不合适，也不能是计算列。也许它是一个外键列，但对于其他列，我们不能使用像0或-1这样的虚假值，因为这些值可能具有意义（例如数字数据）。
在预部署脚本中添加该列将导致发布失败，因为它会自动尝试创建相同的列两次（即使预部署脚本被编写为幂等的）_{（这真的很恼人，因为我可以想到一个简单的解决方案）}
在后部署脚本中将列更改为NOT NULL将在每次SSDT模式刷新发生时被还原（因此至少我们的代码库将在源代码控制和实际服务器上不匹配）
现在将列添加为可为空，并打算在将来更改为NOT NULL，这在源代码控制的多个分支/派生版本中无法正常工作，因为目标系统在下次升级时不一定都处于相同的状态_{（不管怎么说，我认为这不是一个好方法）}

我听说过的方法是直接更新表定义（以便模式刷新一致），编写一个预部署脚本，将整个表的内容移动到一个包含新列填充逻辑的临时表中，然后在后部署脚本中将行移回原表。虽然这种方法看起来非常冒险，并且在检测到正在向具有现有数据的表添加NOT NULL列时，发布预览仍会出现问题（因为该验证在预部署脚本之前运行）。

如何在不冒风险遗留孤立数据或在每次发布时来回移动数据的情况下添加一个新的非空列？还有没有更安全的迁移脚本？

谢谢。

- Elaskanator

1个回答

- Josh Darnell · Accepted Answer

我将分享我过去是如何做到这一点的。它旨在解决你在第二点中提到的预部署脚本的特定限制：在预部署脚本中添加列将导致发布失败，因为它会自动尝试创建相同的列两次（即使预部署脚本被编写成幂等的）。

为什么预部署脚本对此无效

当你部署一个 SSDT 项目时，它的操作方式如下（有些简化，但大致相同）：

在源（dacpac 文件）和目标（数据库）之间进行"模式比较"
根据比较结果生成一个部署脚本
处理 dacpac 中的任何预部署脚本（进行标记替换等），并将内容插入到部署脚本的开头
对于后续部署脚本，将其追加到部署脚本的末尾

当dacpac中存在一个新的列而目标数据库中不存在时，步骤#2将生成用于添加该列的代码。因此，如果预部署脚本添加了这个列，脚本的主要部分将失败（因为它基于步骤#1中模式比较的结果假设该列不存在）。

解决方案：预-SSDT脚本

Martin Smith在评论中提到了这个选项，这是迄今为止对我最有效的解决方案。

我们在部署流水线中使用预模型脚本。这不是 SSDT 的一部分，而是在 dacfx 发布之前运行的一个步骤。因此，在这种情况下，预模型脚本可以添加具有所需值的列，并使其非空，在发布发生时，它已经处于 SSDT 期望的状态，因此与 SSDT 没有任何关系。我还没有找到预部署脚本的实际用途。- Martin Smith（链接1）Jun 1 at 21:45（链接2）

一般来说，实施此解决方案的步骤如下：

在SSDT项目中创建一个脚本来保存你的"pre-SSDT" T-SQL代码
- 根据你的部署过程如何工作，这些文件中的代码应该是幂等的
确保将此脚本设置为"Build Action=None"和"Copy to Output Directory=Copy always"
- "Copy always"选项尤其重要，因为部署过程需要能够在部署构件中找到此脚本
在部署过程中，在SSDT模式比较发生之前，定位并运行此脚本（或脚本）
一旦该脚本成功执行，您可以像往常一样使用DacServices / DacFx /其他工具完成部署

最后，这使得你可以在pre-SSDT脚本中使用任何自定义代码来添加列，使用复杂的业务逻辑进行填充。

你还可以在SSDT项目中添加列定义（这样源代码控制仍然与数据库的实际状态匹配）。但是当模式比较运行时，它不会检测到与该列相关的任何更改（因为你已经部署过了）。

pre-SSDT的其他用途

我经常发现在测试部署时，SSDT会执行一个完全不必要的“表重建”操作。这是指创建一个具有更新模式的新表，将所有数据复制到该表中，删除旧表，并将新表重命名为旧表的替代品。

如果表很大，这可能导致事务日志文件的巨大增长和其他问题。如果我注意到模式更改导致此问题，我会在SSDT之前自己进行更改（通常是简单的ALTER TABLE语句），以避免表重建。

这个想法好吗？

我认为是的。如果你阅读Alex Yates的《Critiquing two different approaches to delivering databases: Migrations vs state》，这实际上是将两种方法结合起来。SSDT是基于状态的，但我们在SSDT之前加入了迁移步骤，以处理一些SSDT无法以一般方式处理的更复杂的情况。

在回答时进行一些搜索，我发现实际上这是SSDT用户社区中讨论的一种常见方法，一旦你知道要搜索什么。我看过它被称为：

- 预比较 - 预建模 - 预DAC - 预SSDT

等等。这里有一篇很好的文章，涵盖了我上面提到的许多要点： Pre-Compare & Pre-Deployment Scripts to SSDT 还有一篇来自Red Gate的文章（在“#4 - 从系统类型更改为用户定义类型”部分），也将其称为预比较： How to Fix Ten SSDT Deployment Snags, With or Without ReadyRoll 那么，预部署脚本的目的是什么呢？

Martin指出他对于“预部署脚本并没有找到太多用处”。我倾向于持相同的看法。但是在某些情况下，它们仍然可以很有用。

一个同事给我指出的一个例子是将一些数据存储在临时表中，并将其用于后部署脚本（比如说你正在将一个列从一张表移动到另一张表）。

*表格重建看起来就像这样，真是太可怕了，对吧？

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;