SQL Server ALTER TABLE ADD列概述

In this article, we will explore SQL Server ALTER TABLE ADD Column statements to add column(s) to an existing table. We will also understand the impact of adding a column with a default value and adding and updating the column with a value later on larger tables.

在本文中，我们将探索SQL Server ALTER TABLE ADD Column语句，以将列添加到现有表中。 我们还将理解在较大的表上添加具有默认值的列以及添加和更新具有值的列的影响。

As a database developer, you need to add columns to the existing tables too offen. You would think that adding a column to the SQL Server database table would not be a major deal. Sometimes you might be adding the column from the SQL Server Management Studio itself. Well, that would be fine for a small table or a table which does not have a large number of transactions. Let’s go ahead and see how we can add columns to an existing table and also understand the reasons and the best practices to add a column to a table that is large in size.

作为数据库开发人员，您还需要向现有表中添加列。 您可能会认为向SQL Server数据库表中添加列并不是一件大事。 有时您可能是从SQL Server Management Studio本身添加该列。 好吧，这对于较小的表或没有大量事务的表会很好。 让我们继续前进，看看如何将列添加到现有表中，并了解向大型表添加列的原因和最佳实践。

样品表 (Sample Table)

Let us create a sample table with sufficient data set from the following script.

让我们用以下脚本创建一个具有足够数据集的示例表。

IF EXISTS (SELECT 1 FROM Sys.tables where Name = 'SampleTable')
DROP TABLE SampleTable
 
CREATE TABLE dbo.SampleTable (
   ID BIGINT IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
   DateTime4 DATETIME DEFAULT GETDATE(),
   Column1 CHAR(1000) DEFAULT 'MMMMMMMMMMMMMMMMM',
   Column2 CHAR(2000) DEFAULT 'YYYYYYYYYYYYYYYYY'
)

The above script will create a sample table called SampleTable. Data fields are added so that the large size table will be created. Next, a large number of records were added by executing the following query multiple times.

上面的脚本将创建一个名为SampleTable的示例表。 添加了数据字段，以便创建大型表。 接下来，通过多次执行以下查询来添加大量记录。

SET NOCOUNT ON
INSERT INTO SampleTable
(DateTime4, Column1, Column2)
VALUES
(GETDATE(),'XXXX','YYYY')
GO 500000

After the above query is executed, 500,000 records are updated to the SampleTable. After executing the above query following is the table size and other parameters for the table.

执行以上查询后，将500,000条记录更新到SampleTable 。 执行上述查询后，下面是表的大小和该表的其他参数。

This can be retrieved by sp_spaceused ‘SampleTable’

可以通过sp_spaceused'SampleTable'进行检索

The following is the database size for data and the log file.

以下是数据和日志文件的数据库大小。

Let’s quickly go over the syntax of adding one column to an existing table by using ALTER TABLE ADD statement as shown below.

让我们快速遍历使用ALTER TABLE ADD语句向现有表中添加一列的语法，如下所示。

ALTER TABLE tbl_name
ADD Col_name  data_type  col_constraint;

You can use the below statement to add column NewColumn1 to our table SampleTable.

您可以使用以下语句将NewColumn1列添加到我们的表SampleTable中。

ALTER TABLE SampleTable
ADD NewColumn1 varchar(200)

Also, you can add multiple columns to a table using the single SQL Server ALTER TABLE statement as below.

另外，您可以使用单个SQL Server ALTER TABLE语句将多个列添加到表中，如下所示。

ALTER TABLE SampleTable
ADD NewColumn2 varchar(20),
NewColumn3 varchar(50)

When adding a column to a large table, typically you want to add the column and fill the added column with a default value. For example, if you want to add a status column, next is to fill the column with a default value.

将列添加到大型表时，通常需要添加该列，并使用默认值填充添加的列。 例如，如果要添加状态列，则下一步是用默认值填充该列。

添加具有默认约束的列 (Adding a Column with a Default Constraints)

To achieve the above objective, the easiest way is to add a column with a default constraint. When the default column is added, the default value will be added to the table. The following script makes use of SQL Server ALTER TABLE ADD Column (Status in our case) statement to add a column named Status with default constraint.

为了实现上述目的，最简单的方法是添加具有默认约束的列。 添加默认列后，默认值将添加到表中。 以下脚本利用SQL Server ALTER TABLE ADD Column（在本例中为Status）语句添加具有默认约束的名为Status的列。

ALTER TABLE SampleTable 
ADD Status CHAR(5000) 
DEFAULT 'INC' WITH VALUES

When the column is added as above, the Status column will be added with the Value INC for all records.

当按上述方式添加列时，将为所有记录添加“状态”列和“值INC”。

From the profiler following statics are captured via SQL Profiler during the column addition with default values.

在添加默认值的列期间，通过SQL事件探查器从事件探查器捕获以下静态信息。

CPU	0
Reads	185
Writes	0
Duration Mille Seconds	65

中央处理器	0
读	185
写	0
持续时间毫秒	65

You can see that the column is added to the table even less than one second and operation is very minimal cost.

您可以看到该列添加到表中的时间甚至不到一秒钟，并且操作成本非常低。

The following are the locking stats during the column that are added with a constraint.

以下是在列期间添加了约束的锁定状态。

Resource Type	Resource Subtype	Request Mode	Records Count
DATABASE		S	1
DATABASE	DDL	S	1
KEY		X	11
METADATA	DATA_SPACE	Sch-S	1
OBJECT		IX	6
OBJECT		Sch-M	2

