如何将具有默认值的列添加到SQL Server 2000/SQL Server 2005中的现有表中?


当前回答

这有很多答案,但我觉得有必要添加这个扩展方法。这看起来要长得多,但如果要向活动数据库中有数百万行的表中添加NOT NULL字段,则非常有用。

ALTER TABLE {schemaName}.{tableName}
    ADD {columnName} {datatype} NULL
    CONSTRAINT {constraintName} DEFAULT {DefaultValue}

UPDATE {schemaName}.{tableName}
    SET {columnName} = {DefaultValue}
    WHERE {columName} IS NULL

ALTER TABLE {schemaName}.{tableName}
    ALTER COLUMN {columnName} {datatype} NOT NULL

这将做的是将列添加为可空字段,并使用默认值将所有字段更新为默认值(或者您可以分配更多有意义的值),最后将列更改为NOT NULL。

这样做的原因是,如果您更新一个大型表并添加一个新的非空字段,它必须写入每一行,因此在添加列并写入所有值时将锁定整个表。

此方法将添加可为空的列,该列本身运行速度快得多,然后在设置非空状态之前填充数据。

我发现,在一个语句中完成整个操作将在4-8分钟内锁定一个更活跃的表,而且我经常会终止这个过程。这种方法每一部分通常只需几秒钟,并导致最小的锁定。

此外,如果您有一个表,其面积为数十亿行,则可能值得按如下方式批量更新:

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

    IF @@ROWCOUNT < 1000000
        BREAK;
END

其他回答

这可以通过以下代码完成。

CREATE TABLE TestTable
    (FirstCol INT NOT NULL)
    GO
    ------------------------------
    -- Option 1
    ------------------------------
    -- Adding New Column
    ALTER TABLE TestTable
    ADD SecondCol INT
    GO
    -- Updating it with Default
    UPDATE TestTable
    SET SecondCol = 0
    GO
    -- Alter
    ALTER TABLE TestTable
    ALTER COLUMN SecondCol INT NOT NULL
    GO
ALTER TABLE <YOUR_TABLENAME>
ADD <YOUR_COLUMNNAME> <DATATYPE> <NULL|NOT NULL> 
ADD CONSTRAINT <CONSTRAINT_NAME>   ----OPTIONAL
DEFAULT <DEFAULT_VALUE>

如果您没有提供约束名称,那么SQLServer将使用默认名称。

示例:-

ALTER TABLE TEMP_TABLENAME
ADD COLUMN1 NUMERIC(10,0) NOT NULL
ADD CONSTRAINT ABCDE   ----OPTIONAL
DEFAULT (0)

步骤1.首先,您必须使用添加字段来更改表

alter table table_name add field field_name data_type

步骤2创建默认值

USE data_base_name;
GO
CREATE DEFAULT default_name AS 'default_value';

步骤3然后您必须执行此过程

exec sp_bindefault 'default_name' , 'schema_name.table_name.field_name'

示例-

USE master;
GO
EXEC sp_bindefault 'today', 'HumanResources.Employee.HireDate';
--Adding New Column with Default Value
ALTER TABLE TABLENAME 
ADD COLUMNNAME DATATYPE NULL|NOT NULL DEFAULT (DEFAULT_VALUE)

OR

--Adding CONSTRAINT And Set Default Value on Column
ALTER TABLE TABLENAME ADD  CONSTRAINT [CONSTRAINT_Name]  DEFAULT 
(DEFAULT_VALUE) FOR [COLUMNNAME]

如果要添加多个列,可以通过以下方式进行:

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