SQL 一些命令

的数据作为分区重新分配给现有的已分区表，或者将分区由一个已分区表切换到另一个已分区表，则目标分区必须存在，并且必须为空。

如果重新分配一个分区的数据以组成单个表，则必须已经创建了目标表，并且该表必须为空。源表或分区以及目标表或分区必须在同一个文件组中。对应的索引或索引分区也必须在同一个文件组中。切换分区还有许多其他限制。有关详细信息，请参阅使用分区切换高效传输数据。table 和 target_table 不能相同。target_table 可以是由多个部分构成的标识符。

source_partition_number_expression 和 target_partition_number_expression 为常量表达式，可以引用变量和函数，其中包括用户定义类型变量和用户定义函数。它们不能引用 Transact-SQL 表达式。

注意：
不能对复制表使用 SWITCH 语句。

若要添加新数据行，请使用 INSERT。若要删除数据行，请使用 DELETE 或 TRUNCATE TABLE。若要更改现有行中的值，请使用 UPDATE。

如果过程缓存中存在引用表的执行计划，ALTER TABLE 会将这些执行计划标记为下次执行时重新编译。

锁和 ALTER TABLE

ALTER TABLE 语句指定的更改将立即实现。如果这些更改需要修改表中的行，ALTER TABLE 将更新这些行。ALTER TABLE 将获取表上的架构来修改锁，以确保在更改期间没有其他连接引用（甚至是该表上的元数据，也不引用），但可在结束时执行需要一个极短的 SCH-M 锁的联机索引操作。ALTER TABLE…SWITCH 操作中，源表和目标表都需要锁。对表进行的更改将记录于日志中，并且可以完整恢复。影响超大型表中所有行的更改，比如删除列或者用默认值添加 NOT NULL 列，可能需要较长时间才能完成，并将生成大量日志记录。如同影响许多行的 INSERT、UPDATE 或者 DELETE 语句一样，应谨慎执行这些 ALTER TABLE 语句。

并行计划执行

在 SQL Server 2005 Enterprise Edition 中，根据 max degree of parallelism 配置选项和当前工作负荷，确定运行单个 ALTER TABLE ADD（基于索引）CONSTRAINT 或 DROP（聚集索引）CONSTRAINT 语句采用的处理器数。如果数据库引擎检测到系统正忙，则在语句执行开始之前将自动降低操作并行度。可以通过指定 MAXDOP 选项，手动配置用于运行此语句的处理器数。

已分区表

除了执行涉及到已分区表的 SWITCH 操作外，ALTER TABLE 还可用于更改已分区表的列、约束和触发器的状态，就像它用于非分区表一样。但是，该语句不能用于更改表本身进行分区的方式。若要对已分区表进行重新分区，请使用 ALTER PARTITION SCHEME 和 ALTER PARTITION FUNCTION。此外，不能更改已分区表中列的数据类型。

对包含架构绑定视图的表的限制

应用于包含架构绑定视图的表的 ALTER TABLE 语句的限制，与当前修改包含简单索引的表时应用的限制相同。允许添加列。但是，不允许删除或更改参与任何架构绑定视图的列。如果 ALTER TABLE 语句要求更改用于架构绑定视图中的列，ALTER TABLE 将失败，并且数据库引擎将引发错误消息。有关架构绑定和索引视图的详细信息，请参阅 CREATE VIEW (Transact-SQL)。

创建引用表的架构绑定视图不会影响为基表添加或删除触发器。

索引和 ALTER TABLE

删除约束时，作为约束的一部分而创建的索引也将被删除。由 CREATE INDEX 创建的索引必须使用 DROP INDEX 删除。ALTER INDEX 语句可用于重新生成约束定义的索引部分；而不必再使用 ALTER TABLE 来删除和添加约束。

必须删除所有基于列的索引和约束后，才能删除列。

如果删除了创建聚集索引的约束，则存储在聚集索引叶级别的数据行将存储在非聚集表中。在 SQL Server 2005 中，可以删除聚集索引，然后通过指定 MOVE TO 选项，在单个事务中将生成的表移至另一个文件组或分区架构。MOVE TO 选项有以下限制：

  • MOVE TO 对索引视图或非聚集索引无效。
  • 分区方案或文件组必须已经存在。
  • 如果没有指定 MOVE TO，则表将位于为聚集索引定义的同一分区方案或文件组中。

删除聚集索引时，可以指定 ONLINE = ON 选项，这样 DROP INDEX 事务就不会阻塞对基础数据和相关的非聚集索引的查询和修改。

ONLINE = ON 具有下列限制：

  • ONLINE = ON 对于也被禁用的聚集索引无效。 必须使用 ONLINE = OFF 删除禁用的索引。
  • 一次只能删除一个索引。
  • ONLINE = ON 对于索引视图、非聚集索引或本地临时表的索引无效。

删除聚集索引时，需要大小等于现有聚集索引的大小的临时磁盘空间。操作完成后，即可释放此额外空间。

注意：
<drop_clustered_constraint_option> 中列出的选项可适用于表的聚集索引，但不能用于视图的聚集索引或非聚集索引。

复制架构更改

