例如:
修改(列名前 要有column关键字)
ALTER TABLE [USER] ALTER column [NAME] varchar(35) null
新增
ALTER TABLE [USER] ADD [PRICE] numeric(18, 8) NULL DEFAULT 0

通过更改、添加、除去列和约束或者通过启用或禁用约束和触发器来更改表定义
语法
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ _data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constra > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constra_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constra_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}
< column_definition > ::=
{ column_name data_type }
[ [ DEFAULT constant_expression ] [ WITH VALUES ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL ]
[ COLLATE < collation_name > ]
[ < column_constra > ] [ ...n ]
< column_constra > ::=
[ CONSTRAINT constra_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ _disibledevent=> [ CONSTRAINT constra_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ _disibledevent=>如果 ALTER COLUMN 中指定了 NULL 或 NOT NULL那么必须同时指定 _data_type [(precision [, scale ])]如果不更改数据类型、精度和小数位数请指定列这些值当前值
[ {ADD | DROP} ROWGUIDCOL ]
指定在指定列上添加或除去 ROWGUIDCOL 属性ROWGUIDCOL 是个关键字表示列是行全局唯标识符列对于每个表只能指派个 uniqueidentier 列作为 ROWGUIDCOL 列ROWGUIDCOL 属性只能指派给 uniqueidentier 列
ROWGUIDCOL 属性并不强制列中所存储值唯性该属性也不会为插入到表中新行自动生成值若要为每列生成唯值那么或者在 INSERT 语句中使用 NEWID 或者将 NEWID 指定为该列默认值
ADD
指定要添加个或多个列定义、计算列定义或者表约束
computed_column_expression
是个定义计算列值表达式计算列是并不物理地存储在表中虚拟列该列用表达式计算得出该表达式使用同表中其它列例如计算列定义可以是:cost AS price * qty表达式可以是非计算列列名、常量、、变量也可以是用个或多个运算符连接上述元素任意组合表达式不能为子查询
计算列可用于选择列表、WHERE 子句、ORDER BY 字句或其它任何可以使用常规表达式位置但下列情况除外:
计算列不能用作 DEFAULT 或 FOREIGN KEY 约束定义也不能和 NOT NULL 约束定义起使用但是如果计算列由具有确定性表达式定义并且索引列中允许计算结果数据类型则可将该列用作索引中键列或用作 PRIMARY KEY 或 UNIQUE 约束部分
例如如果表中有整数列 a 和 b那么计算列 a+b 上可建立索引而计算列 a+DATEPART(dd, GETDATE) 上则不能该值将在后续时更改
计算列不能作为 INSERT 或 UPDATE 语句目标

介绍说明 由于表中计算列所用列中各行可能有区别值所以计算列每行可能有区别值
n
是表示前面项可重复 n 次占位符
WITH CHECK | WITH NOCHECK
指定表中数据是否用新添加或重新启用 FOREIGN KEY 或 CHECK 约束进行验证如果没有指定对于新约束假定为 WITH CHECK对于重新启用约束假定为 WITH NOCHECK
WITH CHECK 和 WITH NOCHECK 子句不能用于 PRIMARY KEY 和 UNIQUE 约束
如果不想用新 CHECK 或 FOREIGN KEY 约束对现有数据进行验证请用 WITH NOCHECK除了个别情况不建议这样使用新约束将在以后所有更新中生效任何在添加约束时由 WITH NOCHECK 抑制约束违规都可能导致将来更新失败如果这些更新操作要更新行中包含不符合约束条件数据
查询优化器不考虑用 WITH NOCHECK 定义约束将忽略这些约束直到使用 ALTER TABLE table CHECK CONSTRAINT ALL语句重新启用这些约束为止
DROP { [CONSTRAINT] constra_name | COLUMN column_name }
指定从表中删除 constra_name 或者 column_name如果兼容级别小于或等于 65将不允许 DROP COLUMN可以列出多个列或约束下面列不能除去:
被复制列

用在索引中列

用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束中列

有相关联默认值(由 DEFAULT 关键字定义)列或绑定到默认对象列

绑定到规则列
{ CHECK | NOCHECK} CONSTRAINT
指定启用或禁用 constra_name如果禁用将来插入或更新该列时将不用该约束条件进行验证此选项只能和 FOREIGN KEY 和 CHECK 约束起使用
ALL
指定使用 NOCHECK 选项禁用所有约束或者使用 CHECK 选项启用所有约束
{ENABLE | DISABLE} TRIGGER
指定启用或禁用 trigger_name当个触发器被禁用时它对表定义依然存在；然而当在表上执行 INSERT、UPDATE 或 DELETE 语句时触发器中操作将不执行除非重新启用该触发器
ALL
指定启用或禁用表中所有触发器
trigger_name
指定要启用或禁用触发器名称
column_name data_type
新列数据类型data_type 可以是任何 Microsoft® SQL Server™ 数据类型或用户定义数据类型
DEFAULT
是指定列默认值关键字DEFAULT 定义可用于为表中现有行新列提供值DEFAULT 定义不能添加到具有 timestamp 数据类型、IDENTITY 属性、现有 DEFAULT 定义或绑定默认值列如果列已有默认值必须除去旧默认值后才能添加新默认值为同 SQL Server 先前版本保持兼容性向 DEFAULT 赋予约束名是可能
IDENTITY
指定新列是标识列在表中添加新行时SQL Server 为列提供个唯增量值标识列通常和 PRIMARY KEY 约束起用作表唯行标识符IDENTITY 属性可赋予 tiny、small、、big、decimal(p,0) 或者 numeric(p,0) 列对于每个表只能创建个标识列DEFAULT 关键字和绑定默认值不能用于标识列要么种子和增量都同时指定要么都不指定如果 2者都未指定则取默认值 (1,1)
Seed
是用于表中所装载第行值
Increment
是添加到前行标识值增量值
NOT FOR REPLICATION
指定当复制登录(如 sqlrepl)向表中插入数据时不强制 IDENTITY 属性也可对约束指定 NOT FOR REPLICATION当复制登录向表中插入数据时不检查约束条件
CONSTRAINT
指定 PRIMARY KEY、UNIQUE、FOREIGN KEY 或 CHECK 约束开始或者指定 DEFAULT 定义开始
constrain_name
是新约束约束名称必须符合标识符规则但其名称首不能为 #如果没有提供 constra_name约束使用系统生成名称
PRIMARY KEY
是通过唯索引对给定列或多列强制实体完整性约束对每个表只能创建个 PRIMARY KEY 约束
UNIQUE
是通过唯索引为给定列或多列提供实体完整性约束
CLUSTERED | NONCLUSTERED
指定为 PRIMARY KEY 或 UNIQUE 约束创建聚集或非聚集索引PRIMARY KEY 约束默认为 CLUSTERED；UNIQUE 约束默认为 NONCLUSTERED
如果表中已存在聚集约束或索引那么在 ALTER TABLE 中就不能指定 CLUSTERED如果表中已存在聚集约束或索引PRIMARY KEY 约束默认为 NONCLUSTERED
WITH FILLFACTOR = fillfactor
指定 SQL Server 存储索引数据时每个索引页充满程度用户指定 fillfactor 取值范围从 1 到 100如果没有指定那么默认值为 0创建索引时fillfactor 值越低不必分配新空间即可添加新索引条目可用空间就越多
ON {filegroup | DEFAULT}
指定为约束创建索引存储位置如果指定了 filegroup索引将在该文件组内创建如果指定了 DEFAULT索引将在默认文件组内创建如果未指定 ON索引将在表所在文件组内创建当为 PRIMARY KEY 或 UNIQUE 约束添加聚集索引时如果指定了 ON那么创建聚集索引时整个表都将移到指定文件组中
在这里DEFAULT 不是个关键字DEFAULT 是默认文件组标识符必须用符号界定如 ON "DEFAULT" 或 ON [DEFAULT]
FOREIGN KEY...REFERENCES
是为列中数据提供引用完整性约束FOREIGN KEY 约束要求列中每个值在被引用表指定列中都存在
ref_table
是 FOREIGN KEY 约束所引用表
ref_column
是新 FOREIGN KEY 约束所引用列或多列(置于括号中)
ON DELETE {CASCADE | NO ACTION}
指定当表中被更改行具有引用关系并且该行所引用行从父表中删除时要对被更改行采取操作默认设置为 NO ACTION
如果指定 CASCADE则从父表中删除被引用行时也将从引用表中删除引用行如果指定 NO ACTIONSQL Server 将产生个并回滚父表中行删除操作
如果表中已存在 ON DELETE INSTEAD OF 触发器那么就不能定义 ON DELETE CASCADE 操作
例如在 Northwind 数据库中Orders 表和 Customers 表的间有引用关系Orders.CustomerID 外键引用 Customers.CustomerID 主键
如果对 Customers 表某行执行 DELETE 语句并且为 Orders.CustomerID 指定 ON DELETE CASCADE 操作则 SQL Server 将在 Orders 表中检查是否有和被删除行相关行或多行如果存在相关行那么 Orders 表中相关行将随 Customers 表中被引用行同删除
反的如果指定 NO ACTION若在 Orders 表中至少有行引用 Customers 表中要删除行则 SQL Server 将产生个并回滚 Customers 表中删除操作
ON UPDATE {CASCADE | NO ACTION}
指定当表中被更改行具有引用关系并且该行所引用行在父表中更新时要对被更改行采取操作默认设置为 NO ACTION
如果指定 CASCADE则在父表中更新被引用行时也将在引用表中更新引用行如果指定 NO ACTIONSQL Server 将产生个并回滚父表中行更新操作
如果表中已存在 ON DELETE INSTEAD OF 触发器那么就不能定义 ON DELETE CASCADE 操作
例如在 Northwind 数据库中Orders 表和 Customers 表的间有引用关系Orders.CustomerID 外键引用 Customers.CustomerID 主键
如果对 Customers 表某行执行 UPDATE 语句并且为 Orders.CustomerID 指定 ON UPDATE CASCADE 操作则 SQL Server 将在 Orders 表中检查是否有和被更新行相关行或多行如果存在相关行那么 Orders 表中相关行将随 Customers 表中被引用行同更新
反的如果指定了 NO ACTION若在 Orders 表中至少存在行引用 Customers 表中要更新行那么 SQL Server 将引发个并回滚 Customers 表中更新操作
[ASC | DESC]
指定加入到表约束中列或多列排序次序默认设置为 ASC
WITH VALUES
指定在添加到现有行新列中存储 DEFAULT constant_expression 中所给定值只有在 ADD 列子句中指定了 DEFAULT 情况下才能使用 WITH VALUES如果要添加列允许空值且指定了 WITH VALUES那么将在现有行新列中存储默认值如果没有指定 WITH VALUES 且列允许空值那么将在现有行新列中存储 NULL 值如果新列不允许空值那么不论是否指定 WITH VALUES都将在现有行新列中存储默认值
column[,...n]
是新约束所用列或多列(置于括号中)
constant_expression
是用作列默认值字面值、NULL 或者系统
FOR column
指定和表级 DEFAULT 定义相关联列
CHECK
是通过限制可输入到列或多列中可能值强制域完整性约束
logical_expression
是用于 CHECK 约束返回 TRUE 或 FALSE 逻辑表达式用于 CHECK 约束 Logical_expression 不能引用其它表但可引用同表中同行其它列
注释
若要添加新数据行请使用 INSERT 语句若要删除数据行请使用 DELETE 或 TRUNCATE TABLE 语句若要更改现有行中值请使用 UPDATE 语句
ALTER TABLE 语句指定更改将立即实现如果这些更改需要修改表中行ALTER TABLE 将更新这些行ALTER TABLE 将获取表上架构修改锁以确保在更改期间其它连接不能引用该表(甚至不能引用其元数据)对表进行更改将记录于日志中并且可以完全恢复影响非常大表中所有行更改比如除去列或者用默认值添加 NOT NULL 列可能需要较长时间才能完成并会生成大量日志记录如同影响大量行 INSERT、UPDATE 或者 DELETE 语句样这类 ALTER TABLE 语句也应小心使用
如果过程高速缓存Cache中存在引用该表执行计划ALTER TABLE 会将这些执行计划标记为下次执行时重新编译
如果 ALTER TABLE 语句指定更改其它表所引用列值那么根据引用表中 ON UPDATE 或者 ON DELETE 所指定操作将发生以下两个事件的
如果在引用表中没有指定值或指定了 NO ACTION(默认值)那么 ALTER TABLE 语句导致更改父表中被引用列操作将回滚并且 SQL Server 将引发个

如果在引用表中指定了 CASCADE那么由 ALTER TABLE 语句导致对父表更改将应用于父表及其相关表
添加 sql_variant 列 ALTER TABLE 语句会生成下列警告:
The total row size (xx) for table 'yy' exceeds the maximum number of s per row (8060). Rows that exceed the maximum number of s will not be added.
sql_variant 最大长度为 8016 个字节所以产生该警告当某 sql_variant 列所含值接近最大长度时即会超过行长度最大字节限制
ALTER TABLE 语句对具有架构绑定视图表执行时所受限制和当前在更改具有简单索引表时所受限制相同添加列是允许但是不允许删除或更改参和架构绑定视图表中列如果 ALTER TABLE 语句要求更改用在架构绑定视图中列更改操作将失败并且 SQL Server 将引发条信息
创建引用表架构绑定视图不会影响在基表上添加或删除触发器
当除去约束时作为约束部分而创建索引也将除去而通过 CREATE INDEX 创建索引必须使用 DROP INDEX 语句来除去DBCC DBREINDEX 语句可用来重建约束定义索引部分；而不必使用 ALTER TABLE 先除去再重新添加约束
必须删除所有基于列索引和约束后才能删除列
添加约束时所有现有数据都要进行约束违规验证如果发生违规ALTER TABLE 语句将失败并返回个
当在现有列上添加新 PRIMARY KEY 或 UNIQUE 约束时该列中数据必须唯如果存在重复值ALTER TABLE 语句将失败当添加 PRIMARY KEY 或 UNIQUE 约束时WITH NOCHECK 选项不起作用
每个 PRIMARY KEY 和 UNIQUE 约束都将生成个索引UNIQUE 和 PRIMARY KEY 约束数目不能导致表上非聚集索引数目大于 249聚集索引数目大于 1
如果要添加列数据类型为 uniqueidentier那么该列可以使用 NEWID 作为默认值以向表中现有行新列提供唯标识符值
SQL Server 在列定义中并不强制以特定顺序指定 DEFAULT、IDENTITY、ROWGUIDCOL 或列约束
ALTER TABLE ALTER COLUMN 子句并不会在列上绑定或取消绑定任何规则必须分别使用 sp_bindrule 或 sp_unbindrule 来绑定或取消绑定规则
可将规则绑定到用户定义数据类型然后 CREATE TABLE 将自动在以该用户定义数据类型定义列上绑定该规则当用 ALTER COLUMN 更改列数据类型时并不会取消绑定这些规则原用户定义数据类型上规则仍然绑定在该列上在 ALTER COLUMN 更改了列数据类型的后随后执行任何从该用户定义数据类型上取消绑定规则 sp_unbindrule 都不会导致从更改了数据类型列上取消绑定该规则如果 ALTER COLUMN 将列数据类型更改为绑定了规则用户定义数据类型那么绑定到新数据类型规则不会绑定到该列
权限
ALTER TABLE 权限默认授予表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员且不可转让
举例
A. 更改表以添加新列
下例添加个允许空值列而且没有通过 DEFAULT 定义提供值各行新列中值将为 NULL
CREATE TABLE doc_exa ( column_a INT) GOALTER TABLE doc_exa ADD column_b VARCHAR(20) NULLGOEXEC sp_help doc_exaGODROP TABLE doc_exaGO
B. 更改表以除去列
下例修改表以删除列
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) GOALTER TABLE doc_exb DROP COLUMN column_bGOEXEC sp_help doc_exbGODROP TABLE doc_exbGO
C. 更改表以添加具有约束列
下例向表中添加具有 UNIQUE 约束新列
CREATE TABLE doc_exc ( column_a INT) GOALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUEGOEXEC sp_help doc_excGODROP TABLE doc_excGO
D. 更改表以添加未验证约束
下例向表中现有列上添加约束该列中存在个违反约束值；因此利用 WITH NOCHECK 来防止对现有行验证约束从而允许该约束添加
CREATE TABLE doc_exd ( column_a INT) GOINSERT INTO doc_exd VALUES (-1)GOALTER TABLE doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1)GOEXEC sp_help doc_exdGODROP TABLE doc_exdGO
E. 更改表以添加多个带有约束列
下例向表中添加多个带有约束新列第个新列具有 IDENTITY 属性；表中每行标识列都将具有递增新值
CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) GOALTER TABLE doc_exe ADD /* Add a PRIMARY KEY identity column. */ column_b INT IDENTITYCONSTRAINT column_b_pk PRIMARY KEY, /* Add a column referencing another column in the same table. */ column_c INT NULL CONSTRAINT column_c_fk REFERENCES doc_exe(column_a),/* Add a column with a constra to enforce that */ /* nonnull data is in a valid phone number format. */column_d VARCHAR(16) NULL CONSTRAINT column_d_chkCHECK (column_d IS NULL OR column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" ORcolumn_d LIKE"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),/* Add a nonnull column with a default. */ column_e DECIMAL(3,3)CONSTRAINT column_e_defaultDEFAULT .081GOEXEC sp_help doc_exeGODROP TABLE doc_exeGO
F. 添加具有默认值可为空列
下例添加可为空、具有 DEFAULT 定义列并使用 WITH VALUES 为表中各现有行提供值如果没有使用 WITH VALUES那么每行新列中都将具有 NULL 值
ALTER TABLE MyTable ADD AddDate smalldatetime NULLCONSTRAINT AddDateDfltDEFAULT getdate WITH VALUES
G. 禁用并重新启用个约束
下例禁用用于限制可接受薪水数据约束WITH NOCHECK CONSTRAINT 和 ALTER TABLE 起使用以禁用该约束并使正常情况下会引起约束违规插入操作得以执行WITH CHECK CONSTRAINT 重新启用该约束
CREATE TABLE cnst_example (id INT NOT NULL, name VARCHAR(10) NOT NULL, salary MONEY NOT NULL CONSTRAINT salary_cap CHECK (salary < 100000))-- Valid insertsINSERT INTO cnst_example VALUES (1,"Joe Brown",65000)INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)-- This insert violates the constra.INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)-- Disable the constra and try again.ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_capINSERT INTO cnst_example VALUES (3,"Pat Jones",105000)-- Reenable the constra and try another insert, will fail.ALTER TABLE cnst_example CHECK CONSTRAINT salary_capINSERT INTO cnst_example VALUES (4,"Eric James",110000)
H. 禁用并重新启用触发器
下例使用 ALTER TABLE DISABLE TRIGGER 选项来禁用触发器以使正常情况下会违反触发器条件插入操作得以执行然后下例使用 ENABLE TRIGGER 重新启用触发器
CREATE TABLE trig_example (id INT, name VARCHAR(10),salary MONEY)go-- Create the trigger.CREATE TRIGGER trig1 ON trig_example FOR INSERTas IF (SELECT COUNT(*) FROM INSERTEDWHERE salary > 100000) > 0BEGINpr "TRIG1 Error: you attempted to insert a salary > $100,000"ROLLBACK TRANSACTIONENDGO-- Attempt an insert that violates the trigger.INSERT INTO trig_example VALUES (1,"Pat Smith",100001)GO-- Disable the trigger.ALTER TABLE trig_example DISABLE TRIGGER trig1GO-- Attempt an insert that would normally violate the triggerINSERT INTO trig_example VALUES (2,"Chuck Jones",100001)GO-- Re-enable the trigger.ALTER TABLE trig_example ENABLE TRIGGER trig1GO-- Attempt an insert that violates the trigger.INSERT INTO trig_example VALUES (3,"Mary Booth",100001)GO