ALTER TABLE
通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。
语法
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_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_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_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_constraint > ] [ ...n ]
< column_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression
[ FOR column ] [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
参数
table
是要更改的表的名称。如果表不在当前数据库中或者不属于当前用户所拥有，可以显式指定数据库和所有者。
ALTER COLUMN
指定要更改给定列。如果兼容级别是 65 或小于 65，将不允许使用 ALTER COLUMN。有关更多信息，请参见 sp_dbcmptlevel。
要更改的列不能是：
数据类型为 text、image、ntext 或 timestamp 的列。
表的 ROWGUIDCOL 列。
计算列或用于计算列中的列。
被复制列。
用在索引中的列，除非该列数据类型是 varchar、nvarchar 或 varbinary，数据类型没有更改，而且新列大小等于或者大于旧列大小。
用在由 CREATE STATISTICS 语句创建的统计中的列。首先用 DROP STATISTICS 语句删除统计。由查询优化器自动生成的统计会由 ALTER COLUMN 自动除去。
用在 PRIMARY KEY 或 [FOREIGN KEY] REFERENCES 约束中的列。
用在 CHECK 或 UNIQUE 约束中的列，除非用在 CHECK 或 UNIQUE 约束中的可变长度列的长度允许更改。
有相关联的默认值的列，除非在不更改数据类型的情况下允许更改列的长度、精度或小数位数。
有些数据类型的更改可能导致数据的更改。例如，将数据类型为 nchar 或 nvarchar 的列更改为 char 或 varchar 类型，将导致扩展字符的转换。有关更多信息，请参见 CAST 和 CONVERT。降低列的精度和小数位数可能导致数据截断。
column_name
是要更改、添加或除去的列的名称。对于新列，如果数据类型为 timestamp，column_name 可以省略。对于 timestamp 数据类型的列，如果未指定 column_name，将使用名称 timestamp。
new_data_type
是要更改的列的新数据类型。要更改的列的 new_data_type 应符合下列准则：
原来的数据类型必须可以隐式转换为新数据类型。
new_data_type 类型不能为 timestamp。
对 ALTER COLUMN，ANSI 空默认值始终打开；如果没有指定，列将可为空。
对 ALTER COLUMN，ANSI 填充始终打开。
如果要更改的列是标识列，new_data_type 必须是支持标识属性的数据类型。
将忽略 SET ARITHABORT 的当前设置。ALTER TABLE 语句的行为如同 ARITHABORT 选项为 ON 时一样。
precision
是指定数据类型的精度。有关有效精度值的更多信息，请参见精度、小数位数和长度。
scale
是指定数据类型的小数位数。有关有效小数位数值的更多信息，请参见精度、小数位数和长度。
COLLATE < collation_name >
为更改列指定新的排序规则。排序规则名称既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。有关列表及更多信息，请参见 Windows 排序规则名称 和 SQL 排序规则名称。
COLLATE 子句只能用于更改数据类型为 char、varchar、text、nchar、nvarchar 和 ntext 的列的排序规则。如果未指定，则此列采用数据库的默认排序规则。
若满足下列条件，则 ALTER COLUMN 不能更改排序规则：
检查约束、外键约束或计算列引用了更改列。
在此列上创建了索引、统计或全文索引。更改列的排序规则时，该列上自动创建的统计将除去。
SCHEMABOUND 视图或函数引用了此列。
有关 COLLATE 子句的更多信息，请参见 COLLATE。
NULL | NOT NULL
指定该列是否可接受空值。不允许空值的列只有在指定了默认值的情况下，才能用 ALTER TABLE 语句向表中添加。添加到表中的新列要么允许空值，要么必须指定默认值。
如果新列允许空值，而且没有指定默认值，那么新列在表中每一行都包含空值。如果新列允许空值并且指定了新列的默认值，那么可以使用 WITH VALUES 选项在表中所有现有行的新列中存储默认值。
如果新列不允许空值，那么新列必须具有 DEFAULT 定义，而且新列的所有现有行中将自动装载该默认值。
可在 ALTER COLUMN 语句中指定 NULL 以使 NOT NULL 列允许空值，但 PRIMARY KEY 约束中的列除外。只有列中不包含空值时，ALTER COLUMN 中才可指定 NOT NULL。必须将空值更新为非空值后，才允许执行 ALTER COLUMN NOT NULL 语句，比如：
UPDATE MyTable SET NullCol = N 'some_value ' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
如果 ALTER COLUMN 中指定了 NULL 或 NOT NULL，那么必须同时指定 new_data_type [(precision [, scale ])]。如果不更改数据类型、精度和小数位数，请指定列的这些值的当前值。
[ {ADD | DROP} ROWGUIDCOL ]
指定在指定列上添加或除去 ROWGUIDCOL 属性。ROWGUIDCOL 是一个关键字，表示列是行全局唯一标识符列。对于每个表只能指派一个 uniqueidentifier 列作为 ROWGUIDCOL 列。ROWGUIDCOL 属性只能指派给 uniqueidentifier 列。
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 定义的约束。将忽略
WITH FILLFACTOR = fillfactor
指定 SQL Server 存储索引数据时每个索引页的充满程度。用户指定的 fillfactor 取值范围从 1 到 100。如果没有指定，那么默认值为 0。创建索引时，fillfactor 值越低，不必分配新空间即可添加的新索引条目的可用空间就越多。有关详细信息，请参见 CREATE INDEX。
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 ACTION，SQL 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 ACTION，SQL 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 语句也应小心使用。
如果过程高速缓存中存在引用该表的执行计划，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 bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
因为 sql_variant 的最大长度为 8016 个字节，所以产生该警告。当某 sql_variant 列所含值接近最大长度时，即会超过行长度的最大字节限制。
ALTER TABLE 语句对具有架构绑定视图的表执行时，所受限制与当前在更改具有简单索引的表时所受的限制相同。添加列是允许的。但是，不允许删除或更改参与架构绑定视图的表中的列。如果 ALTER TABLE 语句要求更改用在架构绑定视图中的列，更改 *** 作将失败，并且 SQL Server 将引发一条错误信息。有关 SCHEMABINDING 和索引视图的更多信息，请参见 CREATE VIEW。
创建引用表的架构绑定视图不会影响在基表上添加或删除触发器。
当除去约束时，作为约束的一部分而创建的索引也将除去。而通过 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。
如果要添加的列的数据类型为 uniqueidentifier，那么该列可以使用 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)
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO
B. 更改表以除去列
下例修改表以删除一列。
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
GO
ALTER TABLE doc_exb DROP COLUMN column_b
GO
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO
C. 更改表以添加具有约束的列
下例向表中添加具有 UNIQUE 约束的新列。
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
GO
EXEC sp_help doc_exc
GO
DROP TABLE doc_exc
GO
D. 更改表以添加未验证的约束
下例向表中的现有列上添加约束。该列中存在一个违反约束的值；因此，利用 WITH NOCHECK 来
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 INSERT
as
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
print "TRIG1 Error: you attempted to insert a salary > $100,000 "
ROLLBACK TRANSACTION
END
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (1, "Pat Smith ",100001)
GO
你最好是把原先这列的信息写出来，以便大家出主意。1、如果可以用图形化工具（如navicat） *** 作SQL，那是最简单的了：删除这一列所对应的字段，保存，插入这一列对应的字段，设置为int类型，长度为6，自动递增、补充零，保存，即可实现。
2、逐条修改会麻烦一些，可以借助其他的编辑工具，减少工作量，呵呵！
SQL排序子句的语法是：ORDER BY {column_name [ASC|DESC]} [，…n]大括号{}的内容表示是必有的内容（这里应该是你提问的内容）
中括号[]表示的是可选的内容
连接符|连接的是任意有一个的内容
例如：order by seq_id；--seq_id假设是表中的序号字段，这样是缺省按asc顺序排序
order by seq_id desc--显式指定排序的方式，desc降序排序
order by 1--按输出结果集的第一个字段，缺省按asc顺序排序
order by 1,3 desc,5--按输出结果集的第一个字段，缺省按asc顺序排序；第3个字段，显式明确按降序排序；第5个字段，缺省按asc顺序排序
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）