SQL Server数据库基础

当创建了数据库之后，下一步就需要设计数据库对象。SQL Server能够创建多种数据库对象，如表、索引、视图、存储过程、游标、触发器等。本章将对其基础知识、相关的操作进行详细介绍。

本章主要内容：

l 表

l 索引

l 视图

l 存储过程

l 游标

l 触发器

1 表

本节我们介绍数据表的基础知识，以及一些基本的操作：创建、修改、删除操作。需要提醒的是，创建数据表是创建数据库的一项基本操作。在实际的项目开发过程中，在创建数据表的时候，需要注意使用五种范式对表的数据列进行划分，得到表的逻辑结构，然后通过SQL Server提供的工具加以实现其物理结构。

1.1 表基础

表是包含数据库中所有数据的数据库对象，表定义为列的集合，与电子表格相似，数据在表中是按行和列的格式组织排列的。每行代表唯一的一条记录，而每列代表记录中的一个域。例如，下面是SQL Server提供的默认数据库Pubs中的sales表结构如图1所示。

图1 “sales”表

该表包含行和列信息，其中行表示数据，列表示数据域（stor_id、ord_num、ord_date、qty、payterns、title_id）。

设计数据库时，应先确定需要什么样的表，各表中都有哪些数据以及各个表的存取权限等等。在创建和操作表过程中，对表进行更为细致的设计。创建一个表最有效的方法是将表中所需的信息一次定义完成，包括数据约束和附加成分。也可以先创建一个基础表，向其中添加一些数据并使用一段时间。这种方法可以在添加各种约束、索引、默认设置、规则和其它对象形成最终设计之前，发现哪些事务最常用，哪些数据经常输入。

最好在创建表及其对象时预先将设计写在纸上，设计时应注意：

l 表所包含的数据的类型。

l 表的各列及每一列的数据类型（如果必要，还应注意列宽）。

l 哪些列允许空值。

l 是否要使用以及何时使用约束、默认设置或规则。

l 所需索引的类型，哪里需要索引，哪些列是主键，哪些是外键。

当设计完成数据表之后，可以采用多种方式创建数据表，如在SQL Server Management Studio中使用图形界面创建数据库表，或者执行Transact-SQL语句创建数据库表。

表的每一列都有一组属性，如名称、数据类型、为空性和数据长度等。列的所有属性构成列的定义。可以使用数据库关系图在数据库表中直接指定列的属性。在数据库中创建表之前列应具备三个属性：列名、数据类型和数据长度。

1.2 创建企业信息平台的数据表

在了解了SQL Server数据表概念和列属性之后，下面就可以创建数据表。SQL Server提供了多种方式创建数据表。如在SQL Server Management Studio中采用图形向导创建数据表，也可以使用Transact-SQL语句创建数据表，下面分别加以介绍。

（1）使用SQL Server Management Studio创建数据库表

使用SQL Server Management Studio创建数据库表的步骤如下：

1．打开SQL Server Management Studio，如图2所示。

图2 SQL Server Management Studio

2．选择需要创建表的数据库，展开文件夹，选择“表”，单击鼠标右键，选择“新建表”，如图3所示。

图3 选择“新建表”菜单项

3．输入列的名称、数据类型、长度、是否允许为空等属性，如图4所示。

图4 定义数据表

4．当定义完成之后，单击工具栏上的按钮，保存该表，SQL Server Management Studio将弹出对话框，要求输入表名，如图5所示。

图5 定义表名的对话框

5．当定义了表名之后，单击“OK“按钮，保存该表，数据表创建完成。

（2）使用Transact-SQL创建数据库表

在SQL Server Management Studio提供的查询分析器中，可以定义Transact-SQL的CREATE TABLE语句创建数据表。其语法格式如下：

CREATE TABLE 
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
    ( { <column_definition> | <computed_column_definition> }
        [ <table_constraint> ] [ ,...n ] ) 
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | " DEFAULT " } ] 
    [ { TEXTIMAGE_ON { filegroup | " DEFAULT " } ] 
[ ; ]

< column_definition > ::=
column_name <data_type>
    [ NULL | NOT NULL ]
    [ COLLATE collation_name ] 
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] 
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] 

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | MAX | xml_schema_collection ) ] 
    sql_server_native_type | type_name 

<column_constraint> ::= 
[ CONSTRAINT constraint_name ] 
{     { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ 
            WITH FILLFACTOR = fillfactor
          | WITH ( < index_option >[, ...n ] ) 
 
 
        ]
        [ ON { partition_scheme_name ( partition_column_name ) 
            | filegroup | " DEFAULT " } ]
  | [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
  | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

<computed_column_definition> ::=
column_name AS computed_column_expression 
[ PERSISTED [ NOT NULL] ]
[ 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [ 
            WITH FILLFACTOR = fillfactor 
          | WITH ( <index_option> [, ...n ] )
        ]
    | [ FOREIGN KEY ] 
        REFERENCES ref_table [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE } ] 
        [ ON UPDATE { NO ACTION } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK ( logical_expression ) 
    [ ON { partition_scheme_name ( partition_column_name ) 
        | filegroup | " DEFAULT " } ]
] 

< table_constraint > ::=
[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
 
 
        (column [ ASC | DESC ] [ ,...n ] ) 
        [ 
            WITH FILLFACTOR = fillfactor 
           |WITH ( <index_option> [, ...n ] ) 
        ]
        [ ON { partition_scheme_name (partition_column_name)
            | filegroup | " DEFAULT " } ] 
    | FOREIGN KEY 
        ( column [ ,...n ] ) 
        REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

<index_option> ::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY  = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS= { ON | OF} 
  | ALLOW_PAGE_LOCKS={ ON | OF} 
}

其中，各个参数的具体说明如下：

l database_name，是要在其中创建表的数据库名称。database_name 必须是现有数据库的名称。如果不指定数据库，database_name 默认为当前数据库。当前连接的登录必须在 database_name 所指定的数据库中有关联的现有用户 ID，而该用户 ID 必须具有创建表的权限。

l schema_name，是新表所属于的模式名。

l table_name，是新表的名称。表名必须符合标识符规则。数据库中的 owner.table_name 组合必须唯一。table_name 最多可包含 128 个字符，但本地临时表的表名（名称前有一个编号符 #）最多只能包含 116 个字符。

l column_name，是表中的列名。列名必须符合标识符规则，并且在表内唯一。以 timestamp 数据类型创建的列可以省略 column_name。如果不指定 column_name，timestamp 列的名称默认为 timestamp。

l computed_column_expression，是定义计算列值的表达式。计算列是物理上并不存储在表中的虚拟列。计算列由同一表中的其它列通过表达式计算得到。例如，计算列可以这样定义：cost AS price * qty。表达式可以是非计算列的列名、常量、函数、变量，也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。

l PERSISTED，定义SQL Server物理存储计算值到表中，当计算列依赖的任何其他列值发生改变的时候，更新值。

l ON { <partition_scheme> | filegroup | "DEFAULT " } ，定义表所在的分区模式或者文件组。如果选择< partition_scheme >，表是一个分区表。如果定义为filegroup，表存储在文件组中。文件组必须位于数据库中。

l TEXTIMAGE_ON { filegroup| "DEFAULT "]} ，是表示 text、ntext 和 image 列存储在指定文件组中的关键字。如果表中没有 text、ntext 或 image 列，则不能使用 TEXTIMAGE ON。如果没有指定 TEXTIMAGE_ON，则 text、ntext 和 image 列将与表存储在同一文件组中。

l [ type_schema_name. ] type_name，定义列的数据类型，以及所属于的模式，数据类型可以是：NativeSQL类型、CLR用户自定义类型。

l precision，定义数据类型的精度。

l scale，定义数据类型的刻度。

l MAX，只能用于varchar, nvarchar和varbinary数据类型，存储2^31字节的字符串或者二进制数据，或者2^30的Unicode数据。

l xml_schema_collection ，只应用于XML数据类型，将XML模式同类型关联起来。在将一个XML列输入到模式中时，首先必须使用CREATE XML SCHEMA COLLECTION语句在数据库中创建模式。

l DEFAULT，如果在插入过程中未显式提供值，则指定为列提供的值。DEFAULT 定义可适用于除定义为 timestamp 或带 IDENTITY 属性的列以外的任何列。除去表时，将删除 DEFAULT 定义。只有常量值（如字符串）、系统函数（如 SYSTEM_USER()）或 NULL 可用作默认值。为保持与 SQL Server 早期版本的兼容，可以给 DEFAULT 指派约束名。

l constant_expression，是用作列的默认值的常量、NULL 或系统函数。

l IDENTITY，表示新列是标识列。当向表中添加新行时，Microsoft® SQL Server™ 将为该标识列提供一个唯一的、递增的值。标识列通常与 PRIMARY KEY 约束一起用作表的唯一行标识符。可以将 IDENTITY 属性指派给 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 列。对于每个表只能创建一个标识列。不能对标识列使用绑定默认值和 DEFAULT 约束。必须同时指定种子和增量，或者二者都不指定。如果二者都未指定，则取默认值 (1,1)。

l Seed，是装入表的第一行所使用的值。

l Increment，是添加到前一行的标识值的增量值。

l NOT FOR REPLICATION，表示当复制登录向表中插入数据时，不强制 IDENTITY 属性。复制的行必须保留发布数据库中所赋予的键值；NOT FOR REPLICATION 子句确保不向复制进程所插入的行赋予新的标识值。其它登录所插入的行仍然具有以通常的方式创建的新标识值。建议同时使用具有 NOT FOR REPLICATION 的 CHECK 约束，以确保赋予的标识值处于当前数据库所需的范围内。

l ROWGUIDCOL，表示新列是行的全局唯一标识符列。对于每个表只能指派一个 uniqueidentifier 列作为 ROWGUIDCOL 列。ROWGUIDCOL 属性只能指派给 uniqueidentifier 列。如果数据库兼容级别小于或等于 65，则 ROWGUIDCOL 关键字无效。ROWGUIDCOL 属性并不强制列中所存储值的唯一性。该属性也不会为插入到表中的新行自动生成值。若要为每列生成唯一值，那么或者在 INSERT 语句中使用 NEWID 函数，或者将 NEWID 函数指定为该列的默认值。

l collation_name，指定列的排序规则。排序规则名称既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。collation_name 仅适用于数据类型为 char、varchar、text、nchar、nvarchar 及 ntext 的列。如果没有指定该参数，那么如果列的数据类型是用户定义的，则该列的排序规则就是用户定义数据类型的排序规则，否则就是数据库的默认排序规则。

l CONSTRAINT，是可选关键字，表示 PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY 或 CHECK 约束定义的开始。约束是特殊属性，用于强制数据完整性并可以为表及其列创建索引。

l constraint_name，是约束的名称。约束名在数据库内必须是唯一的。

l NULL | NOT NULL，是确定列中是否允许空值的关键字。从严格意义上讲，NULL 不是约束，但可以使用与指定 NOT NULL 同样的方法指定。

l PRIMARY KEY，是通过唯一索引对给定的一列或多列强制实体完整性的约束。对于每个表只能创建一个 PRIMARY KEY 约束。

l UNIQUE，是通过唯一索引为给定的一列或多列提供实体完整性的约束。一个表可以有多个 UNIQUE 约束。

l CLUSTERED | NONCLUSTERED，是表示为 PRIMARY KEY 或 UNIQUE 约束创建聚集或非聚集索引的关键字。PRIMARY KEY 约束默认为 CLUSTERED，UNIQUE 约束默认为 NONCLUSTERED。在 CREATE TABLE 语句中只能为一个约束指定 CLUSTERED。如果在为 UNIQUE 约束指定 CLUSTERED 的同时又指定了 PRIMARY KEY 约束，则 PRIMARY KEY 将默认为 NONCLUSTERED。

l FOREIGN KEY ...REFERENCES，是为列中的数据提供引用完整性的约束。FOREIGN KEY 约束要求列中的每个值在被引用表中对应的被引用列中都存在。FOREIGN KEY 约束只能引用被引用表中为 PRIMARY KEY 或 UNIQUE 约束的列或被引用表中在 UNIQUE INDEX 内引用的列。

l [ schema_name . ] referenced_table_name ]，表示FOREIGN KEY约束引用的表名及其所属于的模式名。

l ( ref_column[ ,... n] )，是 FOREIGN KEY 约束所引用的表中的一列或多列。

l ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }，指定当要创建的表中的行具有引用关系，并且从父表中删除该行所引用的行时，要对该行采取的操作。默认设置为 NO ACTION。如果指定 CASCADE，则从父表中删除被引用行时，也将从引用表中删除引用行。如果指定 NO ACTION，SQL Server 将产生一个错误并回滚父表中的行删除操作。

l ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }，指定当要创建的表中的行具有引用关系，并且在父表中更新该行所引用的行时，要对该行采取的操作。默认设置为 NO ACTION。如果指定 CASCADE，则在父表中更新被引用行时，也将在引用表中更新引用行。如果指定 NO ACTION，SQL Server 将产生一个错误并回滚父表中的行更新操作。

l CHECK，是通过限制可输入到一列或多列中的可能值强制域完整性的约束。

l NOT FOR REPLICATION，是用于防止在复制所使用的分发过程中强制 CHECK 约束的关键字。当表是复制发布的订户时，请不要直接更新订阅表，而要更新发布表，然后让复制进程将数据分发回订阅表。可以在订阅表上定义 CHECK 约束，以防用户修改订阅表。但是如果不使用 NOT FOR REPLICATION 子句，CHECK 约束同样会防止复制进程将修改从发布表分发给订阅表。NOT FOR REPLICATION 子句表示对用户的修改（而不是对复制进程）强加约束。NOT FOR REPLICATION CHECK 约束适用于被更新记录的前像和后像，以防在复制范围中添加记录或从复制范围中删除记录。将检查所有删除和插入操作；如果操作在复制范围内，则拒绝执行该操作。如果对标识符列使用此约束，则当复制用户更新标识列时，SQL Server 将允许不必重新计算表标识列的种子值。

l logical_expression，是返回 TRUE 或 FALSE 的逻辑表达式。

l column ，是用括号括起来的一列或多列，在表约束中表示这些列用在约束定义中。

l [ASC | DESC]，指定加入到表约束中的一列或多列的排序次序。默认设置为 ASC。

l n，是表示前面的项可重复 n 次的占位符。

l partition_scheme_name，表示定义文件组的分区模式名。

l [ partition_column_name.]，定义用于对一个表的数据进行分区的列名。

l WITH FILLFACTOR =fillfactor，定义SQL Server如何使用索引页存储索引数据。用户自定义的填充因子值的范围为1到100。如果没有定义该值，默认为0。

l <index_option > ::= ，定义一个或者多个索引选项，对于选项的说明，请参考CREATE INDEX语句的语法。

l PAD_INDEX = { ON | OFF } ，当设置为ON的时候，FILLFACTOR定义的空闲空间的百分比应用于索引的中间层页。当定义为OFF或者没有指定的时候，中间层页填充几乎整个空间，只保留存储最大索引一行数据的空间。默认值为OFF。

l FILLFACTOR =fillfactor，定义指示SQL Server在创建和修改索引的时候，如何创建索引页的页层。取值范围为1到100，默认值为0。

l IGNORE_DUP_KEY = { ON | OFF } ，定义在对唯一集群索引执行多行INSERT事务的时候，出现重复键值的错误响应。当设置为ON时，如果行破坏了唯一索引，将显示一个警告消息，插入行失败。当设置为OFF的时候，如果行破坏唯一索引，将提供一个错误消息，整个INSERT语句回滚。当处理UPDATE语句的时候，IGNORE_DUP_KEY没有影响，默认值为OFF。

l STATISTICS_NORECOMPUTE = { ON | OFF } ，当设置为ON的时候，过期索引统计信息不会自动被重新计算。当设置为OFF的时候，自动更新统计信息，默认值为OFF。

l ALLOW_ROW_LOCKS = { ON | OFF } ，当设置为ON的时候，当访问索引的时候，支持行锁。数据库引擎确定何时使用行锁。如果设置为OFF，不使用行锁，默认值为ON。

l ALLOW_PAGE_LOCKS = { ON | OFF } ，当设置为ON的时候，当访问索引的时候，支持页锁。数据库引擎确定何时使用页锁。当设置为OFF的时候，不使用页锁。默认值为ON。

假设要求在企业信息平台数据库EAMS中创建数据表clCommunicationsList，如表1所示，表示个人的通信录信息，包括通信录ID、类别、人员ID、通信录人员姓名、性别、人员编码、Email地址、办公室电话、办公室传真、移动电话、地址等信息。

表1 clCommunicationsList表结构

字段名称	字段解释	数据类型	允许空	备注
CommunicationID	通信录ID	int	否		主键
CategoryName	通信录类别名称	Varchar(20)	否
EmpID	人员ID	int	否		外键
COMName	通信录人员名	Varchar(50)	否
Sex	性别	Bit	否
EmpCode	人员编码	Varchar(20)	可
Email	人员Email地址	Varchar(50)	可
OfficeTel	办公室电话	Varchar(20)	可
OfficeFax	办公室传真	Varchar(20)	可
Mobile	移动电话	Varchar(20)	可
Position	位置	Varchar(50)	可
Province	省	Varchar(32)	可
City	城市	Varchar(32)	可
District	区	Varchar(32)	可
Street	街道	Varchar(32)	可
PostCode	邮编	Varchar(20)	可
Tel1	电话1	Varchar(20)	可
Tel2	电话2	Varchar(20)	可
Note	注释	Varchar(1000)	可

创建clCommunicationsList表的Transact-SQL语句如下：

USE [EAMS]
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[clCommunicationsList](
    [CommunicationID] [int] IDENTITY(127,1) NOT NULL,
    [CategoryName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [EmpID] [int] NOT NULL,
    [COMName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [EmpCode] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Sex] [bit] NOT NULL,
    [Email] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [OfficeTel] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [OfficeFax] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Mobile] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Position] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Province] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
    [City] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
    [District] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
    [Street] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
    [PostCode] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Tel1] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Tel2] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [note] [varchar](1000) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_clCommunicationsList_DelFlag]  DEFAULT (0)
