MSSQL之八实现视图与索引

数据库开发人员经常需要提高查询的性能。SQL Server 2008允许实现索引来减少查询的执行时间。而且，你可以通过实现视图对不同用户限制数据的浏览 本章讨论如何创建和管理索引和视图。它也讨论如何实现全文索引以提高数据索引。 重点 ? 创建和管理视图 ? 创建

数据库开发人员经常需要提高查询的性能。SQL Server 2008允许实现索引来减少查询的执行时间。而且，你可以通过实现视图对不同用户限制数据的浏览 本章讨论如何创建和管理索引和视图。它也讨论如何实现全文索引以提高数据索引。 重点 ? 创建和管理视图 ? 创建和管理索引 预习功课 ? 创建视图的格式以及注意事项 ? 创建索引的格式以及注意事项

视图

视图是一个虚表，是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。

使用视图的优点和作用主要有：

l 视图可以使用户只关心他感兴趣的某些特定数据和他们所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。

l 视图大大地简化了用户对数据的操作。

l 视图可以让不同的用户以不同的方式看到不同或者相同的数据集。

l 在某些情况下，由于表中数据量太大，因此在表的设计时常将表进行水平或者垂直分割，但表的结构的变化对应用程序产生不良的影响。而使用视图可以重新组织数据，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。

视图提供了一个简单而有效的安全机制。

? 如何创建视图：

SQL Server 2008提供了如下几种创建视图的方法：

l 用SQL SERVER管理平台创建视图；

l 用Transact-SQL语句中的CREATEVIEW命令创建视图；

l 利用SQL SERVER管理平台的视图模板来创建视图。

l 创建视图时应该注意以下情况：

l 只能在当前数据库中创建视图，在视图中最多只能引用1024列，视图中记 录的数目限制只由其基表中的记录数决定。

l 如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。

l 如果视图中某一列是函数、数学表达式、常量或者来自多个表的列且名字相同，则必须为列定义名称。

l 不能在视图上创建索引，不能在规则、触发器的定义中引用视图。

l 当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库 对象存在，每个数据库对象在语句的上下文中有效，而且数据修改语句不能违反数据完整性规则。

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

利用SQL SERVER管理平台创建视图

利用SQL SERVER管理平台创建视图的具体操作步骤如下：

在SQLSERVER管理平台中，展开指定的服务器，打开要创建视图的数据库文件夹，选择指定的数据库，右击该数据库图标，从弹出的快捷菜单中依次选择“新建（New）→视图”选项，如图7-1所示。接着就出现添加表、视图、函数对话框。如图7-2所示。

利用Transact-SQL语句中的CREATE VIEW命令创建视图

使用Transact-SQL语句中的CREATE VIEW创建视图，其语法形式如下：

CREATE VIEW [schema_name.] view_name [(column[,...n])]
[WITH [,...n]]
AS
select_statement
[WITH CHECK OPTION]
< view_attribute > ::=
{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}

利用Transact-SQL语句中的CREATE VIEW命令创建视图

【例8-1】选择表s和sc中的部分字段和记录来创建一个视图，并且限制表s中的记录只能是计算机系的记录集合，视图定义为view_s。

程序清单如下：

create view view_s

as

selects.name,s.age,s.sex,

sc.cno,sc.scorefrom s,sc

where s.sno=sc.snoand s.dept=’计算机’

【例8-2】创建一个视图，使之包含复杂的查询。

程序清单如下：

CREATE VIEWExampleView

WITH SCHEMABINDING

AS

SELECT sno,SUM(score) AS Sumscore, COUNT(*) AS CountCol FROM sc

GROUP BY sno

利用Transact-SQL语句中的CREATE VIEW命令创建视图

例【8-3】创建一个视图，使之包含字符串的运算。

程序清单如下：

CREATE VIEWv_shyjl(shyxh, shj, xm, jglb, phr, bzh) AS

Select

distinctshyxh,substring(shj,1,10)+""+zhi,

t01_shbshy.xm,t012_shyjl.jglb,t012_shyjl.phr,t012_shyjl.bzh

from t01_shbshy, t012_shyjl

wheret012_shyjl.xm=t01_shbshy.xm

其中shj列是从基表中取出两列并和一空字符串相加而得到的。这些都为基表中字段间的灵活运算提供了极大方便，从而使我们可以随心所欲地定制符合自己要求的数据。

利用模板创建视图

使用视图模板可以很容易地创建视图，其具体操作步骤如下；

