如何将一列添加到大型 sql server 表 [英] How do I add a column to large sql server table

问题描述

我在生产中有一个包含数百万行的 SQL Server 表，结果我需要向其中添加一列.或者，更准确地说，我需要向表所代表的实体添加一个字段.

I have a SQL Server table in production that has millions of rows, and it turns out that I need to add a column to it. Or, to be more accurate, I need to add a field to the entity that the table represents.

在语法上这不是问题，如果表没有这么多行并且不在生产中，这将很容易.

Syntactically this isn't a problem, and if the table didn't have so many rows and wasn't in production, this would be easy.

我真正追求的是行动方针.有很多网站都有非常大的表格，他们必须不时添加字段.他们如何在没有大量停机的情况下做到这一点?

Really what I'm after is the course of action. There are plenty of websites out there with extremely large tables, and they must add fields from time to time. How do they do it without substantial downtime?

我应该补充的一件事，我不希望该列允许空值，这意味着我需要有一个默认值.

One thing I should add, I did not want the column to allow nulls, which would mean that I'd need to have a default value.

所以我要么需要弄清楚如何及时添加具有默认值的列，要么需要想办法在以后更新该列，然后将该列设置为不允许空值.

So I either need to figure out how to add a column with a default value in a timely manner, or I need to figure out a way to update the column at a later time and then set the column to not allow nulls.

推荐答案

ALTER TABLE table1 ADD
  newcolumn int NULL
GO

不应该花那么长时间...花很长时间是在其他列中间插入列... b/c 然后引擎需要创建一个新表并将数据复制到新表中.

should not take that long... What takes a long time is to insert columns in the middle of other columns... b/c then the engine needs to create a new table and copy the data to the new table.

这篇关于如何将一列添加到大型 sql server 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！