在SQL Server中向现有表添加非空列？

Question

我已经有一个包含数据的表格，现在需要更改该表格以添加两个不为空的新列。如何在不丢失任何现有数据的情况下完成此操作？

---

以下是我的尝试过程(通过右键单击表格并选择设计)：

1. 添加新列“EmpFlag”(位, 空)，“CreatedDate”(日期时间, 空)

2. 更新表中的“EmpFlag”列，以包含一些有效值。(仅想处理一个字段，因此没有更新“CreatedDate”字段)

3. 现在右键单击表格，选择设计，将其设置为非空。

当我试图保存时，出现了以下错误信息:

不允许保存更改。您所做的更改需要删除并重新创建以下表格。

- sanjeev40084

---

我认为这是SSMS设计工具的限制。尝试使用SQL语句执行此操作：“alter table tablename alter column EmpFlag bit not null”。只需运行一个新查询，并将表名更改为实际名称即可。 - Jose Chama

这与您所做更改的具体内容关系不大，而是反映了SSMS的新默认行为-防止人们无意中对大型生产表进行灾难性更改，这将使它们离线，直到复制数据并重新填充表为止。 您可以在“工具”>“选项”>“设计师”>“表+数据库设计师”>“防止保存更改......”（取消选中此选项）中关闭此选项。 但是，在对生产表进行这些更改时一定要非常小心。 - Aaron Bertrand

Answer 1

您只需在新列中设置一个默认值，就可以添加它们。

alter table table_name
    add column_name datetime not null
       constraint DF_Default_Object_Name default (getdate())

或者对于一个varchar字段使用这个。

alter table table_name
    add column_name varchar(10) not null
       constraint DF_Default_Object_Name default ('A')

如果你添加了这一列之后不再需要默认值，你也可以删除它。

alter table table_name
    drop constraint DF_Default_Object_Name

- Jose Chama

---

请注意，如果您的填充因子为100％（默认值），那么像这样向所有行添加数据将导致大量页面分裂。这是很多I / O操作，我认为这是一个阻塞操作。对于中等大小的表格来说还好，但是如果您有一个大型和高吞吐量的表格，它基本上就成为了离线操作。据我所知，唯一以“在线”方式执行此操作的方法是创建具有所需结构的新表格；使用触发器和批处理操作合并数据；最后在单个事务中将新表格交换到位（同义词效果很好）。 - ahains

这个答案对我来说很有效，但后来我意识到我可以在SSMS的Design Table屏幕中完全相同 - 添加非空列，并在列属性中设置默认值或绑定，然后保存更改，如果必要，之后删除默认值或绑定。 - MarkD

我已经为此苦苦挣扎了多年，总是编写 SQL 代码来重新创建表并填充数据。我不知道它会这么简单。我的很多时间都被浪费了 :-@ - Drew Chapin

Answer 2

如果你不想在列上设置默认值，可以采取以下步骤：

1. 将新列创建为可为NULL
2. 适当更新现有数据
3. 添加NOT NULL约束

- Aaron Bertrand

---

总的来说我同意，但这取决于具体情况。如果你正在添加像CreatedDate和ModifiedDate这样的列，你可以应用默认值，然后回头调整旧数据（如果需要），那么你就不必担心在2和3之间出现任何停机时间（或新行）。在标准默认值有意义的情况下，这为您提供了灵活性，使您不必同步所有尚不知道新列的代码。 - Aaron Bertrand

这对于第三步 - 更改列：从null到not null非常有用。 - Dunc

Answer 3

在GUI中添加具有默认值的新非空列可以像下面这样完成。将现有列更改为非空则似乎是另一回事。我收到了与您相同的消息。一个选项是创建一个具有默认值的新非空列来替换旧列，然后将旧列数据复制到新列数据中。

1. 将表格放入设计视图中（右键单击表格->选择设计）
2. 添加列，选择数据类型
3. 取消选中允许为空并设置默认值或绑定=您的默认值，如下所示

- Tony L.