,
 CONSTRAINT [PK_clCommunicationsList] PRIMARY KEY CLUSTERED 
(
    [CommunicationID] ASC,
    [CategoryName] ASC,
    [EmpID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS OFF

类似可以创建企业信息平台的企业表结构。

1.3 修改企业信息平台的数据表

当创建了表之后，根据特定情况，可能需要对所创建好的表进行修改操作，如修改列名、数据类型、类型长度、默认值等属性。修改表的列定义相对较为简单。可以在SQL Server Management Studio中采用图形方式，也可以采用Transact-SQL语句完成。例如，修改企业信息平台数据库表mrBaseInf，将办公室电话号码OfficeTel长度从50修改为20。

采用图形方式修改表的步骤如下：

1．打开SQL Server Management Studio，在“对象资源管理器”视图中，展开EAMS数据库，如图6所示。

图6 选择EAMS数据库

2．选中“mrBaseInf”表，单击鼠标右键，选择“修改表”菜单项，如图7所示。

图7 修改表

3．打开表的定义视图，如图8所示。

图8 mrBaseInf表结构

4．选择“OfficeTel”列，在列属性视图中，将长度从50修改为20，如图9所示。

图9 修改列定义

5．如果还需要修改其它列定义，可以参考类似操作，单击工具栏上的按钮，保存修改即可。

1.4 删除企业信息平台的数据表

当某些表不再需要的时候，可以删除数据库中的某些表。删除数据表的操作可以在SQL Server Management Studio中，或者采用Transact-SQL语句完成。

（1）使用SQL Server Management Studio删除表

使用SQL Server Management Studio删除表的步骤如下：

1．在SQL Server Management Studio中的“对象资源管理器”视图中，选中需要删除的表，单击鼠标右键，如图10所示。

图10 删除表

2．选择“删除”菜单项，弹出“删除对象”对话框，单击“确定”按钮，删除表，如图11所示。

图11 确定删除表

（2）使用Transact-SQL语句删除表

使用Transact-SQL语句删除表的语句是DELETE TABLE，语法如下：

DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]

table_name [ ,...n ] [ ; ]

参数说明如下：

l database_name，表示表所在的数据库名。

l schema_name，表示表属于的模式名。

l table_name，需要删除的表名。

l n，表示在任何数据库中删除多个表。如果删除的表引用了另一个表的主键，则另一个表也被删除。

假定需要删除所创建的企业信息平台数据表mrBaseInf。可以采用Transact-SQL语句删除数据表，其语句如下：

USE [EAMS]

DROP TABLE [dbo].[ mrBaseInf]

USE [master]

2 索引

数据库中90％的性能问题与索引/查询有关。索引机制是提升数据库性能的重要机制。SQL Server提供了对索引的良好支持，提供了多种类型的索引机制，方便开发人员在适当的时候创建特定的索引。

2.1 索引基础

用户对数据库最频繁的操作是进行数据查询。一般情况下，数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时，搜索数据就需要很长的时间，这就造成了服务器的资源浪费。为了提高检索数据的能力，数据库引入了索引机制。

SQL Server数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是一个表中所包含的值的列表，其中注明了表中包含各个值的行所在的存储位置。可以为表中的单个列建立索引，也可以为一组列建立索引。

通过定义索引，可以提高查询速率，节省响应时间。不过，索引为性能所带来的好处却是有代价的。带索引的表在数据库中会占据更多的空间。另外，为了维护索引，对数据进行插入、更新、删除操作的命令所花费的时间会更长。在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引是依赖于表建立的，它提供了数据库中编排表中数据的内部方法。一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上，通常，索引页面相对于数据页面来说小得多。当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，再直接通过指针从数据页面中读取数据。从某种程度上，可以把数据库看作一本书，把索引看作书的目录，通过目录查找书中的信息，显然较没有目录的书方便、快捷。

SQL Server 中的索引是以B-树结构来维护的，如图12所示。B-树是一个多层次、自维护的结构。一个B-树包括一个顶层，称为根节点（Root Node）；0 到多个中间层（Intermediate）；一个底层（Level 0），底层中包括若干叶子节点（Leaf Node）。在图 12中，每个方框代表一个索引页，索引列的宽度越大，B-树的深度越深，即层次越多，读取记录所要访问的索引页就越多。也就是说，数据查询的性能将随索引列层次数目的增加而降低。

图 12 索引结构

SQL Server使用两种基本类型的索引：聚集索引和非聚集索引。这两类索引都可以对多个列进行索引，在这种情况下也可以称它们为组合索引。依据查询使用索引的方式，还可以将其称为覆盖索引（covering index）。在SQL Server中，还支持唯一索引、索引视图、全文索引和XML索引。

（1）非聚集索引

如图13所示，非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。

典型的桌面数据库使用的是非聚集索引。在这类索引中，索引键值是有序的，而每个索引节点所指向的数据行是无序的。一个SQL Server表最多可以拥有255个非聚集索引。

非聚集索引与聚集索引一样有 B-树结构，但是有两个重大差别：

l 数据行不按非聚集索引键的顺序排序和存储。

l 非聚集索引的叶层不包含数据页。

相反，叶节点包含索引行。每个索引行包含非聚集键值以及一个或多个行定位器，这些行定位器指向有该键值的数据行（如果索引不唯一，则可能是多行）。非聚集索引可以在有聚集索引的表、堆集或索引视图上定义。在 SQL Server中，非聚集索引中的行定位器有两种形式：

l 如果表是堆集（没有聚集索引），行定位器就是指向行的指针。该指针用文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID。

由于非聚集索引将聚集索引键作为其行指针存储，因此使聚集索引键尽可能小很重要。如果表还有非聚集索引，请不要选择大的列作为聚集索引的键。

图 13 非聚集索引结构

与使用书中索引的方式相似，SQL Server在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。这使非聚集索引成为精确匹配查询的最佳方法，因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。如果基础表使用聚集索引排序，则该位置为聚集键值；否则，该位置为包含行的文件号、页号和槽号的行 ID (RID)。

在创建非聚集索引之前，应先了解您的数据是如何被访问的。可考虑将非聚集索引用于：

l 包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。如果只有很少的非重复值，如只有 1 和 0，则大多数查询将不使用索引，因为此时表扫描通常更有效。

l 不返回大型结果集的查询。

l 返回精确匹配的查询的搜索条件（WHERE 子句）中经常使用的列。

l 经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。

l 在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。

（2）聚集索引

如图14所示，聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

聚集索引在系统数据库表sysindexes 内有一行，其 indid = 1。数据链内的页和其内的行按聚集索引键值排序。所有插入都在所插入行中的键值与排序顺序相匹配时执行。

SQL Server将索引组织为B-树。索引内的每一页包含一个页首，页首后面跟着索引行。每个索引行都包含一个键值以及一个指向较低级页或数据行的指针。索引的每个页称为索引节点。B-树的顶端节点称为根节点。索引的底层节点称为叶节点。每级索引中的页链接在双向链接列表中。在聚集索引内数据页组成叶节点。根和叶之间的任何索引级统称为中间级。

对于聚集索引，sysindexes.root 指向它的顶端。SQL Server 沿着聚集索引浏览以找到聚集索引键对应的行。为找到键的范围，SQL Server 浏览索引以找到这个范围的起始键值，然后用向前或向后指针扫描数据页。为找到数据页链的首页，SQL Server 从索引的根节点开始沿最左边的指针进行扫描，图14说明聚集索引的结构。

图14 聚集索引结构

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

对于聚集索引，人们往往有一些错误的认识。其中，最常见的错误有：

l 聚集索引会降低insert操作的速度，因为必须要向后移动一半的数据来为新插入的行腾出空间。这种认识是错误的，因为可以利用填充因子控制填充的百分比，从而在索引页上为新插入的数据保留空间。如果索引页填满了，SQL Server将会进行页拆分，在这种情况下只有第一个页才会受到影响。

l 在使用标识列的主键上创建聚集索引是一种好的设计方法，它可以使对表的操作达到最快速度。这种认识是错误的，它浪费了创建其它更有效的聚集索引的机会。并且，使用这种方法会把每个新插入的记录行都存储到表尾部的同一个的数据页中，这将导致数据库的热点和锁争用。笔者曾经见过采用这种方法设计的数据库，对于每一个新订单，客户服务人员都不得不等待数分钟来加以确认。

l 聚集索引是具有魔力的。如果哪个查询的速度不够快，那么就在该列上创建聚集索引，对于表的操作速度一定会得到提高。这种认识也是错误的，聚集索引只是比非聚集索引稍稍快了那么一点点。因为在每个表上只能创建一个聚集索引，所以它也是一种宝贵的性能资源，只有在那些经常作为条件查询一组记录行的列上才应该建立聚集索引。

在创建聚集索引之前，应先了解数据是如何被访问的。可考虑将聚集索引用于：

l 包含大量非重复值的列。

l 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。

l 被连续访问的列。

l 返回大型结果集的查询。

l 经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

l OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引。

注意，聚集索引不适用于：

l 频繁更改的列，这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。

l 宽键，来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。

（3）唯一索引

唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。例如，如果在 last_name、first_name 和 middle_initial 列的组合上创建了唯一索引 full_name，则该表中任何两个人都不可以具有相同的全名。

聚集索引和非聚集索引都可以是唯一的。因此，只要列中的数据是唯一的，就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引。

只有当唯一性是数据本身的特征时，指定唯一索引才有意义。如果必须实施唯一性以确保数据的完整性，则应在列上创建 UNIQUE 或 PRIMARY KEY 约束，而不要创建唯一索引。例如，如果打算经常查询雇员表（主键为 emp_id）中的社会安全号码 (ssn) 列，并希望确保社会安全号码的唯一性，则在 ssn 列上创建 UNIQUE 约束。如果用户为一个以上的雇员输入了同一个社会安全号码，则会显示错误。

（4）索引视图

复杂报表的场景经常会在数据仓储应用程序中遇到，它在查询过程中会对数据库服务器产生大量请求。当这些查询访问视图时，因为数据库将建立视图结果集所需的逻辑合并到从基本表数据建立完整查询结果集所需的逻辑中，所以性能将会下降。这一操作的开销可能会比较大，尤其当视图涉及到复杂的大量行处理--如大量数据聚合或多表联结时。因为结果集并不永久存放在数据库（标准视图）中，以后对该视图的访问可能导致在每次执行查询时建立结果集的代价。

SQL Server允许为视图创建独特的聚集索引，从而让访问此类视图的查询的性能得到极大地改善。在创建了这样一个索引后，视图将被执行，结果集将被存放在数据库中，存放的方式与带有聚集索引的表的存放方式相同。这就在数据库中有效地实现了查询结果。对于那些在FROM子句中不直接指定视图名的查询，SQL Server查询优化器将使用视图索引。现有查询将受益于从索引视图检索数据而无需重新编写程序原码的高效率。对于某些特定类型的视图，甚至可以获得指数级的性能改善。

如果在视图上创建索引，那么视图中的数据会被立即存储在数据库中，对索引视图进行修改，那么这些修改会立即反映到基础表中。同理，对基础表所进行的数据修改也会反映到索引视图那里。索引的惟一性大大提高了SQL Server 查找那些被修改的数据行。

维护索引视图比维护基础表的索引更为复杂。所以，如果认为值得以因数据修改而增加系统负担为代价来提高数据检索的速度，那么应该在视图上创建索引。

设计索引视图时，请考虑以下准则：

l 设计的索引视图必须能用于多个查询或多个计算。例如，包含某列的 SUM 和某列的 COUNT_BIG 的索引视图可用于包含函数 SUM、COUNT、COUNT_BIG 或 AVG 的查询。由于只需检索视图中的少数几行，而不是基表中的所有行，且执行 AVG 函数要求的部分计算已经完成，所以查询将比较快。

l 使索引保持紧凑。通过使用最少的列数和尽可能少的字节数，优化器在查找行数据时可获得最高的效率。相反，如果定义了大的群集索引关键字，则为视图定义的任何辅助性非群集索引都将明显增大，这是因为非群集索引项除包含索引定义的列之外，还将包含群集关键字。

l 考虑生成的索引视图的大小。在单纯的聚合情况下，如果索引视图的大小类似于原表的大小，使用索引视图可能无法明显提高任何性能。

l 设计多个较小的索引视图来加快部分进程的速度。有时可能无法设计出能满足整个查询需要的索引视图。此时即可考虑创建这样一些索引视图，每个索引视图执行一部分查询。

在为视图创建索引前，视图本身必须满足以下条件：

l 视图以及视图中引用的所有表都必须在同一数据库中，并具有同一个所有者。

l 索引视图无需包含要供优化器使用的查询中引用的所有表。

l 必须先为视图创建唯一群集索引，然后才可以创建其它索引。

l 创建基表、视图和索引以及修改基表和视图中的数据时，必须正确设置某些 SET 选项（在本文档的后文中讨论）。另外，如果这些 SET 选项正确，查询优化器将不考虑索引视图。

l 视图必须使用架构绑定创建，视图中引用的任何用户定义的函数必须使用 SCHEMABINDING 选项创建。

l 另外，还要求有一定的磁盘空间来存放由索引视图定义的数据。

在视图上创建了索引之后，如果打算修改视图数据，则应该保证修改时的选项设置与创建索引时的选项设置一样，否则SQL Server 将产生错误信息，并回滚所做的INSERT、UPDATE 和DELETE 操作。

并非所有查询都会从索引视图中获益。与普通索引类似，如果未使用索引视图，就没有好处可言。在此情况下，不但不能提高性能，还会加大磁盘空间的占用、增加维护和优化的成本。但是，如果使用了索引视图，它们可以（成数量级地）明显地提高数据访问的性能。这是因为查询优化器使用存储在索引视图中的预先计算的结果，从而大大降低了执行查询的成本。

查询优化器只在查询的成本比较大时才考虑使用索引视图。这样可以避免在查询优化成本超出因使用索引视图而节省的成本时，试图使用各种索引视图。当查询成本低于 1 时，几乎不使用索引视图。

使用索引视图可以受益的应用包括：

l 决定支持工作量

l 数据集市

l 联机分析处理 (OLAP) 库和源

l 数据挖掘工作量

从查询的类型和模式的角度来看，受益的应用可被归纳为包含以下内容的应用：

l 大表的联接和聚合

l 查询的重复模式

l 重复聚合相同或重叠的列集

l 针对相同关键字重复联接相同的表

l 上述的组合

相反，包含许多写入的联机事务处理 (OLTP) 系统或更新频繁的数据库，可能会因为要同时更新视图和根本基表而使维护成本增加，所以不能利用索引视图。

SQL Server 自动维护索引视图，这与维护任何其它索引的情况类似。对于普通索引而言，每个索引都直接连接到单个表。通过对基础表执行每个 INSERT、UPDATE 或 DELETE 操作，索引相应地进行了更新，以便使存储在该索引中的值始终与表一致。

索引视图的维护与此类似。不过，如果视图引用了多个表，则对这些表中的任何一个进行更新都需要更新索引视图。与普通索引不同的是，对任何一个参与的表执行一次行插入操作都可能导致在索引视图中进行多次行插入操作。更新和删除操作的情况也是如此。因此，较之于维护表的索引，维护索引视图的代价更为高昂。

在 SQL Server 中，某些视图可以更新。如果某个视图可以更新，则使用 INSERT、UPDATE 和 DELETE 语句可通过该视图直接修改根本基表。为某个视图创建索引并不会妨碍该视图的更新。

与 SQL Server 2000 相比，SQL Server 包含了许多索引视图的改进功能。可索引的视图组已扩展至包含基于下列各项的视图：

l 标量聚合，包括 SUM 和不带 GROUP BY 的 COUNT_BIG。

l 标量表达式和用户定义的功能 (UDFs)。例如，给定一个表 T(a int, b int, c int) 和一个标量 UDF dbo.MyUDF(@x int)，T 上定义的索引视图可包含一个计算列（比如：a+b 或 dbo.MyUDF(a)）。

l 不精确的永久性列。不精确的列是一种浮型或实型的列，或者是一种派生自浮型或实型列的计算列。在 SQL Server 2000 中，如果不属于索引键的一部分，不精确的列就可用于索引视图的选择列表。不精确的列不能用于视图定义中的其他地方（比如：WHERE 或 FROM 子句）。如果不精确的列永久保存在基表中，那么 SQL Server 允许其加入键或视图定义。永久性列包含常规列和标记为 PERSISTED 的计算列。

l 不精确的非永久性列无法加入索引或索引视图的根本原因是：必须使数据库脱离原计算机，然后再附加到另一台计算机。完成转移之后，保存在索引或索引视图中的所有计算列值在新硬件上的派生方式必须与旧硬件完全相同，精确到每个位。否则，这些索引视图在新硬件上会遭到逻辑破坏。由于这种破坏，在新硬件上，针对索引视图的查询会根据计划是否使用了索引视图或基表来派生视图数据，返回不同的应答。此外，无法在新计算机上正常维护索引视图。可惜，不同计算机上的浮点硬件（即便采用相同制造商的相同处理器体系结构）在处理器的版本上并不总是完全相同。对于某些浮点值 a 和 b，固件升级可能导致新硬件上的 (a*b) 不同于旧硬件上的 (a*b)。例如，结果可能非常相近，但仍存在细微差别。在进行索引之前一直保留不精确的计算值可解决这种分离/附加的不一致性问题，因为在进行索引和索引视图的数据库更新和维护期间，在相同的计算机上评估了所有表达式。

l 通用语言运行时 (CLR) 类型。SQL Server 的一个主要的新功能是支持基于 CLR 的用户定义的类型 (UDT) 和 UDF。假如列或表达式具有确定性或是永久且精确的，或者二者兼具，那么就可在 CLR UDT 列或从这些列派生而来的表达式上定义索引视图。不能在索引视图上使用 CLR 用户定义的聚合。

优化器匹配查询和索引视图（使之可在查询计划中使用）的功能经扩展包含：

l 新的表达式类型，位于查询或视图的 SELECT 列表或条件中，涉及：标量表达式（比如 (a+b)/2）、标量聚合、标量 UDF。

l 间隔归入。优化器可检测索引视图定义中的间隔条件是否覆盖或“归入”查询中的间隔条件。例如，优化器可确定“a>10 and a<20”覆盖“a>12 and a<18”。

l 表达式等价。某些表达式虽然在语法上有所不同，但最终的结果却相同，那么可以将其视为等价。例如，“a=b and c<>10”与“10<>c and b=a”等价。

（4）全文索引

全文索引可以对存储在SQL Server数据库中的文本数据执行快速检索功能。同LIKE谓词不同，全文索引只对字符模式进行操作，对字和语句执行搜索功能。全文索引对于查询非结构化数据非常有效。一般情况下，可以对char、varchar和nvarchar数据类型的列创建全文索引，同时，还可以对二进制格式的列创建索引，如image和varbinary数据类型列。对于这些二进制数据，无法使用LIKE谓词。

为了对表创建全文索引，表必须包含单个、唯一、非空列。当执行全文检索的时候，SQL Server搜索引擎返回匹配搜索条件的行的键值。一般情况，使用sql server中的全文索引，经过大体4个步骤：

l 安装全文索引服务；

l 为数据表建立全文索引目录；

l 使全文索引与数据表内容同步；

l 使用全文索引进行查询。

（5）XML索引

Microsoft SQL Server 以 XML 数据类型的形式添加了内置的 XML 支持。XML 数据可以存储在 XML 数据类型列内部。另外，通过将一个 XML 方案集合与此 XML 数据类型列关联，还可以对其进行进一步的限制。存储在 XML 数据类型列中的 XML 值可以借助 XQuery 和 XML 数据修改语言 (DML) 进行处理。可以在 XML 数据上建立索引，以增强查询性能。此外，FOR XML 和 OPENXML 也已得到增强，能够支持新的 XML 数据类型。

SQL Server 中引入的存储和处理 XML 数据的新功能与 SQL Server 早期版本中提供的 XML 功能结合在一起，为开发人员提供了多种在 XML 应用程序中存储和处理 XML 数据的方法。由于使用 SQL Server 提供的方法，有多种方法可以生成 XML 应用程序，因此，了解各种不同技术的方案，以及如何在各种技术之间进行权衡和配合对于作出正确的选择是至关重要的。本文提供了如何选择适当的方法，使用 SQL Server 开发 XML 应用程序的指南。

针对XML数据类型，SQL Server提供了XML索引类型。XML索引是在xml数据类型列上创建的索引，同其他索引类似，XML索引可以提高查询性能。

2.2 创建索引

确定了索引设计后，便可以在数据库的表上创建索引。创建索引时须考虑的其它事项包括：

l 只有表的所有者可以在同一个表中创建索引。

l 每个表中只能创建一个聚集索引。

l 每个表可以创建的非聚集索引最多为 249 个（包括 PRIMARY KEY 或 UNIQUE 约束创建的任何索引）。

l 包含索引的所有长度固定列的最大大小为 900 字节。例如，不可以在定义为 char(300)、char(300) 和 char (301) 的三个列上创建单个索引，因为总宽度超过了 900 字节。

l 包含同一索引的列的最大数目为 16。

在使用 CREATE INDEX 语句创建索引时，必须指定索引、表以及索引所应用的列的名称。作为 PRIMARY KEY 或 UNIQUE 约束的一部分或使用 SQL Server 企业管理器创建的新索引，会根据数据库表的名称，自动获得系统定义的名称。如果在一个表上创建多个索引，这些索引的名称被追加 _1、_2 等。必要时可对索引重新命名。

当需要创建索引的时候，可以采用多种方式，可以在SQL Server Management Studio中采用图形工具创建索引，也可以执行Transact-SQL语句创建索引，下面我们分别进行介绍。

（1）使用SQL Server Management Studio图形工具创建索引

使用SQL Server Management Studio图形工具创建索引的步骤如下：

1．打开SQL Server Management Studio，在“对象资源管理器”视图中双击需要创建索引的数据库，展开需要创建索引的表，选择“索引”节点，单击鼠标右键，如图15所示。

图 15 新建索引

2．选择“新建索引”菜单项，打开“新建索引”对话框的“常规”视图，如图16所示。

图 16 “常规”视图

在该对话框中，定义：

l 索引的名称；

l 索引的类型；

l 索引列。

3．当添加索引列的时候，单击“添加”按钮，将打开创建索引的表，如图17所示，用户可以指定索引列。

图 17 定义索引列

4．在“选项”对话框中，定义索引的相关选项，如图18所示。

图 18 定义索引选项

选项说明：

l “删除现有索引”，指定在创建新索引之前删除任何现有的同名索引。“删除现有索引”只有当对话框处于重新创建状态才变为有效，在这种情况下，“重新生成索引”不可用。

l “重新生成索引”，重新创建索引。当对话框打开的时候，选项默认没有选中。

l “忽略重复的值”，指定忽略重复值。

l “自动重新计算统计信息”，指定不重新计算索引统计。不推荐使用本选项。

l “在访问索引时使用行锁”，支持行层锁。默认情况下，SQL Server在页层、行层或者表层锁机制之间进行选择。当清除该选项，索引不使用行层锁机制。默认情况下，选中该选项。

l “在访问索引时使用页锁”，支持也层锁机制。默认情况下，SQL Server在页层、行层或者表层锁机制之间进行选择。当清除该选项，索引不使用页层锁机制。默认情况下，选中该选项。

l “将中间排序结果存储在tempdb中”，将用于创建索引的中间排序结果存储在tempdb数据库中。默认情况下，没有选中该选项，该选项只有在对话框处于重新创建状态或者重新生成状态的时候才可用。

l “设置填充因子”，指定 SQL Server 在创建索引过程中，对各索引页的叶级所进行填充的程度。

l “填充索引”，指定填充索引。填充索引在索引的每个内部节点上留出空格。

l “运行在创建索引时在线处理DML语句”：允许用户并发在索引操作过程中，访问底层表、聚集索引数据和任何相关非聚集索引。该选项默认情况下没有被选中，只有在对话框处于重新创建状态才可用。

l “设置最大并行度”，限制在并行计划执行过程中使用的处理器数量，默认值为0，使用实际可用的CPU，取值为1表示压缩并行计划生成；设置大于1的数字，表示在单个查询过程中使用的处理器的最大数量。

l “使用索引”，将索引可用。

5．在“包含性列”对话框中，定义索引中包含的其他列，如图19所示。当选择聚集索引和XML索引时，无法添加列，当选择非聚集索引类型的时候，可以添加索引列。

图 19 定义索引所包含的其他列

6．在“存储”对话框中，定义索引的存储选项，包括定义文件组、分区模式等参数，如图20所示。

图 20 定义索引的存储选项

7．当完成定义之后，单击“确定”按钮，完成对索引的定义，SQL Server数据库引擎将创建索引。

（2）使用Transact-SQL语句创建索引

SQL Server提供了CREATE INDEX Transact-SQL语法，用于创建索引，其语法格式如下：

创建关系索引的语法：
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = number_of_processors
}