（1）在SQLSERVER管理平台中，选择view菜单中的“模板资源管理器 ”选项，如图所示。

（2）在出现的“模板资源管理器”选项中选择“创建视图”选项，如图所示。

（3）按照提示输入视图名称，select语句后，执行此语句，即可创建视图。

? 修改、删除和重命名视图

1、修改视图

2、重命名视图

3、查看视图信息、删除视图

修改视图

修改视图的方法有以下两种方法：

（1）在SQLSERVER管理平台中，右击要修改的视图，从弹出的快捷菜单中选择“设计视图”选项，出现视图修改对话框。该对话框与创建视图时的对话框相同，可以按照创建视图的方法修改视图。

（2）使用ALTERVIEW语句修改视图，但首先必须拥有使用视图的权限，然后才能使用ALTER VIEW语句，该语句的语法形式如下：

ALTER VIEW view_name

[（column[,...n]）]

[WITH ENCRYPTION]

AS

select_statement
[WITH CHECK OPTION

例【8-4】修改了视图V_employees，在该视图中增加了新的字段employees.salary，并且定义一个新的字段名称e_salary。

程序清单如下：

alter view dbo.employees（number,name,age,e_salary）

as

select number,name,age,salary

from employees

where name=’张三’

? 重命名视图 重命名视图方法有以下两种：

(1). 在SQL SERVER管理平台中，选择要修改名称的视图，并右击该视图，从弹出的快捷菜单中选择“重命名”选项。或者在视图上再次单击，也可以修改视图的名称。接着该视图的名称变成可输入状态，可以直接输入新的视图名称。

(2). 使用系统存储过程sp_rename来修改视图的名称，该过程的语法形式如下：

sp_rename old_name,new_name

例【8-5】把视图v_all重命名为v_part。

程序清单如下：

sp_rename v_all,v_part

? 查看视图信息

可以使用系统存储过程sp_help显示视图特征，使用sp_helptext显示视图在系统表中的定义，使用sp_depends显示该视图所依赖的对象。使用SQL Server 查询分析器可以方便地显示视图属性信息，如图所示。图显示了使用sp_helptext存储过程显示视图的创建语句。

利用select语句或SQL SERVER管理平台可以查看视图的输出数据。在SQL SERVER管理平台中，右击某个视图的名称，从弹出的快捷菜单中选择“打开视图”选项，在SQL SERVER管理平台中就会显示该视图的输出数据，如图7-9所示。

? 删除视图

对于不再使用的视图，可以使用SQLSERVER管理平台或者Transact-SQL语句中的DROPVIEW命令删除它。

使用Transact-SQL语句DROP VIEW删除视图，其语法形式如下：

DROP VIEW {view_name} [,…n]

可以使用该命令同时删除多个视图，只需在要删除的各视图名称之间用逗号隔开即可。

例【8-6】　同时删除视图v_student和v_teacher。

程序清单如下：

drop view v_student,v_teacher

? 通过视图修改记录

使用视图修改数据时，需要注意以下几点：

l 修改视图中的数据时，不能同时修改两个或者多个基表，可以对基于两个或 多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。

l 不能修改那些通过计算得到的字段，例如包含计算值或者合计函数的字段。

l 如果在创建视图时指定了WITH CHECK OPTION选项，那么使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。

l 执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。

l 如果视图引用多个表时，无法用DELETE命令删除数据，若使用UPDATE命令则应与INSERT操作一样，被更新的列必须属于同一个表。

插入数据记录

例【8-7】　创建一个基于表employees的新视图v_employees。

程序清单如下：

create view v_employees（number, name, age, sex, salary）

as

select number, name, age, sex, salary

from employees

where name=’张三’

执行以下语句可向表employees中添加一条新的数据记录：

Insert into v_employees

Values（001,’李力’,22,’m’,2000）

例【8-8】首先创建一个包含限制条件的视图v_employee2，限制条件为工资>2000，然后插入了一条不满足限制条件的记录，再用SELECT语句检索视图和表。

程序清单如下：

create view v_employee2

as

select * from employee

where 工资>2000

go

insert into v_employee2

values（002,’王则’,30,’f’,1000）

go

select * from employee

go

select * from v_employee2

go

例【8-9】在例子8-8的基础上添加WITH CHECK OPTION选项。

程序清单如下：

create view v_employee3

as

select * from employee

where 工资>2000

with check option

go

insert into v_employee3

values（002,’王则’,30,’f’,1000）

go

select * from v_employee3

go

运行该程序将显示如下出错信息：

Server: Msg 550,Level 16, State 1, Line 1

The attemptedinsert or update failed because the target view either specifies WITH CHECKOPTION or spans a view that specifies WITH CHECK OPTION and one or more rowsresulting from the operation did not qualify under the CHECK OPTION constraint.

The statement hasbeen terminated.

更新和删除数据记录

使用视图可以更新数据记录，但应该注意的是，更新的只是数据库中的基表。使用视图删除记录，可以删除任何基表中的记录，直接利用DELETE语句删除记录即可。但应该注意，必须指定在视图中定义过的字段来删除记录。

例【8-10】创建了一个基于表employees的视图v_employees，然后通过该视图修改表employees中的记录。

程序清单如下：

create view v_employees

as

select * from employees

update v_employees

set name=’张然’

where name=’张三’

例【8-11】利用视图v_employees删除表employees中姓名为张然的记录。

程序清单如下：

delete from v_employees

where name=’张然’

索引

? 认识索引：

索引是一种与表或视图关联的物理结构，可以用来加快从表或视图中检索数据行的速度。

为什么要创建索引呢？这是因为创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用ORDER BY和GROUP BY子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

正是因为上述这些原因，所以应该对表增加索引

问题一：

也许会有人要问增加索引有如此多的优点，为什么不在表中的每一个列上建一个索引呢？

虽然索引有许多优点，但是为表中的每一个列都增加索引是非常不明智的做法。这是因为增加索引也有其不利的一面。

第一，创建索引和维护索引要耗费时间。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚集索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

问题二：

索引在什么位置上创建？

在经常需要搜索的列上创建索引；

在主键上创建索引；

在经常用于连接的列上创建索引，也就是在外键上创建索引；

在经常需要根据范围进行搜索的列上创建索引(因为索引已经排序，其指定的范围是连续的)；

在经常需要排序的列上创建索引(因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间)；

在经常用在WHERE子句中的列上创建索引。

? 索引的类型和特点：

在Microsoft SQL Server 2008系统中有两种基本的索引类型：聚集索引和非聚集索引。

除此之外，还有唯一性索引、索引视图、全文索引及XML索引等。在这些索引类型中，聚集索引和非聚集索引是数据库引擎中索引的基本类型，是理解唯一性索引、索引视图的基础，

本节主要研究这两种索引类型。

聚集索引

聚集索引是一种数据表的物理顺序与索引顺序相同的索引，非聚集索引则是一种数据表的物理顺序与索引顺序不相同的索引。

聚集索引的叶级和非叶级构成了一个特殊类型的B树结构。B树结构中的每一页称为一个索引节点。索引的最低级节点是叶级节点。

在一个聚集索引中，某个表的数据页是叶级，在叶级之上的索引页是非叶级。在聚集索引中，页的顺序是有序的。应该在表中经常搜索的列或按照顺序访问的列上创建聚集索引。其中，用于指定聚集索引第一页地址信息的root_page来自sys.system_internal_allocation_units系统视图中。聚集索引的结构示意图如图所示

非聚集索引：

非聚集索引与聚集索引具有相同的B树结构，但是在非聚集索引中，基础表的数据行不是按照非聚集键的顺序排序和存储，且非聚集索引的叶级是由索引页而不是由数据页组成。

非聚集索引既可以定义在表或视图的聚集索引上，也可以定义在表或视图的堆上。非聚集索引中的每一个索引行都是由非聚集键值和行定位符组成，该行定位符指向聚集索引或堆中包含该键值的数据行。如果表或视图中没有聚集索引(堆)，则行定位符是指向行的指针RID，而RID由文件标识符ID、页码和页上的行数生成。如果表或视图上有聚集索引，则行定位符则是行的聚集索引键。非聚集索引的结构示意图如图

其他类型的索引：

除了聚集索引和非聚集索引之外，Microsoft SQLServer 2008系统还提供了一些其他类型的索引或索引表现形式，这些内容包括唯一性索引、包含性列索引、索引视图、全文索引和XML索引。

在创建聚集索引或非聚集索引时，索引键可以都不相同，也可以包含重复值。如果希望索引键都各不相同，那么必须创建唯一性索引。当然，在创建聚集索引或非聚集索引时，都可以指定该索引具有唯一性的特点。这种唯一性与前面讲过的主键约束是关联的，某种程度上可以说，主键约束等于唯一性的聚集索引。

如果多个列的字节总数大于900字节且又希望将这些列都包含在索引中，那么可以使用包含性列索引。

如果希望提高视图的查询效率，可以将视图的索引物理化，也就是说将结果集永久存储在索引中。

全文索引是一种特殊类型的基于标记的索引，是通过Microsoft SQLServer的全文引擎服务创建、使用和维护，其目的是为用户提供在字符串数据中高效率地搜索复杂的词语。这种索引的结构 与数据库引擎使用的聚集索引或非聚集索引的B树 结构是不同的。

XML索引是与XML数据关联的索引形式，是XML二进制BLOB的已拆分持久表示形式。XML索引又可以分为主索引和辅助索引。

访问数据的方式：

访问数据库中数据时，可以采用两种方法即表扫描和索引查找。

第一种方法是表扫描，就是指系统将指针放在该表的表头数据所在的数据页上，然后按照数据页的排列顺序，逐页地从前向后扫描该表数据所占有的全部数据页，直至扫描完表中的全部记录。在扫描时，如果找到符合查询条件的记录，那么就将这条记录挑选出来。最后，将全部挑选出来符合查询 语句条件的记录显示出来。

第二种方法是使用索引查找。索引是一种树状结构，其中存储了关键字和指向包含关键字所在记录的数据页的指针。当使用索引查找时系统将沿着索引的树状结构，根据索引中关键字和指针找到符合查询条件的记录。最后将全部查找到的符合查询语句条件的记录显示出来。当系统沿着索引值查找时，使用搜索值与索引值进行比较判断。这种比较判断一直进行下去，直到满足下面两个条件为止：

——搜索值不大于或等于索引值。

——搜索值大于或等于索引页上的最后一个值。

? 创建索引：

在Microsoft SQL Server 2008系统中，既可以直接创建索引，也可以间接创建索引。当直接创建索引时，可以使用CREATE INDEX语句，也可以使用图形工具。

创建索引的方法有：

直接方法和间接方法：

直接创建索引的方法就是使用命令和工具直接创建索引。

间接创建索引就是通过创建其他对象而附加创建了索引，例如在表中定义主键约束或唯一性约束的同时也创建了索引。虽然，这两种方法都可以创建索引，但是它们创建索引的具体内容是有区别的。

使用CREATE INDEX语句或使用创建索引向导来创建索引，这是最基本的索引创建方式，并且这种方法最具有柔性，可以定制创建出符合自己需要的索引。

通过定义主键约束或唯一性约束，也可以间接创建索引。

CREATE INDEX语句

CREATE [ UNIQUE ][ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON table_or_view_name (column [ ASC | DESC ] [ ,...n ] )

[INCLUDE (column_name[, …n])]

[ WITH

( 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 = max_degree_of_parallelism)[, …n]]

ON {partition_schema_name(column_name) | filegroup_name | default}

【例8-12】使用CREATE INDEX语句创建唯一性的聚集索引

? 索引维护：

索引在创建之后，由于数据的增加、删除、更新等操作使得索引页发生碎块，为了提高系统的性能，必须对索引进行维护。

这些维护包括查看碎块信息、维护统计信息、分析索引性能及删除重建索引等

查看索引统计信息：

索引统计信息是查询优化器用来分析和评估查询、确定最优查询计划的基础数据。一般地，用户可以通过常用的方式访问指定索引的统计信息。一种方式是使用DBCCSHOW_STATISTICS命令，另一种是使用图形化工具。

DBCC SHOW_STATISTICS命令可以用来返回指定表或视图的特定对象的统计信息，这些特定对象可以是索引、列等。

【例8-17】查看索引统计信息

1、解释簇索引的工作过程

2、哪个系统函数被用来在表的索引上检测碎片

3、哪个选项被用于CREATE VIEW语句加密视图的文本

4、标准视图和索引视图的区别是什么？

5、为什么我们需要在试图上创建索引

小结

1、索引被创建能够增强查询的性能。

2、有两种类型的索引：簇索引和非簇索引。

3、使用create index语句创建索引。

4、簇索引在属性上建立，属性的值是唯一的，并且不经常改变。数据在物理上是在簇索引中排序的。

5、视图是虚拟表，它从一个或多个称为基表或内含表的表中产生数据。

6、视图作为安全机制，而保护基表中的数据。

7、当使用视图的时候，SQLServer仅允许在一个内含表中修改数据，甚至视图是由多个内含表产生的。