资源类型	资源子类型	请求模式	记录数
数据库		小号	1个
数据库	DDL	小号	1个
键		X	11
元数据	DATA_SPACE	Sch-S	1个
目的		九	6
目的		Sch-M	2

Please note that the following query should be executed in an open transaction in order to capture the above locking statistics.

请注意，以下查询应在打开的事务中执行，以捕获上述锁定统计信息。

SELECT
resource_type,
resource_subtype,
request_mode,
COUNT(*) FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
GROUP BY resource_type,
resource_subtype,
request_mode

This shows that Table (Object) has intended Exclusive lock which means that the table is not exclusively locked during the addition of the column. Also, adding a column with default value has not taken even a one minute though it has 500,000 records.

这表明表（对象）具有预期的排他锁，这意味着在添加列期间表未排它锁。 同样，添加具有默认值的列即使有500,000条记录，也花了一分钟的时间。

Let us see the table size.

让我们看看桌子的大小。

You will see that nothing has changed.

您会看到什么都没有改变。

Let us see the file sizes of the database.

让我们看看数据库的文件大小。

Nothing much has changed to the data file as well as for the log file. All of these results indicate that adding a column with a default constraint will result in only a metadata change.

数据文件和日志文件没有任何变化。 所有这些结果表明，添加具有默认约束的列只会导致元数据发生变化。

用值更新 (Update with a Value)

Let us update the same column with a different value and let us get the same stats.

让我们用不同的值更新同一列，让我们获得相同的统计信息。

Resource Type	Request Mode	Records Count
DATABASE	S	1
OBJECT	X	1

资源类型	请求模式	记录数
数据库	小号	1个
目的	X	1个

The above table shows that the table is exclusively locked which means that the table is not accessible during the update.

上表显示该表被独占锁定，这意味着该表在更新期间不可访问。

Let us look at the table size.

让我们看看表的大小。

The table has grown by some value as shown in the above figure.

该表已增长了一些值，如上图所示。

The following are the details for the query expenses captured from the SQL Profiler.

以下是从SQL事件探查器捕获的查询费用的详细信息。

CPU	29,781
Reads	9,188,986
Writes	527,436
Duration Mille Seconds	1,113,142

中央处理器	29,781
读	9,188,986
写	527,436
持续时间毫秒	1,113,142

Evidently, when updating a column for a large table, resource consumption is high.

显然，当更新大表的列时，资源消耗很高。

The following is the database file sizes when the column is added and updated the values.

以下是添加和更新列时的数据库文件大小。

These stats show that there is a remarkable difference between adding a column with a default value and adding a column and updating the column with a value later. During the column update, the transaction log will grow and exclusive locking will be placed on the table prohibiting any reads or writes to the table.

这些统计数据表明，添加具有默认值的列与添加列以及稍后更新具有值之间的区别显着。 在列更新期间，事务日志将增长，并且排他锁将放置在表上，以禁止对该表进行任何读取或写入。

What is the difference in these scenarios? Prior to the SQL Server 2012, when adding a column with default value will cause the same behavior. In SQL Server 2012 and onwards, this is no longer the situation, the column is added online to the table and no update occurs and it is only a metadata change.

在这些情况下有什么区别？ 在SQL Server 2012之前，添加具有默认值的列将导致相同的行为。 在SQL Server 2012及更高版本中，这种情况不再存在，该列已在线添加到表中，并且没有更新发生，而只是元数据更改。

如何做到这一点 (How This is Achieved)

This is achieved by a somewhat very simple but novel approach. sys.system_internals_partition_columns DMV has two additional columns named has_default and default_value as shown below.

这是通过某种非常简单但新颖的方法来实现的。 sys.system_internals_partition_columns DMV具有另外两个名为has_default和default_value的列，如下所示。

So when the column added with a default value, it will not update the data page instead it will update this system table. When a row is updated, then the default value will be pushed to the table even if the default value column is not updated.

因此，当添加默认值的列时，它将不会更新数据页，而是会更新此系统表。 当更新一行时，即使默认值列未更新，默认值也会被推送到表中。

Now, the next question is what if the Default constraint is dropped just after it is created. In that scenario, still, the above setting will prevail hence the previously set default value will be kept.

现在，下一个问题是如果在创建默认约束后立即删除默认约束。 在这种情况下，仍然会使用上述设置，因此将保留先前设置的默认值。

结论
(Conclusion
)

We covered the basic syntax of SQL Server ALTER TABLE in this article and implemented it to add columns to an existing table.

我们在本文中介绍了SQL Server ALTER TABLE的基本语法，并实现了将其添加到现有表中的列。

Before SQL Server 2012, there was no difference between adding a column with the default and adding a column and updating it. Therefore, in the prior SQL Server 2012 era, rather than adding a column with a default constraint, it is better to add a column and updating with batches so that the table is not exclusively locked.

在SQL Server 2012之前，添加具有默认值的列与添加列并对其进行更新之间没有区别。 因此，在以前SQL Server 2012时代，与其添加具有默认约束的列，不如添加一个列并进行批处理更新，以使该表不被排他地锁定，这更好。

However, from SQL Server 2012 onwards, the approach has changed so that adding a column with default constraints is much better.

但是，从SQL Server 2012起，此方法已更改，因此添加具有默认约束的列要好得多。

翻译自: https://www.sqlshack.com/sql-server-alter-table-add-column-overview/