创建XML索引的语法：

CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH } ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = number_of_processors
}


向后兼容的关系索引创建语法：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | default } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
    table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

参数说明：

l UNIQUE，为表或视图创建唯一索引（不允许存在索引值相同的两行）。视图上的聚集索引必须是 UNIQUE 索引。在创建索引时，如果数据已存在，Microsoft SQL Server会检查是否有重复值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行这种检查。如果存在重复的键值，将取消 CREATE INDEX 语句，并返回错误信息，给出第一个重复值。当创建 UNIQUE 索引时，有多个 NULL 值被看作副本。如果存在唯一索引，那么会产生重复键值的 UPDATE 或 INSERT 语句将回滚，SQL Server 将显示错误信息。即使 UPDATE 或 INSERT 语句更改了许多行但只产生了一个重复值，也会出现这种情况。如果在有唯一索引并且指定了 IGNORE_DUP_KEY 子句情况下输入数据，则只有违反 UNIQUE 索引的行才会失败。在处理 UPDATE 语句时，IGNORE_DUP_KEY 不起作用。SQL Server 不允许为已经包含重复值的列创建唯一索引，无论是否设置了 IGNORE_DUP_KEY。如果尝试这样做，SQL Server 会显示错误信息；重复值必须先删除，才能为这些列创建唯一索引。

l CLUSTERED，创建一个对象，其中行的物理排序与索引排序相同，并且聚集索引的最低一级（叶级）包含实际的数据行。一个表或视图只允许同时有一个聚集索引。具有聚集索引的视图称为索引视图。必须先为视图创建唯一聚集索引，然后才能为该视图定义其它索引。在创建任何非聚集索引之前创建聚集索引。创建聚集索引时重建表上现有的非聚集索引。如果没有指定 CLUSTERED，则创建非聚集索引。

l NONCLUSTERED，创建一个指定表的逻辑排序的对象。对于非聚集索引，行的物理排序独立于索引排序。非聚集索引的叶级包含索引行。每个索引行均包含非聚集键值和一个或多个行定位器（指向包含该值的行）。如果表没有聚集索引，行定位器就是行的磁盘地址。如果表有聚集索引，行定位器就是该行的聚集索引键。每个表最多可以有 249 个非聚集索引（无论这些非聚集索引的创建方式如何：是使用 PRIMARY KEY 和 UNIQUE 约束隐式创建，还是使用 CREATE INDEX 显式创建）。每个索引均可以提供对数据的不同排序次序的访问。对于索引视图，只能为已经定义了聚集索引的视图创建非聚集索引。因此，索引视图中非聚集索引的行定位器一定是行的聚集键。

l index_name，是索引名。索引名在表或视图中必须唯一，但在数据库中不必唯一。索引名必须遵循标识符规则。

l database_name，索引所在的数据库名。

l schema_name，索引所在的模式名。

l table_or_view_name，包含要创建索引的列的表或者视图。可以选择指定数据库和表所有者。

l column，应用索引的列。指定两个或多个列名，可为指定列的组合值创建组合索引。在 table 后的圆括号中列出组合索引中要包括的列（按排序优先级排列）。

l [ ASC | DESC ]，确定具体某个索引列的升序或降序排序方向。默认设置为 ASC。

l xml_column_name，表示索引创建所在的XML列。在单个XML索引定义中，只能定义在一个XML列上。但是，可以在单个XML列上创建多个辅助XML索引。主XML索引不能在计算XML列上创建。

l USING XML INDEX xml_index_name，定义在创建副本XML索引中使用的主XML索引。

l FOR { VALUE | PATH }，FOR VALUE用于在主XML索引的VALUE, HID, PK, XID列上创建副本索引。FOR PATH用于在主XML索引的HID, VALUE, PK, XID列上创建副本索引。

l INCLUDE (column [ ,... n ] )，定义添加到非聚集索引页级的非键列。

l ON partition_scheme_name(column_name)，定义分区模式。分区模式用于定义分区索引映射的文件组。

l ON filegroup_name，在给定的 filegroup 上创建指定的索引。该文件组必须已经通过执行 CREATE DATABASE 或 ALTER DATABASE 创建。

l PAD_INDEX = { ON | OFF }，指定索引中间级中每个页（节点）上保持开放的空间。PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。默认情况下，给定中间级页上的键集，SQL Server 将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行。如果为 FILLFACTOR 指定的百分比不够大，无法容纳一行，SQL Server 将在内部使用允许的最小值替代该百分比。

l FILLFACTOR =fillfactor，指定在 SQL Server 创建索引的过程中，各索引页叶级的填满程度。如果某个索引页填满，SQL Server 就必须花时间拆分该索引页，以便为新行腾出空间，这需要很大的开销。对于更新频繁的表，选择合适的 FILLFACTOR 值将比选择不合适的 FILLFACTOR 值获得更好的更新性能。FILLFACTOR 的原始值将在 sysindexes 中与索引一起存储。如果指定了 FILLFACTOR，SQL Server 会向上舍入每页要放置的行数。例如，发出 CREATE CLUSTERED INDEX ...FILLFACTOR = 33 将创建一个 FILLFACTOR 为 33% 的聚集索引。假设 SQL Server 计算出每页空间的 33% 为 5.2 行。SQL Server 将其向上舍入，这样，每页就放置 6 行。用户指定的 FILLFACTOR 值可以从 1 到 100。如果没有指定值，默认值为 0。如果 FILLFACTOR 设置为 0，则只填满叶级页。可以通过执行 sp_configure 更改默认的 FILLFACTOR 设置。只有不会出现 INSERT 或 UPDATE 语句时（例如对只读表），才可以使用 FILLFACTOR 100。如果 FILLFACTOR 为 100，SQL Server 将创建叶级页 100% 填满的索引。如果在创建 FILLFACTOR 为 100% 的索引之后执行 INSERT 或 UPDATE，会对每次 INSERT 操作以及有可能每次 UPDATE 操作进行页拆分。如果 FILLFACTOR 值较小（0 除外），就会使 SQL Server 创建叶级页不完全填充的新索引。例如，如果已知某个表包含的数据只是该表最终要包含的数据的一小部分，那么为该表创建索引时，FILLFACTOR 为 10 会是合理的选择。FILLFACTOR 值较小还会使索引占用较多的存储空间。表2说明如何在已指定 FILLFACTOR 的情况下填充索引页。

表2 填充索引页说明

FILLFACTOR	中间级页	叶级页
0	一个可用项	100% 填满
1% -99	一个可用项	<= FILLFACTOR% 填满
100%	一个可用项	100% 填满

l SORT_IN_TEMPDB = { ON | OFF }，指定用于生成索引的中间排序结果将存储在 tempdb 数据库中。如果 tempdb 与用户数据库不在同一磁盘集，则此选项可能减少创建索引所需的时间，但会增加创建索引时使用的磁盘空间。

l IGNORE_DUP_KEY = { ON | OFF }，控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。如果为索引指定了 IGNORE_DUP_KEY，并且执行了创建重复键的 INSERT 语句，SQL Server 将发出警告消息并忽略重复的行。如果没有为索引指定 IGNORE_DUP_KEY，SQL Server 会发出一条警告消息，并回滚整个 INSERT 语句。表3显示何时可使用 IGNORE_DUP_KEY。

表 3 IGNORE_DUP_KEY使用情况

索引类型	选项
聚集	不允许
唯一聚集	允许使用 IGNORE_DUP_KEY
非聚集	不允许
唯一非聚集	允许使用 IGNORE_DUP_KEY

l STATISTICS_NORECOMPUTE = { ON | OFF}，指定过期的索引统计不会自动重新计算。若要恢复自动更新统计，可执行没有 NORECOMPUTE 子句的 UPDATE STATISTICS。

l DROP_EXISTING = { ON | OFF }，指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必须与现有的索引名相同。因为非聚集索引包含聚集键，所以在除去聚集索引时，必须重建非聚集索引。如果重建聚集索引，则必须重建非聚集索引，以便使用新的键集。为已经具有非聚集索引的表重建聚集索引时（使用相同或不同的键集），DROP_EXISTING 子句可以提高性能。DROP_EXISTING 子句代替了先对旧的聚集索引执行 DROP INDEX 语句，然后再对新的聚集索引执行 CREATE INDEX 语句的过程。非聚集索引只需重建一次，而且还只是在键不同的情况下才需要。如果键没有改变（提供的索引名和列与原索引相同），则 DROP_EXISTING 子句不会重新对数据进行排序。在必须压缩索引时，这样做会很有用。无法使用 DROP_EXISTING 子句将聚集索引转换成非聚集索引；不过，可以将唯一聚集索引更改为非唯一索引，反之亦然。

l ONLINE = { ON | OFF }，当设置为ON的时候，长项表锁不允许对底层表进行查询或者更新操作。当设置为OFF的时候，应用表锁机制。默认值为OFF。

l ALLOW_ROW_LOCKS = { ON | OFF }，当设置为ON，当访问索引时，支持行级锁。当设置为OFF时，不使用行级锁。默认为ON。

l ALLOW_PAGE_LOCKS = { ON | OFF }，当设置为ON，当访问索引时，支持页级锁。当设置为OFF时，不使用页级锁。默认为ON。

l MAXDOP = number_of_processors，覆盖“max degree of parallelism”配置选项的值。使用“max degree of parallelism”限制在并行计划执行过程中使用的处理器数量，默认值为0，使用实际可用的CPU，取值为1表示压缩并行计划生成；设置大于1的数字，表示在单个查询过程中使用的处理器的最大数量。

例如，下面给出创建索引的SQL语句。

USE [EAMS]

CREATE NONCLUSTERED INDEX [ix_name] ON [dbo].[mrBaseInf]

