在SQL Server中向现有表添加带默认值的列的方法

技术背景

在数据库管理中，随着业务需求的变化，常常需要对现有的数据库表结构进行调整。其中，向现有表添加一个带有默认值的列是一种常见操作。例如，在已有的用户信息表中添加一个新的字段来记录用户的注册时间，并且为该字段设置默认值为当前时间。

实现步骤

基本语法

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

示例

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

详细说明

可选约束名称：如果省略 CONSTRAINT D_SomeTable_SomeCol，SQL Server 将自动生成一个默认约束，名称可能类似 DF__SomeTa__SomeC__4FB7FEF6。
可选的 WITH VALUES 语句：只有当列是可空的，并且你希望对现有记录使用默认值时，才需要 WITH VALUES。如果列是 NOT NULL，则无论是否指定 WITH VALUES，都会自动对所有现有记录使用默认值。
默认约束下的插入操作：
- 如果向 SomeTable 插入一条记录，并且未指定 SomeCol 的值，则它将默认为 0。
- 如果插入记录时指定 SomeCol 的值为 NULL（且列允许为空），则不会使用默认约束，而是将 NULL 插入作为值。

核心代码

添加单列

-- 添加一个可为空的列并设置默认值
ALTER TABLE table_name
ADD column_name BIT
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES

-- 添加一个非空列并设置默认值
ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0

添加多列

ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'
GO

更完善的脚本（包含检查列和约束是否存在）

-- 删除列
IF EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'table_Emplyee'
              AND COLUMN_NAME = 'Column_EmployeeName'
           )
    BEGIN

        IF EXISTS ( SELECT 1
                    FROM sys.default_constraints
                    WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]')
                      AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
                  )
            BEGIN
                ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
                PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'
            END
        ALTER TABLE [dbo].table_Emplyee
            DROP COLUMN Column_EmployeeName
        PRINT 'Column Column_EmployeeName in images table was dropped'
    END

-- 添加列
IF NOT EXISTS (
                SELECT 1
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'table_Emplyee'
                  AND COLUMN_NAME = 'Column_EmployeeName'
               )
    BEGIN
        ALTER TABLE dbo.table_Emplyee
            ADD Column_EmployeeName BIT   NOT NULL
            CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]  DEFAULT (0)
        PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added'
        PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'
     END

GO

处理大数据量表添加非空列

-- 1. 添加可空列并设置默认值
ALTER TABLE {schemaName}.{tableName}
    ADD {columnName} {datatype} NULL
    CONSTRAINT {constraintName} DEFAULT {DefaultValue}

-- 2. 更新现有记录的值
UPDATE {schemaName}.{tableName}
    SET {columnName} = {DefaultValue}
    WHERE {columName} IS NULL

-- 3. 将列设置为非空
ALTER TABLE {schemaName}.{tableName}
    ALTER COLUMN {columnName} {datatype} NOT NULL

批量更新大数据量表

WHILE 1=1
BEGIN
    UPDATE TOP (1000000) {schemaName}.{tableName}
        SET {columnName} = {DefaultValue}
        WHERE {columName} IS NULL

    IF @@ROWCOUNT < 1000000
        BREAK;
END