默认情况下，当在 SQL Server 发布服务器中对发布的表运行 ALTER TABLE 时，此更改将传播到所有 SQL Server 订阅服务器。此功能存在一些限制并可禁用。有关详细信息，请参阅对发布数据库进行架构更改。

需要对表的 ALTER 权限。

ALTER TABLE 权限适用于 ALTER TABLE SWITCH 语句涉及的两个表。任何已切换的数据都将继承目标表的安全性。

如果将 ALTER TABLE 语句中的任何列定义为公共语言运行时 (CLR) 用户定义类型或别名数据类型，都需要对该类型有 REFERENCES 权限。

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. 更改列的数据类型

以下示例将表中列的数据类型由 INT 改为 DECIMAL。

 复制代码
CREATE TABLE doc_exy ( column_a INT ) ;
GO
INSERT INTO doc_exy (column_a)
VALUES (10) ;
GO
ALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE doc_exy ;
GO

D. 添加包含约束的列

以下示例将添加一个包含 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

E. 在现有列中添加一个未经验证的 CHECK 约束

以下示例将在表中的现有列中添加一个约束。该列包含一个违反约束的值。因此，将使用 WITH NOCHECK以避免根据现有行验证该约束，从而允许添加该约束。

 复制代码
CREATE TABLE doc_exd ( column_a INT) ;
GO
INSERT INTO doc_exd VALUES (-1) ;
GO
ALTER TABLE doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE doc_exd ;
GO

F. 在现有列中添加一个 DEFAULT 约束

以下示例将创建一个包含两列的表，在第一列插入一个值，另一列保持为 NULL。然后在第二列中添加一个 DEFAULT 约束。验证是否已应用了默认值，另一个值是否已插入第一列以及是否已查询表。

 复制代码
CREATE TABLE doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO doc_exz (column_a)
VALUES ( 7 ) ;
GO
ALTER TABLE doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO doc_exz (column_a)
VALUES ( 10 ) ;
GO
SELECT * FROM doc_exz ;
GO
DROP TABLE doc_exz ;
GO

G. 添加多个包含约束的列

以下示例将添加多个包含随新列定义的约束的列。第一个新列具有 IDENTITY 属性。表中的每一行在标识列中都有新的增量值。

 复制代码
CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE doc_exe ADD 

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, 

-- Add a column that references another column in the same table.
column_c INT NULL  
CONSTRAINT column_c_fk 
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that 
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL 
CONSTRAINT column_d_chk
CHECK 
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_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_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE doc_exe ;
GO

H. 添加包含默认值的可为空的列

以下示例将添加一个包含 DEFAULT 定义的可为空的列，并使用 WITH VALUES 为表中的各个现有行提供值。如果没有使用 WITH VALUES，那么每一行的新列中都将具包含 NULL 值。

 复制代码
Use AdventureWorks ; 
GO
CREATE TABLE doc_exf ( column_a INT) ;
GO
INSERT INTO doc_exf
VALUES (1) ;
GO
ALTER TABLE doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE doc_exf ;
GO

I. 禁用和重新启用约束

以下示例将禁用对数据中接受的薪金进行限制的约束。NOCHECK CONSTRAINT 将与 ALTER TABLE 配合使用来禁用该约束，从而允许执行通常会违反该约束的插入操作。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 inserts
INSERT INTO cnst_example VALUES (1,'Joe Brown',65000)
INSERT INTO cnst_example VALUES (2,'Mary Smith',75000)

-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,'Pat Jones',105000)

-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,'Pat Jones',105000)

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,'Eric James',110000) ;

J. 删除约束

以下示例将从表中删除 UNIQUE 约束。

 复制代码
CREATE TABLE doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE doc_exc ;
GO

K. 在表之间切换分区

以下示例创建一个已分区表，并假定在数据库中已经创建了分区方案 myRangePS1。然后，在 PartitionTable 表的 PARTITION 2 所在的同一文件组中，创建与已分区表结构相同的未分区的表。最后，将 PartitionTable 表的 PARTITION 2 中的数据切换到 NonPartitionTable 表中。

 复制代码
CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

L. 禁用和重新启用触发器

以下示例将使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器，以允许执行通常会违反此触发器的插入操作。然后，使用 ENABLE TRIGGER 重新启用触发器。

 复制代码
CREATE TABLE trig_example 
(id INT, 
name VARCHAR(12),
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
-- Try 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 trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO trig_example VALUES (3,'Mary Booth',100001) ;
GO

M. 创建包含索引选项的 PRIMARY KEY 约束

以下示例将创建 PRIMARY KEY 约束 PK_TransactionHistoryArchive_TransactionID，并设置 FILLFACTOR、ONLINE 和 PAD_INDEX 选项。生成的聚集索引将与约束同名。

 复制代码
USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)
GO

N. 在 ONLINE 模式下删除 PRIMARY KEY 约束

以下示例在 ONLINE 选项设置为 ON 的情况下删除 PRIMARY KEY 约束。

 复制代码
USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

O. 添加和删除 FOREIGN KEY 约束

以下示例将创建 ContactBackup 表，然后更改此表。首先添加引用 Contact 表的 FOREIGN KEY 约束，然后再删除 FOREIGN KEY 约束。

 复制代码
USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;
posted @ 2011-04-12 08:44  张宏宇  阅读(238)  评论(0)  编辑  收藏  举报