(

[EmpName] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

2.3 查看和修改索引

当在数据库上创建了索引之后，可以在SQL Server Management Studio中查看和修改索引，为此，可以采用SQL Server Management Studio的图形工具或者使用Transact-SQL语句修改索引。

（1）使用SQL Server Management Studio图形工具查看和修改索引

使用SQL Server Management Studio图形工具查看和修改索引步骤如下：

1．打开SQL Server Management Studio，在“对象资源管理器”视图中展开“Database”节点，然后选择某个数据库，如选择数据库“EAMS”，双击“表”节点，展开表，选择“索引”节点，如图21所示。

图 21 选择“Index”节点

2．展开“索引”节点，可以看到在表上所创建的索引，如图22所示。

图 22 展开“索引”

3．选择某个索引，单击鼠标右键选择“属性”菜单项，打开“索引属性”对话框，用户可以查看和修改索引选项，如图23所示。

图 23 查看和修改索引

1. 当修改完成之后，单击“确定”按钮，保存索引修改。

（2）使用Transact-SQL语句查看和修改索引

当需要修改索引的选项时，可以使用ALTER INDEX语句完成，其语法格式如下：

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
    table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = number_of_processors
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = number_of_processors
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS= { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

参数说明请参考Create Index语句语法的参数说明。

如果需要修改索引的名称，可以使用sp_rename存储过程完成，其语法格式如下：

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'

[ , [ @objtype = ] 'object_type' ] ;

其中：

l [ @objname =] 'object_name'，表示对象名；

l [ @newname =] 'new_name'，表示新的名称。

l [ @objtype =] 'object_type'，在此为“INDEX”。

返回0，表示成功，返回非零表示失败。

当需要查看索引时，SQL Server提供了几个视图和函数，用于显示索引元数据信息。例如，可以显示特定表上的索引类型、索引选项和索引整个空间使用情况。

用于返回索引元数据的视图包括：

l sys.indexes ，包括索引类型、文件组或者分区模式ID、索引当前选项的设置。

l sys.index_columns ，包括列ID、在索引中的位置、类型和排序规则；

l sys.stats ，同索引相关的统计信息；

l sys.stats_columns ，同统计相关的列ID；

l sys.xml_indexes，XML索引类型、路径表达式、辅助类型和描述信息。

同时，SQL Sever还提供了几个函数，用于返回元数据：

l sys.dm_db_index_physical_stats (Transact-SQL) ，显示索引规模和分段统计信息；

l sys.dm_db_index_operational_stats (Transact-SQL) ，显示当前索引和表I/O统计信息；

l INDEXKEY_PROPERTY ，显示在索引中索引列的位置和列排序规则；

l INDEXPROPERTY ，显示索引类型、当前设置；

l INDEX_COL，显示索引的键列名。

2.4 删除索引

使用索引可以提高数据库查询的性能，但索引同样需要数据库的开销。当某些索引不需要的时候，需要删除这些索引。删除索引可以直接在SQL Server Management Studio中采用图形方式完成，也可以通过执行Transact-SQL语句完成。

（1）使用SQL Server Management Studio图形工具删除索引

使用SQL Server Management Studio图形工具删除索引的步骤如下：

1．打开SQL Server Management Studio，在“对象资源管理器”视图中展开“数据库”节点，然后选择某个数据库，如选择 “EAMS”，双击“表”节点，展开表，选择“索引”节点，如图24所示。

图 24 选择“索引”节点

2．展开“索引”节点，可以看到在表上所创建的索引，如图25所示。

图 25 展开“索引”

3．选择某个需要删除的索引，单击鼠标右键，选择“删除”菜单项，打开“删除对象”对话框，如图26所示。

图 26 “删除对象”对话框

4．当确定删除时，单击“确定”按钮，完成删除操作。

（2）使用Transact-SQL语句删除索引

当需要使用Transact-SQL语句删除索引时，采用DROP INDEX语句，其语法格式如下：

DROP INDEX
{ <drop_relational_or_xml_index> [ ,...n ] 
| <drop_backward_compatible_index> [ ,...n ]
}

<drop_relational_or_xml_index> ::=
    index_name ON <object> 
    [ WITH ( <drop_clustered_index_option> [ ,...n ] ) ]

<drop_backward_compatible_index> ::=
    [ owner_name. ] table_or_view_name.index_name

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<drop_clustered_index_option> ::=
{
    MAXDOP = number_of_processors
  | ONLINE = { ON | OFF }
  | MOVE TO { partition_scheme_name ( column_name ) 
            | filegroup_name
            | default 
            }
}

其中：

l index_name，表示需要删除的索引名；

l database_name，表示索引所在的数据库名称；

l schema_name，表示索引所在的模式名；

l table_or_view_name，表示索引所在的表或者视图的名称；

l <drop_clustered_index_option>，控制聚集索引选项，这个选项不能同其他索引类型一起使用。

l MAXDOP = number_of_processors，重写“max degree of parallelism”选项。

l ONLINE = ON | OFF，当设置为ON的时候，长项表锁不允许对底层表进行查询或者更新操作。当设置为OFF的时候，应用表锁机制。默认值为OFF。

l MOVE TO，定义聚集索引页级的数据行移动的位置。

l partition_scheme_name ( column_name )，定义存储结果表的分区模式。

l filegroup_name，定义存储结果表的文件组。

l DEFAULT，定义存储结果表的默认位置。

当某个索引不能明显改进查询效率时候，需要将索引删除，以节省资源，因为索引是需要耗费代价的。下面我们以删除2.3一节所创建的索引“ix_name”为例，使用Drop Index命令删除索引，其Transact-SQL语句如下：

USE [EAMS];

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N' ix_name ')

DROP INDEX ix_name ON mrBaseInf;

3 视图

视图是关系数据库的一个重要概念，它为数据呈现提供了更多的表现形式，并提供了数据的访问控制能力，让用户只看到他们权限限制范围内的数据，保护企业的敏感数据。本节将结合企业信息平台数据库的实例，介绍视图的基本概念，以及如何使用多种方式创建、修改、删除视图。

3.1 视图基础

视图（也称虚表）是用户查看数据表中数据的一种方式，用户可以通过它来浏览表中感兴趣的部分或全部数据。而数据库的物理存储位置仍然在表中，这些表称为视图的基表。视图可以从一个或多个基表中派生，也可以从其他视图中派生。需要注意，视图不是数据表，它仅是一些SQL查询语句的集合，作用是按照不同的要求从数据表中提取不同的数据。图27显示了在两个表上所创建的一个视图。

图 27 视图示例

使用视图，最大的优势如下：

l 限定特定的数据，视图让用户能够着重于他们所感兴趣的特定数据和所负责的特定任务。不必要的数据可以不出现在视图中。这同时增强了数据的安全性，因为用户只能看到视图中所定义的数据，而不是基础表中的数据。

l 简化数据操作，视图可以简化用户操作数据的方式。可将经常使用的联接、投影、联合查询和选择查询定义为视图，这样，用户每次对特定的数据执行进一步操作时，不必指定所有条件和限定。例如，一个用于报表目的，并执行子查询、外联接及聚合以从一组表中检索数据的复合查询，就可以创建为一个视图。视图简化了对数据的访问，因为每次生成报表时无须写或提交基础查询，而是查询视图。

l 自定义数据，视图允许用户以不同的方式查看数据，即使他们同时使用相同的数据时也如此。这在具有不同目的和技术水平的用户共享同一个数据库时尤为有利。例如，可定义一个视图以仅检索由客户经理处理的客户数据。视图可以根据使用该视图的客户经理的登录 ID 决定检索哪些数据。

l 导出和导入数据，可使用视图将数据导出至其它应用程序。例如，希望将进销存数据库中的销售数据导出到Excel中执行分析操作，可以为销售表创建一个视图，然后从视图中使用数据导出服务，将数据复制到Excel表中。

l 组合分区数据，Transact-SQL UNION 集合运算符可在视图内使用，以将来自不同表的两个或多个查询结果组合成单一的结果集。这在用户看来是一个单独的表，称为分区视图。例如，如果一个表含有北京的销售数据，另一个表含有上海的销售数据，即可从 UNION 创建这两个表的视图。该视图代表了这两个区域的销售数据。用分区视图时，首先创建几个相同的表，指定一个约束以决定可在各个表中添加的数据范围。视图即使用这些基表创建。当查询该视图时，SQL Server 自动决定查询所影响的表，并仅引用这些表。例如，如果一个查询指定只需要北京的销售数据，则 SQL Server 只读取含有北京销售数据的表，而并不访问其余的表。分区视图可基于来自多个异类源——如远程服务器——的数据，而不仅仅局限于同一数据库中的表。例如，要将分别存储组织中不同区域数据的几台远程服务器上的数据组合起来，可以创建分布式查询，从每个数据源中检索数据，然后基于这些分布式查询创建视图。所有查询都只从包含查询所请求数据的远程服务器上读取表中的数据，其它在视图中由分布式查询引用的服务器均不被访问。通过使用分区视图，数据的外观象是一个单一表，且能以单一表的方式进行查询，而无须手动引用真正的基础表。

到目前为止，视图所存在的最大问题是性能问题。那些不愿意使用视图的开发人员正确的指出了问题的所在：视图不是预编译的，所以它们会对性能产生较大的影响。当在SQL select语句中引用视图时，SQL Server需要通过以下这些步骤来解析视图：

l 将调用视图的SQL select语句和它引用的视图所包含的select语句合并为单个的查询。

l 必须对这个新的查询进行解析。如果在表名中没有指定所有者名字，就必须正确地做出应该引用哪个表的决定。例如，如果在数据库中存在着两个表，dbo.Client和bob.Client，那么，在Bob的查询中引用Client表的就应当是bob.Client，而在其它人的查询中引用的Client表就应当是dbo.Client。

l 必须进行安全性的处理和检查。如果从视图到基础表的所有权链是一致的，那么不管对视图有权限的用户是否对相应基础表拥有权限，他或她都可以通过视图来引用基础表（假定这个视图的所有者拥有对基础表的相应权限）。尽管如此，如果所有权链是断开的，或者在要通过视图对基础表进行更新的情况下，用户就必须拥有对所有权链中每个对象的权限才能够进行操作。所以检查安全性的工作会对性能产生较大的影响。

l 如果以前已经执行过具有同样类型的参数的查询，并且在现在处理的查询中包含了所有者名字，就可以利用SQL Server的自动参数化功能（auto-parameterization），重用以前存储的查询执行计划。如果没有为查询或者视图所包含的表指定所有者名字，或者是第一次执行合并后的查询，查询优化器就必须求出一个查询执行计划。

l 执行合并后的查询。

所以，视图究竟会对性能产生多大的影响呢？根据经验，写的好的存储过程比写的好的视图大约要快10％－20％。当然，编写存储过程需要使用很多的参数。

3.2 创建视图

开发视图具有两个方面的目标：第一个目标是让用户更容易的获取数据；第二个目标是保护数据的安全，防止用户进行非法的存取。通过创建为用户提供正确数据的视图，可以防止用户对于数据的错误查询和错误理解。

在创建视图前请考虑如下原则：

l 只能在当前数据库中创建视图。但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其它数据库中，甚至其它服务器上。

l 视图名称必须遵循标识符的规则，且对每个用户必须为唯一。此外，该名称不得与该用户拥有的任何表的名称相同。

l 可以在其它视图和引用视图的过程之上建立视图。Microsoft SQL Server 允许嵌套多达 32 级视图。

l 不能将规则或 DEFAULT 定义与视图相关联。

l 不能将 AFTER 触发器与视图相关联，只有 INSTEAD OF 触发器可以与之相关联。

l 定义视图的查询不可以包含 ORDER BY、COMPUTE 或 COMPUTE BY 子句或 INTO 关键字。

l 不能在视图上定义全文索引定义。

l 不能创建临时视图，也不能在临时表上创建视图。

l 不能除去参与到用 SCHEMABINDING 子句创建的视图中的表或视图，除非该视图已被除去或更改而不再具有架构绑定。另外，如果参与具有架构绑定的视图的表执行 ALTER TABLE 语句影响视图定义，则这些语句将失败。

l 不能对视图执行全文查询，但是如果查询所引用的表被配置为支持全文索引，就可以在视图定义中包含全文查询。

同时，尽量将视图用于即席查询，而不要将它们作为应用程序的核心。基于这个思想，下面给出了为即席查询创建视图时需要采用的一些原则：

l 使用视图来简化复杂的连接，从而将用户难以理解的数据模式中用来连接数据的键隐藏起来。设计良好的视图应当使用户方便地获取到他们感兴趣的数据。

l 如果不使用视图来预先实现一些复杂的连接，即席查询就会造成潜在的数据完整性问题。即使用户知道什么是连接，他们也很少能够搞清楚什么时候应当使用内连接，什么时候应使用外连接，而错误的连接就会导致错误的结果。

l 将复杂的聚合查询存储为视图。因为在聚合查询中，所有的列都必须出现在聚合函数或者group by子句中，许多复杂的聚合查询往往会包含子查询以便返回那些非聚合的列。如果你能够为即席查询用户预先构造好这些复杂的查询，他们一定会对你感激不尽。

l 使用别名将那些含义不清的列名改为可以理解的列名。就像SQL select语句可以使用列别名或者命名的范围变量（表别名）来改变表或者列的名字一样，在视图中也可以使用这些特性来为用户产生更具可读性的结果集。

l 只把用户感兴趣的列包含在视图中。如果把用户不感兴趣的列排除在视图之外，视图就会变得更易于查询。包含在视图中的列称为投影列，这意味着它们只会从整个基础表中投影出选定的数据。

l 通用的、动态的视图会具有较长的生命周期，也更为实用。单一用途的视图将会很快变得过时，并把数据库弄得乱七八糟。创建视图时，应当假定用户将会把它与where子句一道使用以返回一个数据的子集。如果用户不指定where条件，视图就应当返回全部的行。

因为视图只是存储的SQL select，所以创建视图要从编写select语句开始。可以从任意的工具将SQL select语句－只要它是一个合法的SQL select语句（视图对于SQL select语句只有很少的限制）剪贴到视图中去。

在SQL Server中创建视图可以采用图形化方式，也可以在查询分析器中执行Transact-SQL语句。

（1）使用图形化方式创建视图

SQL Server Management Studio提供了创建视图的图形工具，可以以图形化的方式完成对视图的创建操作，具体步骤如下：

1．打开SQL Server Management Studio，如图28所示。

图 28 SQL Server Management Studio

2．在“对象资源管理器”视图中，展开“数据库”文件夹，选择需要创建视图的数据库，展开数据库节点，选择“视图”节点，单击鼠标右键，选择“新建视图”，如图29所示。

图 29 选择“新建视图”菜单项

3．打开添加“表”对话框，如图30所示。

图30 添加表

4．单击“添加”按钮，执行视图定义，用户可以添加列名，定义视图的语句，例如，图31显示了一个视图定义的示例。

图 31 创建视图的图形界面

5．单击工具栏上的保存按钮，命名所创建的视图，如图32所示，SQL Server数据库引擎将创建该视图。

图32 命名视图

（2）使用Transact-SQL语句创建视图

同样，可以使用Transact-SQL语句创建视图，其语法格式如下：

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]

[ WITH <view_attribute> [ ,...n ] ]

[WITH <common_table_expression>]

select_statement

[ WITH CHECK OPTION ]

[ ; ]

<view_attribute> ::=

{

[ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ] }

其中：

l schema_name，表示视图所属于的模式名。

l view_name，是视图的名称。视图名称必须符合标识符规则。可以选择是否指定视图所有者名称。

l column，是视图中的列名。只有在下列情况下，才必须命名 CREATE VIEW 中的列：当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。还可以在 SELECT 语句中指派列名。如果未指定 column，则视图列将获得与 SELECT 语句中的列相同的名称。

l n，是表示可以指定多列的占位符。

l AS，是视图要执行的操作。

l WITH <common_table_expression>，定义临时命名结果集或者视图，也称为公共表表达式，在SELECT语句的范围内定义。结果集是从执行简单查询得到的。

l select_statement，是定义视图的 SELECT 语句。该语句可以使用多个表或其它视图。若要从创建视图的 SELECT 子句所引用的对象中选择，必须具有适当的权限。视图不必是具体某个表的行和列的简单子集。可以用具有任意复杂性的 SELECT 子句，使用多个表或其它视图来创建视图。在索引视图定义中，SELECT 语句必须是单个表的语句或带有可选聚合的多表 JOIN。对于视图定义中的 SELECT 子句有几个限制。CREATE VIEW 语句：不能包含 COMPUTE 或 COMPUTE BY 子句；不能包含 ORDER BY 子句，除非在 SELECT 语句的选择列表中也有一个 TOP 子句；不能包含 INTO 关键字；不能引用临时表或表变量。

l CHECK OPTION，强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。通过视图修改行时，WITH CHECK OPTION 可确保提交修改后，仍可通过视图看到修改的数据。

l ENCRYPTION，表示 SQL Server 加密包含 CREATE VIEW 语句文本的系统表列。使用 WITH ENCRYPTION 可防止将视图作为 SQL Server 复制的一部分发布。

l SCHEMABINDING，将视图绑定到架构上。指定 SCHEMABINDING 时，select_statement 必须包含所引用的表、视图或用户定义函数的两部分名称 (owner.object)。不能除去参与用架构绑定子句创建的视图中的表或视图，除非该视图已被除去或更改，不再具有架构绑定。否则，SQL Server 会产生错误。另外，如果对参与具有架构绑定的视图的表执行 ALTER TABLE 语句，而这些语句又会影响该架构绑定视图的定义，则这些语句将会失败。

l VIEW_METADATA，指定为引用视图的查询请求浏览模式的元数据时，SQL Server 将向 DBLIB、ODBC 和 OLE DB API 返回有关视图的元数据信息，而不是返回基表或表。浏览模式的元数据是由 SQL Server 向客户端 DB-LIB、ODBC 和 OLE DB API 返回的附加元数据，它允许客户端 API 实现可更新的客户端游标。浏览模式的元数据包含有关结果集内的列所属的基表信息。对于用 VIEW_METADATA 选项创建的视图，当描述结果集中视图内的列时，浏览模式的元数据返回与基表名相对的视图名。当用 VIEW_METADATA 创建视图时，如果该视图具有 INSERT 或 UPDATE INSTEAD OF 触发器，则视图的所有列（timestamp 除外）都是可更新的。

例如，下面的SQL语句用于创建视图v_mrBaseInf。

USE [EAMS]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE VIEW [dbo].[v_mrBaseInf]

SELECT EmpID, EmpName, DepID, LoginID, Sex

FROM dbo.mrBaseInf

3.3 使用视图修改数据

建立视图不但可以用于查询数据库中的数据，也可以通过视图修改视图基表的数据，其行为类似于修改表中数据。为此，可以使用UPDATE、INSERT和DELETE语句修改数据，或者使用bcp工具和BULK INSERT语句。但是，在更新视图的时候，有一些限制：

l 任何修改（包括UPDATE、INSERT、DELETE语句）只能引用来自一个基表的列。

l 视图中被修改的列必须直接引用表列的数据，不能从其他方式派生而来，如通过AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR、VARP聚合函数，或者通过使用集合操作符（UNION, UNION ALL, CROSSJOIN, EXCEPT和INTERSECT）计算得到。

l 被修改的列不能受GROUP BY、HAVING或者DISTINCT子句的影响。

如果视图是可更新的，就可以使用INSERT向视图基表插入数据，使用UPDATE修改视图基表的数据，使用DELETE语句删除视图基表的数据。下面我们分别给出INSERT、UPDATE和DELETE语句的语法和一些示例。

（1）INSERT语句

INSERT语句的语法如下：

[ WITH <common_table_expression> [ ,...n ] ]

INSERT

[ TOP ( expression ) [ PERCENT ] ]

[ INTO]

{

[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]

}

{

[ ( column_list ) ]

[ <OUTPUT Clause> ]

{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )

| derived_table

| execute_statement

}

| DEFAULT VALUES

[; ]

<object> ::=

{

[ server_name . database_name . schema_name .

| database_name .[ schema_name ] .

| schema_name .

]

table_or_view_name

}

其中：

l WITH <common_table_expression>，定义临时命名结果集，也称为公共表表达式，定义在INSERT语句的范围内，结果集是执行SELECT语句得到的。

l TOP (expression) [ PERCENT ]，定义将要删除的随机行的数量或者百分比，expression要么是数量，要么是百分比。

l INTO，一个可选的关键字，可以将它用在 INSERT 和目标表之间。

l server_name，是表或者视图所在的服务器名。

l database_name，数据库的名称。

l schema_name，视图或者表所在的模式名。

l table_or view_name，将要接收数据的表或视图的名称。

l rowset_function_limited，是 OPENQUERY 或 OPENROWSET 函数。

l WITH ( <table_hint_limited> [... n ] )，指定目标表所允许的一个或多个表提示。需要有 WITH 关键字和圆括号。不允许有 READPAST、NOLOCK 和 READUNCOMMITTED。

l ( column_list )，要在其中插入数据的一列或多列的列表。必须用圆括号将 column_list 括起来，并且用逗号进行分隔。如果某列不在 column_list 中，则 SQL Server必须能够基于该列的定义提供一个值；否则不能装载行。如果列满足下面的条件，那么 SQL Server 将自动为列提供值：1）具有 IDENTITY 属性。使用下一个增量标识值。2）有默认值。使用列的默认值。3）具有 timestamp 数据类型。使用当前的时间戳值。4）是可空的。使用空值。当向标识列中插入显式的值时，必须使用 column_list 及 VALUES 列表，并且对于表，SET IDENTITY_INSERT 选项必须是 ON。

l OUTPUT Clause，返回插入的行作为INSERT操作的一部分。OUTPUT子句在任何DML语句模板视图或者远程表中不支持。

l VALUES，引入要插入的数据值的列表。对于 column_list（如果已指定）中或者表中的每个列，都必须有一个数据值。必须用圆括号将值列表括起来。如果 VALUES 列表中的值与表中列的顺序不相同，或者未包含表中所有列的值，那么必须使用 column_list 明确地指定存储每个传入值的列。

l DEFAULT，强制 SQL Server 装载为列定义的默认值。如果对于某列并不存在默认值，并且该列允许 NULL，那么就插入 NULL。对于使用 timestamp 数据类型定义的列，插入下一个时间戳值。DEFAULT 对标识列无效。

l Expression，一个常量、变量或表达式。表达式不能包含 SELECT 或 EXECUTE 语句。

l derived_table，任何有效的 SELECT 语句，它返回将装载到表中的数据行。

l execute_statement，任何有效的 SELECT 语句，它返回将装载到表中的数据行。如果将 execute_statement 与 INSERT 一起使用，那么每个结果集都必须与表中或 column_list 中的列兼容。execute_statement 可以用来执行同一服务器或远程服务器上的存储过程。执行远程服务器中的过程，然后将结果集返回到本地服务器，并装载到本地服务器的表中。如果 execute_statement 使用 READTEXT 语句返回数据，那么每个单独的 READTEXT 语句最多可以返回 1 MB (1024 KB) 的数据，也可以将 execute_statement 与扩展过程一起使用，并且插入扩展过程的主线程返回的数据。不会插入不是主线程的其它线程中的输出结果。

l DEFAULT VALUES，强制新行包含为每个列所定义的默认值。

下面提供了一个示例，定义了一个视图，然后使用视图将数据插入到视图的基表中。

USE AdventureWorks;

IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL

DROP TABLE dbo.T1;

IF OBJECT_ID ('dbo.V1', 'V') IS NOT NULL

DROP VIEW dbo.V1;

CREATE TABLE T1 ( column_1 int, column_2 varchar(30));

CREATE VIEW V1 AS

SELECT column_2, column_1

FROM T1;

INSERT INTO V1

VALUES ('Row 1',1);

SELECT *

FROM T1;

SELECT *

FROM V1;

（2）UPDATE语句

UPDATE语句的语法如下：

[ WITH <common_table_expression> [...n] ]

UPDATE

[ TOP ( expression ) [ PERCENT ] ]

{ <object> | rowset_function_limited

[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]

}

SET

{ column_name = { expression | DEFAULT | NULL }

| { udt_column_name.{ { property_name = expression

| field_name = expression }

| method_name ( argument [ ,...n ] )

}

| column_name { .WRITE ( expression , @Offset , @Length ) }

| @variable = expression

| @variable = column = expression } [ ,...n ]

} [ ,...n ]

[ <OUTPUT Clause> ]

[ FROM{ <table_source> } [ ,...n ] ]

[ WHERE { <search_condition>

| { [ CURRENT OF

{ { [ GLOBAL ] cursor_name }

| cursor_variable_name

}

]

}

]

[ OPTION ( <query_hint> [ ,...n ] ) ]

[ ; ]

<object> ::=

{

[ server_name . database_name . schema_name .

| database_name .[ schema_name ] .

| schema_name .

]

table_or_view_name

}

其中：

l WITH <common_table_expression>，定义临时命名结果集，也称为公共表表达式，定义在INSERT语句的范围内，结果集是执行SELECT语句得到的。

l TOP (expression) [ PERCENT ]，定义将要删除的随机行的数量或者百分比，expression要么是数量，要么是百分比。

l server_name，是表或者视图所在的服务器名。

l database_name，数据库的名称。

l schema_name，视图或者表所在的模式名。

l table_or view_name，将要接收数据的表或视图的名称。

l rowset_function_limited，是 OPENQUERY 或 OPENROWSET 函数。

l WITH ( <table_hint_limited> [... n ] )，指定目标表所允许的一个或多个表提示。需要有 WITH 关键字和圆括号。不允许有 READPAST、NOLOCK 和 READUNCOMMITTED。

l SET，指定要更新的列或变量名称的列表。

l column_name，含有要更改数据的列的名称。column_name 必须驻留于 UPDATE 子句中所指定的表或视图中。标识列不能进行更新。如果指定了限定的列名称，限定符必须同 UPDATE 子句中的表或视图的名称相匹配。

l expression，变量、字面值、表达式或加上括弧的返回单个值的 subSELECT 语句。expression 返回的值将替换 column_name 或 @variable 中的现有值。

l DEFAULT，指定使用对列定义的默认值替换列中的现有值。如果该列没有默认值并且定义为允许空值，这也可用来将列更改为 NULL。

l udt_column_name，已声明的变量，该变量将设置为 expression 所返回的值。SET @variable = column = expression 将变量设置为与列相同的值。这与 SET @variable = column, column = expression 不同，后者将变量设置为列更新前的值。

l property_name | field_name，是用户自定义类型的公共属性或者公共数据成员。

l method_name ( argument [ ,... n] )，是带一个或者多个参数的udt_column_name的非静态公共方法。

l .WRITE (expression,@Offset,@Length)，定义需要修改的column_name的值。

l @ variable，是用于存储表达式返回值的变量。

l <OUTPUT_Clause>，返回更新的数据或者表达式。

l FROM <table_source>，指定用表来为更新操作提供准则。

l WHERE，指定条件来限定所更新的行。

l <search_condition>，指定联接所基于的条件。

l CURRENT OF，指定更新在指定游标的当前位置进行。

l GLOBAL，指定 cursor_name 指的是全局游标。

l cursor_name，要从中进行提取的开放游标的名称。如果同时存在名为 cursor_name 的全局游标和局部游标，则在指定了 GLOBAL 时，cursor_name 指的是全局游标。如果未指定 GLOBAL，则 cursor_name 指局部游标。游标必须允许更新。

l cursor_variable_name，游标变量的名称。cursor_variable_name 必须引用允许更新的游标。

l OPTION ( <query_hint> [ ,... n ] )，指定优化程序提示用于自定义 SQL Server 的语句处理。

（3）DELETE语句

DELETE语句的语法如下：

[ WITH <common_table_expression> [ ,...n ] ]

DELETE

[ TOP ( expression ) [ PERCENT ] ]

[ FROM ]

{ <object> | rowset_function_limited

[ WITH ( <Table Hint> [ ...n ] ) ]

}

[ <OUTPUT Clause> ]

[ FROM <table_source> [ ,...n ] ]

[ WHERE { <search_condition>

| { [ CURRENT OF

{ { [ GLOBAL ] cursor_name }

| cursor_variable_name

}

]

}

]

[ OPTION ( <Query Hint> [ ,...n ] ) ]

[; ]

<object> ::=

{

[ server_name.database_name.schema_name.

| database_name. [ schema_name ] .

| schema_name.

]

table_or_view_name

}

其中：

l WITH <common_table_expression>，定义临时命名结果集，也称为公共表表达式，定义在INSERT语句的范围内，结果集是执行SELECT语句得到的。

l TOP (expression) [ PERCENT ]，定义将要删除的随机行的数量或者百分比，expression要么是数量，要么是百分比。

l FROM，是一个可选的关键字，用于在DELETE关键字和target table_or_view_name或者rowset_function_limited之间。

l server_name，是表或者视图所在的服务器名。

l database_name，数据库的名称。

l schema_name，视图或者表所在的模式名。

l table_or view_name，将要接收数据的表或视图的名称。

l rowset_function_limited，是 OPENQUERY 或 OPENROWSET 函数。

l WITH ( <table_hint_limited> [... n ] )，指定目标表所允许的一个或多个表提示。需要有 WITH 关键字和圆括号。不允许有 READPAST、NOLOCK 和 READUNCOMMITTED。

l OUTPUT_Clause，返回删除的行或者表达式。

l FROM <table_source>，指定附加的 FROM 子句。这个对 DELETE 的 Transact-SQL 扩展使您得以从 <table_sources> 指定数据，并从第一个 FROM 子句内的表中删除相应的行。

l WHERE，指定用于限制删除行数的条件。如果没有提供 WHERE 子句，则 DELETE 删除表中的所有行。基于 WHERE 子句中所指定的条件，有两种形式的删除操作。

l <search_condition>，指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。

l CURRENT OF，指定在指定游标的当前位置完成 DELETE。

l GLOBAL，定义cursor_name引用一个全局游标。

l cursor_name，是从其中进行提取的打开游标的名称。当全局和局部游标都以 cursor_name 作为它们的名称存在时，如果指定 GLOBAL，则 cursor_name 引用全局游标，如果未指定 GLOBAL，则 cursor_name 引用局部游标。游标必须允许更新。

l cursor_variable_name，是游标变量的名称。游标变量必须引用允许更新的游标。

l OPTION ( <query_hint> [ ,... n] )，是表示使用优化程序提示自定义 SQL Server 的语句处理的关键字。

3.4 修改视图

视图创建完成之后，可以支持应用程序的开发。但是，应用往往是经常发生变化的，因此也要求视图也发生变化，才能适合特定需求。SQL Server提供了修改视图的功能，完成对视图的修改操作。

通常情况下，完成对视图的修改可以采用两种方式：第一种是删除原有的视图，然后重新创建所需要的视图，另一种方式是使用ALTER VIEW语句。其语法格式如下：

ALTER VIEW [ database_name . [ schema_name ] . | schema_name . ] view_name [ ( column [ ,...n ] ) ]

[ WITH < view_attribute > [ ,...n ] ]

select_statement

[ WITH CHECK OPTION ]

< view_attribute > ::=

{

[ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ]

}

其中：

l database_name，视图所在的数据库。

l schema_name，视图所属于的模式名。

l view_name，是要更改的视图

l column，是一列或多列的名称，用逗号分开，将成为给定视图的一部分。

l n，是表示 column 可重复 n 次的占位符。

l ENCRYPTION，加密 syscomments 表中包含 ALTER VIEW 语句文本的条目。使用 WITH ENCRYPTION 可防止将视图作为 SQL Server 复制的一部分发布。

l SCHEMABINDING，将视图绑定到架构上。指定 SCHEMABINDING 时，select_statement 必须包含由所引用的表、视图或用户定义函数组成的两部分名称 (owner.object)。不能除去参与到用架构绑定子句创建的视图中的视图或表，除非该视图已被除去或更改而不再具有架构绑定。否则，SQL Server 会产生错误。另外，对于参与具有架构绑定的视图的表，如果其上的 ALTER TABLE 语句影响了该视图的定义，则这些语句将会失败。

l VIEW_METADATA，在为引用视图的查询请求浏览模式元数据时，指定 SQL Server 将向 DBLIB、ODBC 和 OLE DB API 返回有关视图的元数据信息，而不是返回基表或表。浏览模式元数据是由 SQL Server 向客户端 DB-LIB、ODBC 和 OLE DB API 返回的附加元数据，它允许客户端 API 实现可更新的客户端游标。浏览模式元数据包含有关结果集内的列所属基表的信息。对于用 VIEW_METADATA 选项创建的视图，当在结果集中描述视图内的列时，浏览模式元数据返回相对于基表名的视图名。当用 VIEW_METADATA 创建视图时，如果该视图具有 INSERT 或 UPDATE INSTEAD OF 触发器，则视图的所有列（timestamp 除外）都是可更新的。

l AS，是视图要执行的操作。

l select_statement，是定义视图的 SELECT 语句。

l WITH CHECK OPTION，强制视图上执行的所有数据修改语句都必须符合由定义视图的 select_statement 设置的准则。

3.5 删除视图

当视图创建完成之后，可能因为应用程序不再需要，需要删除该视图。在删除视图的时候，底层数据表是不受影响的。

删除视图可以直接在SQL Server Management Studio中采用图形方式删除，也可以使用Transact-SQL语句DROP VIEW完成。

使用SQL Server Management Studio删除视图的步骤如下：

1．打开SQL Server Management Studio，在“对象资源管理器”视图中展开视图所在的数据库，双击“视图”节点，选择需要删除的视图，单击鼠标右键，选择“删除”菜单项，如图33所示。

图 33 删除视图

2．打开“删除对象”对话框，当确定删除该视图的时候，单击“确定”按钮，数据库引擎将删除所选择的视图，如图34所示。

图 34 “Delete Object”对话框

同样，可以使用Transact-SQL语句删除视图，其语法格式如下：

DROP VIEW [ database_name . [ schema_name ] . | schema_name . ] view_name [ ; ]

其中：

l database_name，视图所在的数据库的名称。

l schema_name，视图所属于的模式名。

l view_name，需要删除的视图名。

l n，是表示可以指定多个视图的占位符。

例如，下面的脚本用于删除视图v_mrBaseInf。

USE [EAMS]

DROP VIEW [dbo].[v_mrBaseInf]

4 存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

4.1 存储过程基础

在使用 SQL Server创建应用程序时，Transact-SQL 编程语言是应用程序和 SQL Server 数据库之间的主要编程接口。使用 Transact-SQL 程序时，可用两种方法存储和执行程序。可以在本地存储程序，并创建向 SQL Server 发送命令并处理结果的应用程序；也可以将程序在 SQL Server 中存储为存储过程，并创建执行存储过程并处理结果的应用程序。

存储过程是Transact-SQL 语句的预编译集合，这些语句在一个名称下存储并作为一个单元进行处理。SQL Server 提供存储过程以管理 SQL Server 和显示有关数据库和用户的信息。SQL Server 提供的存储过程称为系统存储过程。用户可以使用Transact-SQL语言自己创建存储过程，Transact-SQL语言是SQL Server提供专为设计数据库应用程序的语言，它是应用程序和SQL Server数据库间的主要编程接口。

SQL Server 中的存储过程与其它编程语言中的过程类似，原因是存储过程可以：

l 接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。

l 包含执行数据库操作（包括调用其它过程）的编程语句。

l 向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）。

存储过程具有如下优点，使得在应用程序开发过程中，开发人员经常选择使用存储过程支持应用开发：

l 更快的执行速度：存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度；

l 与事务的结合，提供更好的解决方案：当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query和Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用；

l 支持代码重用：存储过程可以重复使用,可减少数据库开发人员的工作量；

l 安全性高：可设定只有某此用户才具有对指定存储过程的使用权。

SQL Server提供了三种类型的存储过程：

l 用户自定义存储过程：用户在SQL Server中采用Transact-SQL语句所创建的存储过程。

l 系统存储过程：是SQL Server自带的存储过程。系统存储过程提供了许多管理SQL Server的功能，存储在master数据库中，以sp_为前缀。

l 扩展存储过程：扩展存储过程使用户得以使用象 C 这样的编程语言创建自己的外部例程。对用户来说，扩展存储过程与普通存储过程一样，执行方法也相同。可将参数传递给扩展存储过程，扩展存储过程可返回结果，也可返回状态。扩展存储过程可用于扩展 Microsoft SQL Server的功能，使得SQL Server 可以动态装载并执行的动态链接库 (DLL)，直接在 SQL Server 的地址空间运行，并使用 SQL Server 开放式数据服务 (ODS) API 编程。当用户编写好扩展存储过程后，固定服务器角色 sysadmin 的成员即可在 SQL Server 中注册该扩展存储过程，然后授予其他用户执行该过程的权限。注意扩展存储过程只能添加到 master 数据库中。

4.2 创建存储过程

同创建其他数据库对象一样，创建存储过程可以采用多种方式，我们这儿介绍两种最主要的方式：使用SQL Server Management Studio图形工具和执行Transact-SQL语句创建存储过程。在创建存储过程时，需要注意以下几个方面：

l 不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。

l 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。

l 只能在当前数据库中创建存储过程。

创建存储过程时，应指定：

l 所有输入参数和向调用过程或批处理返回的输出参数。

l 执行数据库操作（包括调用其它过程）的编程语句。

l 返回至调用过程或批处理以表明成功或失败（以及失败原因）的状态值。

下面介绍采用常用的两种创建存储过程的方法。

（1）使用Transact-SQL语句创建存储过程

SQL Server提供的创建存储过程的语句是CREATE PROCEDURE，具体语法格式如下：

CREATE PROC [ EDURE ] [schema_name.] procedure_name [ ; number ]

[ { @parameter [ type_schema_name ]data_type }

[ VARYING ] [ = default ] [ [ OUT [ PUT ]

] [ ,...n ]

[ WITH < procedure_option > [ ,...n ]

[ FOR REPLICATION ]

AS { < sql_statement > [ ...n ] | <method_specifier> }

< procedure_option > ::=

[ ENCRYPTION ]

[ RECOMPILE ]

[ EXECUTE_AS_Clause ]

< sql_statement > ::=

{ [ BEGIN ] statements [ END ] }

<method_specifier > ::=

EXTERNAL NAME assembly_name.class_name[.method_name]

其中：

l schema_name，表示存储过程所属于的用户ID，模式名要么是当前用户的名称，要么是角色名。

l procedure_name，新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。

l ; number，是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。

l @ parameter，过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。

l [ type_schema_name ] data_type，参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。

l VARYING，参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。

l Default，参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

l OUTPUT，表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。

l N，表示最多可以指定 2.100 个参数的占位符。

l RECOMPILE，RECOMPILE 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

l ENCRYPTION，ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。

l EXECUTE AS，定义执行存储过程的安全相关的上下文。

l FOR REPLICATION，指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

l AS，指定过程要执行的操作。

l sql_statement，过程中要包含的任意数目和类型的 Transact-SQL 语句。

l n，是表示此过程可以包含多条 Transact-SQL 语句的占位符。

l <method_specifier>定义CLR存储过程引用的.NET框架的方法和网络位置。

要求创建一个存储过程，完成查询功能，从企业短信平台数据库EAMS中查询日志信息，日志存储在表LogInfo中，包括登录ID、用户ID、登录时间、是否合法、退出时间、IP地址和缘由信息。将存储过程命名为GetLogInfo。

在SQL Server Management Studio中新建一个查询窗口，输入以下SQL语句，单击执行按钮同样可以创建该存储过程。

USE [EAMS]

IF EXISTS (SELECT * FROM sysobjets WHERE name = 'GetLogInfo'

and type = 'p' )

DROP PROCEDURE GetLogInfo

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE GetLogInfo

SELECT LogInfo.*

FROM LogInfo order by Logintime desc

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

（2）使用SQL Server Management Studio图形工具创建存储过程

使用SQL Server Management Studio图形工具创建存储过程的步骤如下：

1．打开SQL Server Management Studio，在“对象资源管理器”视图中，展开“数据库”节点，选择需要创建存储过程的数据库EAMS，展开某个数据库，展开“可编程性”节点，选择“存储过程”节点，单击鼠标右键，选择“新建存储过程”，如图35所示。

图 35 选择“新建存储过程”菜单项

2．打开“新建存储过程”对话框，定义存储过程的名称“GetLogInfo”，定义查询脚本，如下所示。

USE [EAMS]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[GetLogInfo]

SELECT LogInfo.*

FROM LogInfo order by Logintime desc

3．单击保存按钮，SQL Server数据库引擎将创建该存储过程。

4.3 执行存储过程

当需要执行存储过程时，请使用 Transact-SQL EXECUTE 语句。如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行该存储过程。

EXECUTE的语法格式如下：

Execute a stored procedure:

[ EXEC [ UTE ] ]

{

[ @return_status = ]

{ procedure_name [ ;number ] | @procedure_name_var }

[ [ @parameter = ] { value

| @variable [ OUTPUT ]

| [ DEFAULT ] }

]

[ ,...n ]

[ WITH RECOMPILE ]

[;]

Execute a character string:

EXEC [ UTE ] ( { @string_variable

| [ N ] 'tsql_string' }

[ + ...n ] )

[;]

Execute a pass-through command against a linked server

EXEC [ UTE ] ( { @string_variable

| [ N ] 'command_string' } [ + ...n ]

[ {, { value | @variable [OUTPUT] } } [...n] ]

)

[ AT linked_srvname ]

[;]

其中：

l @return_status，是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于 EXECUTE 语句前，必须在批处理、存储过程或函数中声明过。用于唤醒调用标量值用户定义函数时，@return_status 变量可以是任何标量数据类型。

l procedure_name，是拟调用的存储过程的完全合法或者不完全合法的名称。过程名称必须符合标识符规则。用户可以执行在另一数据库中创建的过程，只要该用户拥有此过程或有在该数据库中执行它的适当的权限。用户可以在另一台运行 Microsoft® SQL Server™ 的服务器上执行过程，只要该用户有适当的权限使用该服务器（远程访问），并能在数据库中执行该过程。如果指定了服务器名称但没有指定数据库名称，SQL Server 会在用户默认的数据库中寻找该过程。

l number，是可选的整数，用于将相同名称的过程进行组合，使得它们可以用一句 DROP PROCEDURE 语句除去。该参数不能用于扩展存储过程。在同一应用程序中使用的过程一般都以该方式组合。例如，在订购应用程序中使用的过程可以 orderproc;1、orderproc;2 等来命名。DROP PROCEDURE orderproc 语句将除去整个组。在对过程分组后，不能除去组中的单个过程。例如，DROP PROCEDURE orderproc;2 是不允许的。

l @ procedure_name_var，是局部定义变量名，代表存储过程名称。

l @parameter，是过程参数，在 CREATE PROCEDURE 语句中定义。参数名称前必须加上符号 (@)。在以 @parameter_name = value 格式使用时，参数名称和常量不一定按照 CREATE PROCEDURE 语句中定义的顺序出现。但是，如果有一个参数使用 @parameter_name = value 格式，则其它所有参数都必须使用这种格式。默认情况下，参数可为空。如果传递 NULL 参数值，且该参数用于 CREATE 或 ALTER TABLE 语句中不允许为 NULL 的列（例如，插入至不允许为 NULL 的列），SQL Server 就会报错。为避免将 NULL 参数值传递给不允许为 NULL 的列，可以在过程中添加程序设计逻辑或采用默认值（使用 CREATE 或 ALTER TABLE 语句中的 DEFAULT 关键字）。

l value，是过程中参数的值。如果参数名称没有指定，参数值必须以 CREATE PROCEDURE 语句中定义的顺序给出。如果参数值是一个对象名称、字符串或通过数据库名称或所有者名称进行限制，则整个名称必须用单引号括起来。如果参数值是一个关键字，则该关键字必须用双引号括起来。如果在 CREATE PROCEDURE 语句中定义了默认值，用户执行该过程时可以不必指定参数。如果该过程使用了带 LIKE 关键字的参数名称，则默认值必须是常量，并且可以包含 %、_、[ ] 及 [^] 通配符。默认值也可以为 NULL。通常，过程定义会指定当参数值为 NULL 时应该执行的操作。

l @variable，是用来保存参数或者返回参数的变量。

l OUTPUT，指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字 OUTPUT 创建。使用游标变量作参数时使用该关键字。如果使用 OUTPUT 参数，目的是在调用批处理或过程的其它语句中使用其返回值，则参数值必须作为变量传递（即 @parameter = @variable）。如果一个参数在 CREATE PROCEDURE 语句中不是定义为 OUTPUT 参数，则对该参数指定 OUTPUT 的过程不能执行。不能使用 OUTPUT 将常量传递给存储过程；返回参数需要变量名称。在执行过程之前，必须声明变量的数据类型并赋值。返回参数可以是 text 或 image 数据类型以外的任意数据类型。

l DEFAULT，根据过程的定义，提供参数的默认值。当过程需要的参数值没有事先定义好的默认值，或缺少参数，或指定了 DEFAULT 关键字，就会出错。

l N，是占位符，表示在它前面的项目可以多次重复执行。例如，EXECUTE 语句可以指定一个或者多个 @parameter、value 或 @variable。

l AT linked_server_name，定义存储过程在linked_server_name定义的服务器上执行，然后将结果返回给客户。

l WITH RECOMPILE，强制编译新的计划。如果所提供的参数为非典型参数或者数据有很大的改变，使用该选项。在以后的程序执行中使用更改过的计划。该选项不能用于扩展存储过程。建议尽量少使用该选项，因为它消耗较多系统资源。

l @ string_variable，是局部变量的名称。@string_variable 可以是 char、varchar、nchar 或 nvarchar 数据类型，最大值为服务器的可用内存。如果字符串长度超过 4,000 个字符，则把多个局部变量串联起来用于 EXECUTE 字符串。

l [N] 'tsql_string'，是一个常量，tsql_string 可以是 nvarchar 或 varchar 数据类型。如果包含 N，则该字符串将解释为 nvarchar 数据类型，最大值为服务器的可用内存。如果字符串长度超过 4,000 个字符，则把多个局部变量串联起来用于 EXECUTE 字符串。

l [N] 'command_string'，包含传递给链接服务器命令的字符串。如果定义了N，字符串解释为nvarchar数据类型，其最大值为服务器上可用内存的数量。如果过程名称的前三个字符为 sp_，SQL Server 会在 Master 数据库中寻找该过程。如果没能找到合法的过程名称，SQL Server 会寻找所有者名称为 dbo 的过程。若要将存储过程名称解析为与系统存储过程同名的用户定义存储过程，请提供一个完全合法的过程名称。参数可以通过利用 value 或 @parameter_name = value 来提供。参数不是事务的一个部分；因而如果事务中的参数值更改，且该事务在以后回滚，该参数值不会退回到以前的值。返回给调用方的值总是过程返回时的值。如果存储过程编写为可以接受参数值，那么可以提供参数值。需要注意的是：如果使用 @parameter = value 的形式，那么可以按任何顺序提供参数。还可以省略那些已提供默认值的参数。如果以 @parameter = value 形式提供了一个参数，就必须按此种形式提供后面所有的参数。如果没有以 @parameter = value 形式提供参数，则必须按照 CREATE PROCEDURE 语句中给出的顺序提供参数。执行存储过程时，服务器将拒绝所有未包含在过程创建期间的参数列表中的参数。如果参数名称不匹配，用地址传递（显式传递该参数名称）的任何参数都不会被接受。虽然可以省略已提供默认值的参数，但只能截断参数列表。例如，如果一个存储过程有五个参数，可以省略第四个和第五个参数，但不能跳过第四个参数而仍然包含第五个参数，除非以 @parameter = value 形式提供参数。如果在存储过程中定义了参数的默认值，那么下列情况下将使用默认值：执行存储过程时未指定该参数的值；将 DEFAULT 关键字指定为该参数的值

若要执行与其它同名存储过程处于同一分组中的存储过程，请指定此存储过程在组内的标识号。例如，要执行 my_proc 组中的第二个存储过程，请执行：

EXECUTE my_proc;

当创建了存储过程之后，可以使用EXEC语句执行该存储过程，具体语法如下：

USE [EAMS]

EXEC GetLogInfo

将得到如图36所示的结果：

图 36 执行结果

4.4 查看存储过程

SQL Server提供了几个系统存储过程，可以用于获取存储过程的相关信息，使用这些存储过程，可以：

l 查看用于创建存储过程的 Transact-SQL 语句。这对于没有用于创建存储过程的 Transact-SQL 脚本文件的用户是很有用的。

l 获得有关存储过程的信息（如存储过程的所有者、创建时间及其参数）。

l 列出指定存储过程所使用的对象及使用指定存储过程的过程。此信息可用来识别那些受数据库中某个对象的更改或删除影响的过程。

当需要查看存储过程定义的时候，可以查询master系统数据库的sys.sql_modules视图。当需要查看存储过程相关信息时，可以查询master系统数据库的sys.procedures视图。当需要查看存储过程依赖关系时，可以查询master系统数据库的sys.sql_dependencies视图。当需要查看扩展存储过程定义的相关信息时，可以执行Transact-SQL语句sp_helpextendedproc，其语法规范如下：

sp_helpextendedproc [ [@funcname = ] 'procedure' ]

其中：

l [ @funcname =] 'procedure'，表示对象的名称，将显示该对象的定义信息。对象必须在当前数据库中。

l name，的数据类型为 nvarchar(776)，没有默认值。

4.5 修改存储过程

如果用户需要修改存储过程中的语句或者参数，要么删除存储过程，然后重新创建，要么在单个步骤中修改存储过程。当删除和重新创建存储过程时，同存储过程相关的所有授权信息都被丢失。当修改存储过程时，过程或者参数定义被修改，但是禁用存储过程的授权被保留下来。

用户还可以对存储过程进行重命名，新的名称必须遵循命名规则。用户只能重命名其所有的存储过程，但是数据库所有者可以修改任何用户存储过程的名称。

修改存储过程的Transact-SQL语句如下：

ALTER PROC [ EDURE ] [schema_name.] procedure_name [ ; number ]

[ { @parameterdata_type }

[ VARYING ] [ =default ] [ [ OUT [ PUT ]

] [ ,...n ]

[ WITH <procedure_option> [ ,...n ] ]

[ FOR REPLICATION ]

{ sql_statement [ ...n ] | <CLR_reference> }

<procedure_option> ::=

[ ENCRYPTION ]

[ RECOMPILE ]

[ EXECUTE_AS_Clause ]

<CLR_reference>::=

EXTERNAL NAME [ assembly_schema. ]

assembly_name.class_name[.method_name]

其中：

l schema_name，表示拥有存储过程的用户ID。

l procedure_name，是要更改的过程的名称。过程名称必须符合标识符规则。

l ; number，是现有的可选整数，该整数用来对具有同一名称的过程进行分组，以便可以用一条 DROP PROCEDURE 语句全部除去它们。

l @ parameter，过程中的参数。

l data_type，是参数的数据类型。

l VARYING，指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

l Default，参数的默认值。

l OUTPUT，表明参数是返回参数。

l N，是表示最多可指定 2.100 个参数的占位符。

l RECOMPILE，表明 Microsoft SQL Server不会高速缓存该过程的计划，该过程将在运行时重新编译。

l ENCRYPTION，表示 SQL Server 加密 syscomments 表中包含 ALTER PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。

l EXECUTE AS，定义执行存储过程的安全相关的上下文。

l FOR REPLICATION，指定不能在订阅服务器上执行为复制创建的存储过程。使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

l AS，过程将要执行的操作。

l sql_statement，过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

l n，是表示该过程中可以包含多条 Transact-SQL 语句的占位符。

l <CLR_reference>，定义CLR存储过程引用的.NET框架的方法和网络位置。

例如，将存储过程GetLogInfo重命名为sp_GetLogInfo，其Transact-SQL语句如下：

USE EAMS

EXEC sp_rename 'GetLogInfo','sp_GetLogInfo'

4.6 删除存储过程

当某些存储过程无法支持应用时，可能需要将其删除。删除存储过程可以在SQL Server Management Studio中采用图形工具删除，也可以执行Transact-SQL语句删除。在后续我们将给出删除存储过程的实例，在此，提供删除存储过程的Transact-SQL语句的语法规范。

DROP PROCEDURE { [ schema_name. ] procedure } [ ,...n ]

其中：

l schema_name，表示拥有存储过程的用户ID；

l procedure，是要删除的存储过程或存储过程组的名称。过程名称必须符合标识符规则；

l n，是表示可以指定多个过程的占位符。

为了查看存储过程名列表，可以使用sys.objects视图。为了显示存储过程定义，可以使用sys.sql_modules视图、当删除存储过程之后，存储过程的信息也会从sysobjects和syscomments系统表中删除。

当需要删除某个存储过程的时候，直接在SQL Server Management Studio中，通过图形工具删除，下面以删除EAMS数据库存储过程GetLogInfo为例，其步骤如下：

1．打开SQL Server Management Studio，在“对象资源管理器”视图中，展开“数据库”节点，选择需要删除存储过程的数据库EAMS，展开数据库，展开“可编程性”节点，选择“存储过程”节点，选择存储过程“GetLogInfo”，单击鼠标右键，选择“删除”菜单项，如图37所示。

图 37 选择“Delete”菜单项

2．打开“删除对象”对话框，如图38所示，当确定删除时，单击“确定”按钮即可。

图 38 “删除对象”对话框

同样，可以执行Transact-SQL语句完成存储过程的删除操作，针对这个示例，其语句如下。

USE [EAMS]

DROP PROCEDURE [dbo].[GetLogInfo]

USE [master]

5 游标

游标是数据库应用程序开发的一个基本技术，在许多企业应用开发中得到广泛应用，本节我们将结合企业信息平台介绍SQL Server游标的概念、类型，以及创建和使用游标的相关过程和语法规范。

5.1 游标基础

游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回数据表中。

由 SELECT 语句返回的行集包括所有满足该语句 WHERE 子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。

游标通过以下方式扩展结果处理：

l 允许定位在结果集的特定行。

l 从结果集的当前位置检索一行或多行。

l 支持对结果集中当前位置的行进行数据修改。

l 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。

l 提供脚本、存储过程和触发器中使用的访问结果集中的数据的 Transact-SQL 语句。

Microsoft SQL Server支持ODBC、ADO 和 DB-Library 定义的四种游标类型。这些游标检测结果集变化的能力和消耗资源（如在 tempdb 中所占的内存和空间）的情况各不相同。游标仅当尝试再次提取行时才会检测到行的更改。数据源没有办法通知游标当前提取行的更改。游标检测这些变化的能力也受事务隔离级别的影响。因此，在使用的时候，需要加以注意。

Microsoft SQL Server 支持三种游标实现：

l Transact-SQL 游标，基于 DECLARE CURSOR 语法，主要用在 Transact-SQL 脚本、存储过程和触发器中。Transac-SQL 游标在服务器上实现并由从客户端发送到服务器的 Transact-SQL 语句管理。它们还包含在批处理、存储过程或触发器中。

l 应用编程接口（API）服务器游标，支持 OLE DB、ODBC 和 DB-Library 中的 API 游标函数。API 服务器游标在服务器上实现。每次客户应用程序调用 API 游标函数时，SQL Server OLE DB 提供程序、ODBC 驱动程序或 DB-Library 动态链接库（DLL）就把请求传送到服务器，以便对 API 服务器游标进行操作。

l 客户端游标，由 SQL Server ODBC 驱动程序、DB-Library DLL 和实现 ADO API 的 DLL 在内部实现。客户端游标通过在客户端高速缓存所有结果集行来实现。每次客户应用程序调用 API 游标函数时，SQL Server ODBC 驱动程序、DB-Library DLL 或 ADO DLL 就对高速缓存在客户端中的结果集行执行游标操作。

由于 Transact-SQL 游标和 API 服务器游标都在服务器端实现，它们一起被称为服务器游标。不要混合使用这些不同类型的游标。如果执行一个来自应用程序的 DECLARE CURSOR 和 OPEN 语句，首先需要把 API 游标的特性设置为默认值。如果将 API 游标的特性设置非默认值的其它值，然后又执行 DECLARE CURSOR 和 OPEN 语句，您事实上是在要求 SQL Server 在 Transact-SQL 游标上映射 API 游标。例如，不要将 ODBC 特性设置为调用将键集驱动游标映射为结果集，然后又使用语句句柄执行 DECLARE CURSOR 和 OPEN 以调用 INSENSITIVE 游标。

5.2 创建游标

用户在创建和使用游标时，需遵循游标的生命周期。游标的生命周期包含五个阶段：

1．声明游标

为游标指定获取数据时所使用的select语句。声明游标并不会检索任何数据；它只是为游标指定了相应的select语句。并且，在declare后面指定游标的名字的时候，不需要使用@号：

DECLARE CursorName CURSOR CursorOptions

FOR Select Statement

2．打开游标

检索数据并填充游标：

OPEN CursorName

3．fetch操作

fetch操作会使游标移动到下一条记录，并将游标返回的每个列的数据分别赋值给本地变量，这些本地变量必须预先予以声明。

FETCH CursorName INTO @Variable1, @Variable2

也可以使用fetch命令移动到结果中一个绝对位置，或者，从当前位置向前或者向后移动n行。但是，我建议最好不要使用游标完成如此之多的工作。

通常在批处理中，会使用while循环来反复从游标中获取记录行，直到游标不再返回任何行为止。对于这样一个针对游标的循环，应当在其循环条件中检查@@Fetch_Status全局变量以确定是否还能够从游标中获取行：

WHILE @@Fetch_Status = 0

4．关闭游标

关闭游标，释放数据，但保留select语句。游标关闭以后，还可以使用open命令再次打开它。（Close命令是与open命令相对的）。

Close CursorName

5．释放游标

释放相关的内存，并删除游标的定义。（Deallocate命令是与declare命令相对的）。

DEALLOCATE CursorName

通常，用户使用DECLARE语句声明一个游标，声明一个游标主要包括以下内容：

l 游标名字

l 数据来源（表和列）

l 选取条件

l 属性（仅读或可修改）

在SQL Server中，DECLARE CURSOR语句同时支持SQL-92和Transact-SQL两种标准。

（1）SQL-92语法

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

（2）Transact-SQL 扩展语法

DECLARE cursor_name CURSOR

[ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR select_statement

[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

其中SQL-92语法的参数如下：

l cursor_name，是所定义的 Transact-SQL 服务器游标名称。cursor_name 必须遵从标识符规则。

l INSENSITIVE，定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用 SQL-92 语法时，如果省略 INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。

l SCROLL，指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果在 SQL-92 DECLARE CURSOR 中未指定 SCROLL，则 NEXT 是唯一支持的提取选项。如果指定 SCROLL，则不能也指定 FAST_FORWARD。

l select_statement，是定义游标结果集的标准 SELECT 语句。在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。如果 select_statement 中的子句与所请求的游标类型的功能发生冲突，则 Microsoft SQL Server 隐性地将游标转换为另一种类型。

l READ ONLY，防止游标被更新。在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。该选项替代要更新的游标的默认功能。

l UPDATE [OF column_name [,...n]]，定义游标内可更新的列。如果指定 OF column_name [,...n] 参数，则只允许修改所列出的列。如果在 UPDATE 中未指定列的列表，则可以更新所有列。

Transact-SQL 扩展参数如下：

l cursor_name，是所定义的 Transact-SQL 服务器游标名称。cursor_name 必须遵从标识符规则。

l LOCAL，指定该游标的作用域对在其中创建它的批处理、存储过程或触发器是局部的。该游标名称仅在这个作用域内有效。在批处理、存储过程、触发器或存储过程 OUTPUT 参数中，该游标可由局部游标变量引用。OUTPUT 参数用于将局部游标传递回调用批处理、存储过程或触发器，它们可在存储过程终止后给游标变量指派参数使其引用游标。除非 OUTPUT 参数将游标传递回来，否则游标将在批处理、存储过程或触发器终止时隐性释放。如果 OUTPUT 参数将游标传递回来，游标在最后引用它的变量释放或离开作用域时释放。

l GLOBAL，指定该游标的作用域对连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在脱接时隐性释放。

l FORWARD_ONLY，指定游标只能从第一行滚动到最后一行。FETCH NEXT 是唯一受支持的提取选项。如果在指定 FORWARD_ONLY 时不指定 STATIC、KEYSET 和 DYNAMIC 关键字，则游标作为 DYNAMIC 游标进行操作。如果 FORWARD_ONLY 和 SCROLL 均未指定，除非指定 STATIC、KEYSET 或 DYNAMIC 关键字，否则默认为 FORWARD_ONLY。STATIC、KEYSET 和 DYNAMIC 游标默认为 SCROLL。与 ODBC 和 ADO这类数据库 API 不同，STATIC、KEYSET 和 DYNAMIC Transact-SQL 游标支持 FORWARD_ONLY。FAST_FORWARD 和 FORWARD_ONLY 是互斥的；如果指定一个，则不能指定另一个。

l STATIC，定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。

l KEYSET，指定当游标打开时，游标中行的成员资格和顺序已经固定。对行进行唯一标识的键集内置在 tempdb 内一个称为 keyset 的表中。对基表中的非键值所做的更改（由游标所有者更改或由其它用户提交）在用户滚动游标时是可视的。其他用户进行的插入是不可视的（不能通过 Transact-SQL 服务器游标进行插入）。如果某行已删除，则对该行的提取操作将返回 @@FETCH_STATUS 值 -2。从游标外更新键值类似于删除旧行后接着插入新行的操作。含有新值的行不可视，对含有旧值的行的提取操作将返回 @@FETCH_STATUS 值 -2。如果通过指定 WHERE CURRENT OF 子句用游标完成更新，则新值可视。

l DYNAMIC，定义一个游标，以反映在滚动游标时对结果集内的行所做的所有数据更改。行的数据值、顺序和成员在每次提取时都会更改。动态游标不支持 ABSOLUTE 提取选项。

l FAST_FORWARD，指定启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。如果指定 FAST_FORWARD，则不能也指定 SCROLL 或 FOR_UPDATE。FAST_FORWARD 和 FORWARD_ONLY 是互斥的；如果指定一个，则不能指定另一个。

l READ_ONLY，禁止通过该游标进行更新。在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。该选项替代要更新的游标的默认功能。

l SCROLL_LOCKS，指定确保通过游标完成的定位更新或定位删除可以成功。当将行读入游标以确保它们可用于以后的修改时，Microsoft SQL Server会锁定这些行。如果还指定了 FAST_FORWARD，则不能指定 SCROLL_LOCKS。

l OPTIMISTIC，指定如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时 SQL Server 不锁定行。相反，SQL Server 使用 timestamp 列值的比较，或者如果表没有 timestamp 列则使用校验值，以确定将行读入游标后是否已修改该行。如果已修改该行，尝试进行的定位更新或定位删除将失败。如果还指定了 FAST_FORWARD，则不能指定 OPTIMISTIC。

l TYPE_WARNING，指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息。

l select_statement，是定义游标结果集的标准 SELECT 语句。在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。

l UPDATE [OF column_name [,...n]]，定义游标内可更新的列。如果提供了 OF column_name [,...n]，则只允许修改列出的列。如果在 UPDATE 中未指定列的列表，除非指定了 READ_ONLY 并发选项，否则所有列均可更新。

下面我们给出一个具体完整的例子，显示游标的使用方法，读者需要注意DECLARE、OPEN、FETCH、CLOSE和DEALLOCATE语句的使用，分别表示游标使用的整个过程。

USE [EAMS]

/*声明并打开一个全局游标，在批处理以外该游标依然可见*/

declare emp_cur cursor global scroll for

SELECT * FROM mrBaseInfo

Open emp_cur

/*用游标变量引用已声明的游标*/

declare @cur_tal cursor

set @cur_tal = emp_cur

/*现在释放对游标的引用*/

deallocate @cur_tal

/*游标emp_cur 依旧存在*/

fetch next from emp_cur

/*再引用游标*/

declare @cur_ta2 cursor

set !cur_ta2 = emp_cur

/*释放emp_cur游标*/

deallocate emp_cur

/*由于游标被@cur_ta2引用，所以依旧存在*/

fetch next from @cur_ta2

/*当最有一个游标变量超出游标作用域时，游标将被释放*/

declare @cur_ta cursor

set @cur_ta = cursor local scroll for

select * from mrBaseInf

/*由于没有其他变量对其进行引用，所以游标被释放*/

deallocate @cur_ta

5.3 使用游标

下面我们介绍如何使用所创建的游标，这是用户使用游标的基础，其步骤包括：

l 打开所创建的游标

l 获取数据

l 关闭游标

l 释放游标

（1）打开游标

使用OPEN语句打开 Transact-SQL 服务器游标，然后通过执行在 DECLARE CURSOR 或 SET cursor_variable 语句中指定的 Transact-SQL 语句填充游标。

其语法如下：

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

其中：

l GLOBAL，指定 cursor_name 指的是全局游标。

l cursor_name，已声明的游标的名称。如果全局游标和局部游标都使用 cursor_name 作为其名称，那么如果指定了 GLOBAL，cursor_name 指的是全局游标，否则 cursor_name 指的是局部游标。

l cursor_variable_name，游标变量的名称，该名称引用一个游标。

（2）获取数据

使用FETCH从 Transact-SQL 服务器游标中检索特定的一行。其语法规范如下：

FETCH

[ [ NEXT | PRIOR | FIRST | LAST

| ABSOLUTE { n | @nvar }

| RELATIVE { n | @nvar }

]

FROM

]

{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }

[ INTO @variable_name [ ,...n ] ]

其中：

l NEXT，返回紧跟当前行之后的结果行，并且当前行递增为结果行。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认的游标提取选项。

l PRIOR，返回紧临当前行前面的结果行，并且当前行递减为结果行。如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。

l FIRST，返回游标中的第一行并将其作为当前行。

l LAST，返回游标中的最后一行并将其作为当前行。

l ABSOLUTE {n | @nvar}，如果 n 或 @nvar 为正数，返回从游标头开始的第 n 行并将返回的行变成新的当前行。如果 n 或 @nvar 为负数，返回游标尾之前的第 n 行并将返回的行变成新的当前行。如果 n 或 @nvar 为 0，则没有行返回。n 必须为整型常量且 @nvar 必须为 smallint、tinyint 或 int。

l RELATIVE {n | @nvar}，如果 n 或 @nvar 为正数，返回当前行之后的第 n 行并将返回的行变成新的当前行。如果 n 或 @nvar 为负数，返回当前行之前的第 n 行并将返回的行变成新的当前行。如果 n 或 @nvar 为 0，返回当前行。如果对游标的第一次提取操作时将 FETCH RELATIVE 的 n 或 @nvar 指定为负数或 0，则没有行返回。n 必须为整型常量且 @nvar 必须为 smallint、tinyint 或 int。

l GLOBAL，指定 cursor_name 指的是全局游标。

l cursor_name，要从中进行提取的开放游标的名称。如果同时有以 cursor_name 作为名称的全局和局部游标存在，若指定为 GLOBAL 则 cursor_name 对应于全局游标，未指定 GLOBAL 则对应于局部游标。

l @cursor_variable_name，游标变量名，引用要进行提取操作的打开的游标。

l INTO @variable_name[,...n]，允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。

（3）关闭游标

使用CLOSE语句释放当前结果集并且解除定位游标的行上的游标锁定，关闭一个开放的游标。CLOSE 使得数据结构可以重新打开，但不允许提取和定位更新，直到游标重新打开为止。CLOSE 必须在一个开放游标上颁发，不允许在一个仅仅声明的游标或一个已经关闭的游标上颁发。

其语法如下：

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

其中：

l GLOBAL，指定 cursor_name 指的是全局游标。

l cursor_name，开放游标的名称。如果全局游标和局部游标都使用 cursor_name 作为它们的名称，那么当指定 GLOBAL 时 cursor_name 引用全局游标；否则，cursor_name 引用局部游标。

l cursor_variable_name，与开放游标关联的游标变量的名称。

（4）释放游标

使用DEALLOCATE语句删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由 Microsoft SQL Server释放。

DEALLOCATE的语法规范如下：

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

其中：

l cursor_name，是已声明游标的名称。当全局和局部游标都以 cursor_name 作为它们的名称存在时，如果指定 GLOBAL，则 cursor_name 引用全局游标，如果未指定 GLOBAL，则 cursor_name 引用局部游标。

l @cursor_variable_name，是 cursor 变量的名称。@cursor_variable_name 必须为 cursor 类型。

本示例通过游标显示企业信息平台数据库EAMS中人员基本信息mrBaseInf中存储的人员姓名列表。该示例显示了从声明游标、打开游标提取数据、关闭游标和释放游标的全生命过程。

USE [EAMS]

/*定义变量*/

DECLARE @name varchar(50)

/*打印表头*/

PRINT '--------------------- 姓名信息----------------------------------'

PRINT ' '

PRINT ' 姓名 '

PRINT '--------------------------------------------------------------------'

/*声明游标*/

DECLARE name_cursor CURSOR FOR

SELECT EmpName

FROM mrBaseInf

ORDER BY EmpName

/*打开游标*/

OPEN name_cursor

/*通过游标获取数据*/

FETCH NEXT FROM name_cursor

INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @name = CONVERT(CHAR(50), @name)

PRINT @name

END

FETCH NEXT FROM name_cursor

INTO @name

END

/*关闭游标*/

CLOSE name_cursor

/*释放游标*/

DEALLOCATE name_cursor

执行该存储过程，可以得到如图39所示的结果。

图 39 执行结果

6 触发器

触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。触发器不能被直接执行，它们只能为表上的insert、update、delete事件所触发。触发器可以帮助数据库开发人员和管理人员自动维护数据库，增强数据的完整性。本节我们首先介绍触发器的相关知识，然后结合具体的实例分析触发器的创建和删除操作。

1 触发器基础

触发器是一种特殊的存储过程，它在插入、删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。使用触发器具有许多优点，可以帮助开发人员实现一些自动特性。本节介绍触发器的一些基础知识及相关操作。

触发器主要是通过事件进行触发而被执行的。当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：

l 强化约束（Enforce restriction），触发器能够实现比CHECK 语句更为复杂的约束。

l 跟踪变化Auditing changes，触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。

l 级联运行（Cascaded operation），触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作（如删除，更新，插入）而该操作又导致该表上触发器被触发。

l 存储过程的调用（Stored procedure invocation），为了响应数据库更新触，发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS（数据库管理系统）本身之外进行操作。

由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理。此外一个表的同一类型（INSERT、 UPDATE、 DELETE）的多个触发器能够对同一种数据操作采取多种不同的处理。

总体而言，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。

2 创建触发器概述

在设计DML触发器时，需要注意以下几个方面的问题：

l CREATE TRIGGER 语句必须是批处理中的第一个语句。将该批处理中随后的其它所有语句解释为 CREATE TRIGGER 语句定义的一部分。

l 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。

l 触发器为数据库对象，其名称必须遵循标识符的命名规则。

l 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。

l 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。

l 在含有用 DELETE 或 UPDATE 操作定义的外键的表中，不能定义 INSTEAD OF 和 INSTEAD OF UPDATE 触发器。

l 虽然 TRUNCATE TABLE 语句类似于没有 WHERE 子句（用于删除行）的 DELETE 语句，但它并不会引发 DELETE 触发器，因为 TRUNCATE TABLE 语句没有记录。

l WRITETEXT 语句不会引发 INSERT 或 UPDATE 触发器。

创建触发器时需指定：

l 触发器名称

l 在其上定义触发器的表

l 触发器激发时机

l 激活触发器的数据修改语句。有效选项为 INSERT、UPDATE 或 DELETE。多个数据修改语句可激活同一个触发器。例如，触发器可由 INSERT 或 UPDATE 语句激活。

l 执行触发操作的编程语句

对于DML触发器和DDL触发器，SQL Server创建触发器的语法有一些差异，其中创建DML触发器的语法如下：

在INSERT、UPDATE 或 DELETE 语句上创建触发器的语法

CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }

[ WITH <dml_trigger_option> [ …,n ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS { sql_statement [ ...n ] | EXTERNAL NAME <method specifier> }

[ ; ]

<dml_trigger_option> ::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

<method_specifier> ::=

assembly_name.class_name[.method_name]

在CREATE、 ALTER、DROP、 GRANT、 DENY或者REVOKE 语句上创建DDL触发器的语法如下：

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH <ddl_trigger_option> [ …,n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

AS { sql_statement [ ...n ] | EXTERNAL NAME < method specifier > }

[ ; ]

<ddl_trigger_option> ::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

<method_specifier> ::=

assembly_name.class_name[.method_name]

其中：

l schema_name，DML触发器所属于的模式名，对于DDL触发器，不能定义该参数。

l trigger_name，是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。

l table| view，是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。

l DATABASE，应用DDL触发器的范围为当前数据库。如果定义了该参数，在当前数据库中，只要任何event_type或者event_group发生，将激活触发器。

l ALL SERVER，应用DDL触发器的范围为当前服务器。如果定义了该参数，在当前服务器中，只要任何event_type或者event_group发生，将激活触发器。

l WITH ENCRYPTION，加密 syscomments 表中包含 CREATE TRIGGER 语句文本的条目。使用 WITH ENCRYPTION 可防止将触发器作为 SQL Server 复制的一部分发布。

l EXECUTE AS，定义触发器执行的安全上下文。

l AFTER ，指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定 FOR 关键字，则 AFTER 是默认设置。不能在视图上定义 AFTER 触发器。

l INSTEAD OF，指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。

l { [DELETE] [ ,] [INSERT] [ ,] [UPDATE] }，是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。

l event_type，是Transact-SQL语句事件的名称，当事件触发之后，将激活DDL触发器。表4显示了数据库范围内的DDL语句。

表 4 数据库范围内的DDL语句

CREATE_TABLE	ALTER_TABLE	DROP_TABLE
CREATE_VIEW	ALTER_VIEW	DROP_VIEW
CREATE_SYNONYM	DROP_SYNONYM	CREATE_FUNCTION
ALTER_FUNCTION	DROP_FUNCTION	CREATE_PROCEDURE
ALTER_PROCEDURE	DROP_PROCEDURE	CREATE_TRIGGER
ALTER_TRIGGER	DROP_TRIGGER	CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION	CREATE_INDEX	ALTER_INDEX
DROP_INDEX	CREATE_STATISTICS	UPDATE_STATISTICS
DROP_STATISTICS	CREATE_ASSEMBLY	ALTER_ASSEMBLY
DROP_ASSEMBLY	CREATE_TYPE	DROP_TYPE
CREATE_USER	ALTER_USER	DROP_USER
CREATE_ROLE	ALTER_ROLE	DROP_ROLE
CREATE_APPLICATION_ROLE	ALTER_APPLICATION_ROLE	DROP_APPLICATION_ROLE
CREATE_SCHEMA	ALTER_SCHEMA	DROP_SCHEMA
CREATE_MESSAGE_TYPE	ALTER_MESSAGE_TYPE	DROP_MESSAGE_TYPE
CREATE_CONTRACT	ALTER_CONTRACT	DROP_CONTRACT
CREATE_QUEUE	ALTER_QUEUE	DROP_QUEUE
CREATE_SERVICE	ALTER_SERVICE	DROP_SERVICE
CREATE_ROUTE	ALTER_ROUTE	DROP_ROUTE
CREATE_REMOTE_SERVICE_BINDING	ALTER_REMOTE_SERVICE_BINDING	DROP_REMOTE_SERVICE_BINDING
GRANT_DATABASE	DENY_DATABASE	REVOKE_DATABASE
		CREATE_XML_SCHEMA_COLLECTION
ALTER_XML_SCHEMA_COLLECTION	DROP_XML_SCHEMA_COLLECTION	CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION	DROP_PARTITION_FUNCTION	CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME	DROP_PARTITION_SCHEME

表5显示了服务器范围内的DDL语句。

表5 服务器范围内的DDL语句

CREATE_LOGIN	ALTER_LOGIN	DROP_LOGIN
CREATE_ENDPOINT	DROP_ENDPOINT	GRANT_SERVER
DENY_SERVER	REVOKE_SERVER	CREATE_CERTIFICATE
ALTER_CERTIFICATE	DROP_CERTIFICATE

l event_group，是预先定义的Transact-SQL语句事件的组名。DDL在组中事件任何一个触发时激活。

l WITH APPEND，指定应该添加现有类型的其它触发器。只有当兼容级别是 65 或更低时，才需要使用该可选子句。如果兼容级别是 70 或更高，则不必使用 WITH APPEND 子句添加现有类型的其它触发器（这是兼容级别设置为 70 或更高的 CREATE TRIGGER 的默认行为）。WITH APPEND 不能与 INSTEAD OF 触发器一起使用，或者，如果显式声明 AFTER 触发器，也不能使用该子句。只有当出于向后兼容而指定 FOR 时（没有 INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本将不支持 WITH APPEND 和 FOR（将被解释为 AFTER）。

l NOT FOR REPLICATION，表示当复制进程更改触发器所涉及的表时，不应执行该触发器。

l AS，是触发器要执行的操作。

l sql_statement，是触发器的条件和操作。触发器条件指定其它准则，以确定 DELETE、INSERT 或 UPDATE 语句是否导致执行触发器操作。当尝试 DELETE、INSERT 或 UPDATE 操作时，Transact-SQL语句中指定的触发器操作将生效。

l n，表示触发器中可以包含多条 Transact-SQL 语句的占位符。对于 IF UPDATE (column) 语句，可以通过重复 UPDATE (column) 子句包含多列。

l < method_specifier >，对于CLR触发器，该参数定义同触发器绑定的方法。方法不带参数，无返回值。

创建DDL触发器和创建DML触发器的语法中都包含了EXECUTE AS (Transact-SQL)字句，这是因为SQL Server引入了定义用户自定义模块的执行上下文：函数、过程、队列和触发器。通过定义模块执行的上下文，用户可以控制模块执行的特定权限，从而为对象权限管理提供了更多的灵活性。对于不同情况，语法存在一定差异。

对于函数、存储过程和DML触发器，其语法如下：

EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }

对于具有数据库范围的DDL触发器，其语法如下：

EXECUTE AS { CALLER | SELF | 'user_name' }

对于具有服务器范围的DDL触发器，其语法如下：

EXECUTE AS { CALLER | SELF | 'login_name' }

对于队列，其语法如下：

EXECUTE AS { SELF | OWNER | 'user_name' }

3 创建DML触发器

DML触发器在数据库服务器数据操纵语言事件发生时执行的操作。DML事件包括对表或者视图执行UPDATE、INSERT、DELETE语句。DML触发器主要用于在数据修改时增强业务规则，扩展SQL Server约束、默认值和规则的完整性检查。

SQL Server提供的DML触发器包括以下几种类型：

l AFTER触发器，AFTER触发器在触发操作（INSERT、UPDATE 或 DELETE）后和处理完任何约束后激发。可通过指定 AFTER 或 FOR 关键字来请求 AFTER 触发器。因为 FOR 关键字与 AFTER 的效果相同，所以具有 FOR 关键字的触发器也归类为 AFTER 触发器。

l INSTEAD OF触发器，INSTEAD OF触发器代替触发动作进行激发，并在处理约束之前激发。

l .NET触发器，.NET触发器要么是AFTER触发器，要么是INSTEAD OF触发器。除了执行Transact-SQL存储过程，触发器可以执行更多.NET框架下执行的更多方法。

约束和DML触发器具有自己的特性。DML触发器的主要好处在于能够包含复杂的处理逻辑。因此，DML触发器支持约束的所有功能。但是，使用触发器有时并非最佳方法。当约束无法满足应用功能需求时，使用DML触发器可以带来很多好处。

Microsoft SQL Server在设计DML触发器时提供了两种选项：

l 执行 INSTEAD OF 触发器代替通常的触发动作。INSTEAD OF 触发器还可在带有一个或多个基表的视图上定义，而在这些视图上这些触发器可扩展视图可支持的更新类型。

l 在执行了 INSERT、UPDATE 或 DELETE 语句操作之后执行 AFTER 触发器。指定 AFTER 与指定 FOR 相同，而后者是 SQL Server 早期版本中唯一可使用的选项。AFTER 触发器只能在表上指定。

表6比较 AFTER 触发器和 INSTEAD OF 触发器的功能。

表6 AFTER 触发器和 INSTEAD OF 触发器功能比较

功能	AFTER 触发器	INSTEAD OF 触发器
适用范围	表	表和视图
每个表或视图含触发器数量	每个触发动作（UPDATE、DELETE 和 INSERT）含多个触发器	每个触发动作（UPDATE、DELETE 和 INSERT）含一个触发器
级联引用	不应用任何限制	在作为级联引用完整性约束目标的表上限制应用。
执行	晚于： l 约束处理 l 声明引用操作 l inserted 和 deleted 表的创建 l 触发动作	早于： l 约束处理代替： l 触发动作晚于： l inserted 和 deleted 表的创建
执行顺序	可指定第一个和最后一个执行	不可用
在 inserted 和 deleted 表中引用 text、ntext 和 image 列	不允许	允许

如果发生约束侵犯，则永远不会执行 AFTER 触发器，因此这些触发器不能用于任何可能防止约束侵犯的处理。

执行 INSTEAD OF 触发器而不是通常的触发动作。在创建将更改反映到基表的 inserted 和 deleted 表之后，但在执行任何操作之前执行这些触发器。这些触发器在执行任何约束前执行，因此可执行预处理以补充约束操作。

如果某个表上定义的 INSTEAD OF 触发器对该表执行一个通常将再次激发 INSTEAD OF 触发器的语句，则将不递归调用该触发器。相反，将视该表为没有 INSTEAD OF 触发器来处理该语句，并启动约束操作与 AFTER 触发器执行之间的链。例如，如果将触发器定义为一个表的 INSTEAD OF INSERT 触发器，并且该触发器在同一表上执行 INSERT 语句，则由 INSTEAD OF 触发器执行的 INSERT 语句不会再次调用该触发器。该触发器执行的 INSERT 将启动这样的进程，执行约束操作并激发为表定义的任何 AFTER INSERT 触发器。

如果视图上定义的 INSTEAD OF 触发器对该视图执行一个通常将再次激发 INSTEAD OF 触发器的语句，则将不递归调用该触发器。而是将该语句解析为对视图的基础基表的更改。在这种情况下，视图定义必须满足对可更新视图的所有限制。例如，如果将触发器定义为某个视图的 INSTEAD OF UPDATE 触发器，并且该触发器执行引用该视图的 UPDATE 语句，则由 INSTEAD OF 触发器执行的 UPDATE 语句不会再次调用该触发器。在处理该触发器对视图执行的 UPDATE 语句时，将视图看作没有 INSTEAD OF 触发器一样对待。UPDATE 更改的列必须解析到单个基表。对基础基表所做的每个修改将启动应用约束和激发为表定义的 AFTER 触发器之间的链。

触发器的性能开销通常很低。运行触发器的时间大部分都用于引用其它表，这些表可以在内存或数据库设备上。Deleted 和 inserted 表总是在内存中。触发器所引用的其它表的位置确定操作所需的时间。

INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。包含多个基表的视图必须使用 INSTEAD OF 触发器来支持引用表中数据的插入、更新和删除操作。INSTEAD OF 触发器的另一个优点是使您得以编写这样的逻辑代码：可以拒绝批处理中的某些部分同时允许批处理的其它部分成功。

4 创建DDL触发器

DDL触发器，同普通触发器一样，能够在触发器事件激活时，激活存储过程。但是同DML触发器不同，DDL触发器不是在UPDATE、INSERT、DELETE语句上事件作出响应，而是在数据定义语言上定义的触发器，数据定义语言一般以CREATE、ALTER和DROP语句开头。因此，DDL触发器主要用于执行管理任务，如执行审计和规范数据库操作。

在下面这些时候，可以考虑使用DDL触发器：

l 希望防止对数据库模式进行特定的修改

l 当在数据库模式中执行修改操作时，希望在数据库中执行特定操作

l 希望记录在数据库模式中执行的修改或者事件

DDL触发器只有在执行DDL语句之后才触发，不能是INSTEAD OF触发器。下面的示例显示了一个DDL触发器如何防止在数据库表上执行修改和删除操作。

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

PRINT 'You must disable Trigger "safety" to drop or alter tables!'

ROLLBACK ;

当设计DDL触发器时，首先必须：

l 理解DDL触发器的范围

l 确定哪些Transact-SQL语句或者语句组激活触发器

DDL触发器能够在当前数据库或者服务器上响应Transact-SQL事件。触发器的范围依赖于时间。例如，DDL触发器可以响应CREATE TABLE事件，只要在数据库上执行CREATE TABLE语句，触发器将被触发执行。

在下面的实例中，DDL触发器Safety在DROP TABLE或者ALTER TABLE事件发生的时候触发。

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

PRINT 'You must disable Trigger "safety" to drop or alter tables!'

ROLLBACK

;

DDL触发器可以响应一个或者多个Transact-SQL语句。注意并非所有的DDL事件都能用于DDL触发器中。有些事件只用于异步、非事务语句。例如，CREATE DATABASE事件不能用于DDL触发器。

当需要捕获触发DDL触发器的事件信息时，可以使用EVENTDATA函数。改函数返回XML类型数据，XML模式包含以下信息：

l 事件发生的时间

l 触发器执行过程中连接的系统进程ID

l 激活触发器的事件类型

而且，对于不同的事件类型，模式还包含其他信息，如事件发生的数据库、事件发生的对象和事件的Transact-SQL命令。

例如，考虑在数据库EAMS数据库创建以下DDL触发器：

CREATE TRIGGER safety

ON DATABASE

FOR CREATE_TABLE

PRINT 'CREATE TABLE Issued.'

PRINT CONVERT (nvarchar (1000), EVENTDATA() )

RAISERROR ('New tables cannot be created in this database.', 16, 1)

ROLLBACK

;

然后执行下面的CREATE TABLE语句：

CREATE TABLE NewTable (Column1 int);

则DDL触发器的PRINT EVENTDATA()语句将输入以下XML文档：

<EVENT_INSTANCE>

<EventType>CREATE_TABLE</EventType>

<Database>AdventureWorks</Database>

<Object>newtable</Object>

<ObjectType>TABLE</ObjectType>

<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"

ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

<CommandText>create table NewTable (Column1 int)</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

EVENTDATA函数可以用于创建事件日志。在下面的示例中，创建一个表存储事件信息。然后在当前数据库上创建触发器，当任何数据库级DDL事件发生时，下面这些信息将存储到表中。

l 事件触发的时间

l 数据库用户

l 事件类型

l 包括事件的Transact-SQL命令

USE [EAMS];

CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));

CREATE TRIGGER log

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

DECLARE @data XML

SET @data = EVENTDATA()

INSERT ddl_log

(PostTime, DB_User, Event, TSQL)

VALUES

(GETDATE(),

CONVERT(nvarchar(100), CURRENT_USER),

CONVERT(nvarchar(100), @data.query('data(//EventType)')),

CONVERT(nvarchar(2000), @data.query('data(//TSQLCommand)'))) ;

--测试触发器

CREATE TABLE TestTable (a int)

DROP TABLE TestTable ;

SELECT * FROM ddl_log ;

5 修改触发器

修改DML触发器的语法如下：

ALTER TRIGGER schema_name.trigger_name

ON ( table | view )

[ WITH <dml_trigger_option> [ …,n ] ]

( FOR | AFTER | INSTEAD OF )

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }

[ NOT FOR REPLICATION ]

AS { sql_statement [ ...n ] | EXTERNAL NAME <method specifier> }

[ ; ]

<dml_trigger_option> ::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

<method_specifier> ::=

[ assembly_schema. ] assembly_name.class_name[.method_name]

修改DDL触发器的语法如下：

ALTER TRIGGER trigger_name

ON { DATABASE | ALL SERVER }

[ WITH <ddl_trigger_option> [ …,n ] ]

{ FOR | AFTER } { event_type [ ,...n ] | event_group }

AS { sql_statement | EXTERNAL NAME <method specifier> }

} [ ; ]

<ddl_trigger_option> ::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

<method_specifier> ::=

[ assembly_schema. ] assembly_name.class_name[.method_name]

参数的具体说明请读者参考CREATE TRIGER的相关说明。

6 启用和禁用触发器

禁用触发器的语法如下：

DISABLE TRIGGER [ schema . ] trigger_name ON { object_name | DATABASE | SERVER } [ ; ]

其中：

l schema_name，触发器所在的模式名；

l trigger_name，需要禁用的触发器名；

l object_name，触发器所在的表、试图或者队列名；

l DATABASE，对于DDL触发器，表示触发器所在的数据库名；

l SERVER，对于DDL触发器，表示触发器所在的服务器名。

下面的实例禁用safety触发器。

USE [EAMS]

DISABLE TRIGGER safety ON DATABASE

启用触发器的语法如下：

ENABLE TRIGGER [ schema_name . ] trigger_name ON { object_name | DATABASE | SERVER }

其参数与DISABLE TRIGGER的对应参数说明相同。

下面的示例启用safety触发器。

USE [EAMS]

DISABLE TRIGGER safety ON DATABASE

ENABLE Trigger safety ON DATABASE

7 删除触发器

删除触发器的语法对于DML和DDL触发器是不同的，其中DML触发器的删除语法如下：

DROP TRIGGER schema_name.trigger_name [ ,...n ] [ ; ]

而DDL触发器的删除语法如下：

DROP TRIGGER trigger_name [ ,...n ]

ON { DATABASE | ALL SERVER }

[ ; ]

其中：

l schema_name，表示触发器所在的模式名。

l trigger_name，需要删除的触发器名。

l n，是表示可以指定多个触发器的占位符。

l DATABASE，表示DDL触发器的范围是当前数据库。

l ALL SERVER，表示DDL触发器的范围是当前服务器。

下面的示例删除触发器safety。

USE [EAMS]

DROP TRIGGER safety

posted on 2017-12-19 21:43 未来_我来阅读(22731) 评论(1) 编辑收藏举报