第9章 视图及索引

1、第第9 9章章 视图与索引视图与索引n提高数据存取的性能及操作速度提高数据存取的性能及操作速度n加快查询数据的效率加快查询数据的效率视图与索引视图与索引n概念概念n创建创建n管理管理9.1 9.1 视图的基础知识视图的基础知识 n虚拟表虚拟表n给查询语句指定一个名字给查询语句指定一个名字create view rtnTimeasselect UserTb.UserName,book.BookName,Lending.LendDate,Lending.RtnDate from Lending,book,UserTb,UserCatewhere usertb.UserId=Lending.User

2、Idand Lending.BookId=Book.BookIdand UserTb.CateName=UserCate.CateNameand DATEDIFF(DD,LendDate,GETDATE()UserCate.BorrTimeand RtnDate is nullselect * from rtnTime9.1.1 9.1.1 视图的概念视图的概念n视图是基于一个或多个数据表的动态数据集合，是视图是基于一个或多个数据表的动态数据集合，是一个逻辑上的虚拟数据表。一个逻辑上的虚拟数据表。动态：被引用时生成。动态：被引用时生成。9.1.2 9.1.2 视图的作用视图的作用视图的作用：视

3、图的作用：n简化用户操作简化用户操作n简化用户权限管理简化用户权限管理n安全保护功能安全保护功能n重新组织数据重新组织数据9.1.3 9.1.3 视图的类型视图的类型 n标准视图标准视图实现对数据库的查询、修改和删除等基本操作。实现对数据库的查询、修改和删除等基本操作。2. 2. 索引视图索引视图 索引视图适于聚合许多行的查询。但不太适合于经常更新的基本数据集索引视图适于聚合许多行的查询。但不太适合于经常更新的基本数据集3. 3. 分区视图分区视图 分区视图在一台或多台服务器间水平连接一组成员表中的分区数据。分区视图在一台或多台服务器间水平连接一组成员表中的分区数据。 9.2 9.2 创建视图

4、创建视图 n使用使用SQL Server Management StudioSQL Server Management Studio工具创建视图；工具创建视图；n使用使用Transact-SQLTransact-SQL语句中的语句中的CREAT VIEWCREAT VIEW修改视图。修改视图。9.2.2 9.2.2 使用使用Transact-SQLTransact-SQL语句创建视图语句创建视图语法：语法：CREATE VIEW CREATE VIEW schema_name schema_name view_nameview_name ( column ( column ，n ) n ) W

5、ITH , WITH ,n n ASAS select_statement select_statement WITH CHECK OPTION WITH CHECK OPTION 其中：其中：schema_nameschema_name：视图所属框架的名称。：视图所属框架的名称。view_nameview_name：视图的名称。视图名称必须符合标识符的命名规则。：视图的名称。视图名称必须符合标识符的命名规则。columncolumn：视图中的列使用的名称。：视图中的列使用的名称。如果未指定如果未指定columncolumn，则视图列将获得与，则视图列将获得与SELECTSELECT语句中的列

6、相同的名称语句中的列相同的名称。 select_statementselect_statement：定义视图的：定义视图的SELECTSELECT语句。该语句可以使用多个表和其语句。该语句可以使用多个表和其 他视图。需要相应的权限才能在已创建视图的他视图。需要相应的权限才能在已创建视图的SELECTSELECT子句引用的对象中选子句引用的对象中选 择。择。 WITH CHECK OPTIONWITH CHECK OPTION：强制针对视图执行的所有数据修改语句都必须符合：强制针对视图执行的所有数据修改语句都必须符合 在在select_statementselect_statement中设置的条

7、件。通过视图修改行时，中设置的条件。通过视图修改行时，WITH CHECK WITH CHECK OPTION OPTION可确保提交修改后，仍可通过视图看到数据。可确保提交修改后，仍可通过视图看到数据。 包括：包括：ENCRYPTIONENCRYPTION，对，对CREATE VIEWCREATE VIEW语句文本的项进行加密；语句文本的项进行加密；SCHEMABIONDINGSCHEMABIONDING，将视图绑定到基础表的架构；，将视图绑定到基础表的架构；VIEW_METADATAVIEW_METADATA，指定为引用视图的查询请求浏览模式的元数据时，指定为引用视图的查询请求浏览模式的元

8、数据时，SQL SQL ServerServer实例将向实例将向DB-LibraryDB-Library、ODBCODBC和和OLE DB APIOLE DB API返回有关视图的元数据返回有关视图的元数据信息。信息。这里需要注意的是：这里需要注意的是： CREATE VIEWCREATE VIEW必须是查询批处理中的第一句必须是查询批处理中的第一句。 视图定义中的视图定义中的SELECTSELECT子句不能包含下列内容：子句不能包含下列内容：1) 1) COMPUTECOMPUTE或或COMPUTE BYCOMPUTE BY子句。子句。2) 2) ORDER BYORDER BY子句子句，除

9、非在除非在SELECTSELECT语句的选择列表中也有一个语句的选择列表中也有一个TOPTOP子句。子句。3) 3) INTOINTO关键字。关键字。4) 4) OPTIONOPTION子句。子句。5) 5) 引用临时表或表变量。引用临时表或表变量。 【 例例 9 - 19 - 1 】 创 建 完 整 的 教 师 类 型 的 读 者 借 阅 信 息 视 图创 建 完 整 的 教 师 类 型 的 读 者 借 阅 信 息 视 图“teac_lending_view”，并禁止用户查看视图的定义语句。并禁止用户查看视图的定义语句。9.3 9.3 修改、删除和重命名视图修改、删除和重命名视图 9.3.1

10、 9.3.1 修改视图修改视图n使用使用SQL Server Management StudioSQL Server Management Studio工具修改视图定义工具修改视图定义 2. 2. 使用使用ALTER VIEWALTER VIEW语句修改视图定义语句修改视图定义语法格式如下：语法格式如下：ALTER VIEW ALTER VIEW schema_name schema_name view_nameview_name ( column , ( column ,n ) n ) WITH , WITH ,n n AS select_statementAS select_stateme

11、nt WITH CHECK OPTION WITH CHECK OPTION 【例例9-29-2】修改修改【例例9-19-1】所创建的所创建的“teac_lending_view”视图视图，使其显示计算机系教师读者的借阅情况，且不需要显示读者的性别、注册，使其显示计算机系教师读者的借阅情况，且不需要显示读者的性别、注册日期。日期。9.3.2 9.3.2 删除视图删除视图1. 1. 使用使用SQL Server Management StudioSQL Server Management Studio工具删除视图工具删除视图 2.2.使用使用Transact-SQLTransact-SQL语句的

12、语句的DROP VIEWDROP VIEW命令删除视图，其语法形式如下：命令删除视图，其语法形式如下： DROP VIEW schema_name view_name ,n DROP VIEW schema_name view_name ,n 【例例9-39-3】删除视图删除视图book_viewbook_view DROP VIEW book_view DROP VIEW book_view9.3.3 9.3.3 重命名视图重命名视图n使用使用Management StudioManagement Studion使用系统存储过程使用系统存储过程sp_renamesp_rename其语法形式如

13、下：其语法形式如下： sp_rename object_name, new_namesp_rename object_name, new_name其中：其中：object_nameobject_name：当前的视图名：当前的视图名new_namenew_name：指定对象的新名称。：指定对象的新名称。9.3.3 9.3.3 重命名视图重命名视图【例例9-49-4】将视图将视图“计算机系读者借阅情况计算机系读者借阅情况”重命名为重命名为“计算机系教师借阅计算机系教师借阅情况情况”实现代码如下：实现代码如下： EXEC sp_rename EXEC sp_rename 计算机系读者借阅情况计算机系

14、读者借阅情况,计算机系教师借阅情况计算机系教师借阅情况注：注：nsp_rename sp_rename 可以对表、视图重命名可以对表、视图重命名nsp_renamedb 可以对数据库重命名重命名nalter database oldName MODIFY name =newNamealter database oldName MODIFY name =newName9.4 使用视图操作数据表可以通过视图对数据表的数据进行可以通过视图对数据表的数据进行添加、修改和删除添加、修改和删除的操作。的操作。使用视图对数据表的记录进行操作时，所创建的视图必须满足如下的要求：使用视图对数据表的记录进行操作时

15、，所创建的视图必须满足如下的要求：1) 1) 每次只能修改一张基本表的数据每次只能修改一张基本表的数据。2) 2) 视图的字段中视图的字段中不能包含计算列不能包含计算列，计算列是不能更新的。，计算列是不能更新的。3) 3) 如果在创建视图时指定了如果在创建视图时指定了WITH CHECK OPTIONWITH CHECK OPTION选项，那么使用视图修改选项，那么使用视图修改 数据库时，数据库时，必须保证修改后的数据满足视图定义的要求必须保证修改后的数据满足视图定义的要求。4) 4) 如果在视图定义中使用了如果在视图定义中使用了GROUP BYGROUP BY、UNIONUNION、DIST

16、INCTDISTINCT或或TOPTOP子句，则视子句，则视 图图不允许更新不允许更新。5) 5) 如果在视图定义中有嵌套查询，并且内层查询的如果在视图定义中有嵌套查询，并且内层查询的FROMFROM子句中涉及的表也子句中涉及的表也 是导出该视图的基本表，则视图不允许更新。是导出该视图的基本表，则视图不允许更新。通过视图对数据表的数据进行通过视图对数据表的数据进行添加添加n1）用户有向数据表插入数据的权限；n2）视图只引用表中部分字段，插入数据时只能是明确其应用的字段取值；n3）未引用的字段应具备下列条件之一：允许空值；设有默认值；是标识字段；数据类型是timestamp或unique ide

17、ntifer;n4）视图不能包含多个字段的组合n5）视图不能包含使用统计函数的结果；n6）视图不能包含DISTINCT或GROUP BY子句；n7）定义视图使用WITH CHECK OPTION，则插入数据应符合相应条件；n8）若视图引用多个表，一条INSERT语句只能同一个基表表中数据；通过视图对数据表的数据进行通过视图对数据表的数据进行更新更新n使用视图可以更新基表数据记录注：使用INSERT时的限制同样适用通过视图对数据表的数据进行删除通过视图对数据表的数据进行删除n使用视图删除记录，可以删除任何基表中的记录。注意: n必须指定在视图中定义过的字段来删除记录n视图引用多个表时，无法用DE

18、LETE命令删除数据9.5 9.5 查看视图信息查看视图信息 执行系统存储过程来查看视图信息执行系统存储过程来查看视图信息 。1.1.用系统存储过程用系统存储过程sp_helpsp_help查看视图的基本信息，无论是否被加密查看视图的基本信息，无论是否被加密 2. 2. 用用sp_helptextsp_helptext查看视图的定义信息，没有被加密时可用。查看视图的定义信息，没有被加密时可用。【例例9-59-5】查看视图查看视图“book_view”book_view”的定义信息的定义信息【例例9-69-6】查看视图查看视图“teac_lending_view”的定义信息的定义信息 3.3.用

19、系统存储过程用系统存储过程sp_dependssp_depends查看视图与其他对象间的依赖关系查看视图与其他对象间的依赖关系【例例9-79-7】查看视图查看视图“teac_lending_view”的依赖关系的依赖关系9.6 9.6 索引概述索引概述 在日常生活中，我们经常会用到索引，如图书的目录、在日常生活中，我们经常会用到索引，如图书的目录、词典的索引等。词典的索引等。利用索引，我们可以很快地找到需要找的东利用索引，我们可以很快地找到需要找的东西。西。 在对数据库进行操作时，用到索引可以提高数据存取的在对数据库进行操作时，用到索引可以提高数据存取的性能及操作的速度，从而使用户能够较快地查

20、询并准确地得性能及操作的速度，从而使用户能够较快地查询并准确地得到希望的数据。到希望的数据。n索引的概念索引的概念n类型类型n创建和管理索引创建和管理索引9.6.1 9.6.1 什么是索引什么是索引索引是一个重要的数据库对象，它类似于图书中的目录。索引是一个重要的数据库对象，它类似于图书中的目录。索引使数据库不用对整个表进行扫描就能找到所需的数据。索引使数据库不用对整个表进行扫描就能找到所需的数据。(1) (1) 使用索引的优点使用索引的优点 1) 1) 创建唯一索引，可以保证表中的数据记录不重复。创建唯一索引，可以保证表中的数据记录不重复。2) 2) 加快数据检索速度。加快数据检索速度。3)

21、 3) 加速表与表之间的连接。加速表与表之间的连接。4) 4) 在使用在使用ORDER BYORDER BY和和GROUP BYGROUP BY子句中进行检索数据时，可以显著减少查询子句中进行检索数据时，可以显著减少查询 中分组和排序的时间。中分组和排序的时间。5) 5) 可以在检索数据的过程中使用优化隐藏器，从而提高系统的性能。可以在检索数据的过程中使用优化隐藏器，从而提高系统的性能。9.6 9.6 索引概述索引概述(2) (2) 创建索引的原则创建索引的原则1) 1) 主键列上一定要建立索引。主键列上一定要建立索引。2) 2) 在连接中频繁使用的列，比如外键。在连接中频繁使用的列，比如外键

22、。3) 3) 在频繁查询的列上最好建立索引。在频繁查询的列上最好建立索引。4) 4) 对于对于texttext、imageimage和和bitbit数据类型的列不要建立索引。数据类型的列不要建立索引。5) 5) 对于具有重复值较多的列不要建立索引。对于具有重复值较多的列不要建立索引。(3) (3) 索引并非越多越好索引并非越多越好 1) 1) 创建索引要花费时间并占用存储空间，聚集索引更严重。创建索引要花费时间并占用存储空间，聚集索引更严重。2) 2) 维护索引也要花费时间。维护索引也要花费时间。3) 3) 当对表进行修改时，需要维护索引，插入、更新和删除的数据越多，当对表进行修改时，需要维护

23、索引，插入、更新和删除的数据越多， 维护的开销就越大。维护的开销就越大。9.6.2 9.6.2 索引类型索引类型主要分为两类：主要分为两类：n聚集索引聚集索引n非聚集索引非聚集索引唯一索引唯一索引包含列索引包含列索引索引视图索引视图全文索引全文索引空间索引空间索引筛选索引筛选索引XMLXML索引索引9.6.2 9.6.2 索引类型索引类型1. 1. 聚集索引聚集索引指表中数据行的指表中数据行的物理存储顺序物理存储顺序与与索引顺序索引顺序完全相同。完全相同。每个表只能创建一个聚集索引。每个表只能创建一个聚集索引。 2. 2. 非聚集索引非聚集索引非聚集索引具有与表的数据完全分离的结构。非聚集索引

24、具有与表的数据完全分离的结构。表中的每一个列上都可以有自己的非聚集索引表中的每一个列上都可以有自己的非聚集索引创建的非聚集索引最多为创建的非聚集索引最多为249249个个9.7 9.7 创建索引创建索引n只有表的只有表的所有者所有者才能在表上创建索引才能在表上创建索引n创建创建唯一索引唯一索引时，应该保证创建索引的列不包含重复的数据，并且没有时，应该保证创建索引的列不包含重复的数据，并且没有两个或更多的空值。两个或更多的空值。 可以在建表的时候创建索引可以在建表的时候创建索引也可以对已存在的表创建索引。也可以对已存在的表创建索引。创建索引有两种方法：创建索引有两种方法：pSQL Server

25、Management StudioSQL Server Management StudiopTransact-SQLTransact-SQL使用使用CREATE INDEXCREATE INDEX语句创建索引的语法格式如下：语句创建索引的语法格式如下：CREATECREATE UNIQUE UNIQUE CLUSTERED CLUSTERED | | NONCLUSTERED NONCLUSTERED INDEX index_nameINDEX index_name ON ON table table | | view view ( ( column column ASC | DESC ,n

26、) ASC | DESC ,n ) WITH WITH( PAD_INDEX= ON | OFF ( PAD_INDEX= ON | OFF | FILLFACTOR=filefactor | FILLFACTOR=filefactor | IGNORE_DUP_KEY= ON | OFF | IGNORE_DUP_KEY= ON | OFF | DROP_EXISTING= ON | OFF | DROP_EXISTING= ON | OFF | STATISTICS_NORECOMPUTE= ON | OFF | STATISTICS_NORECOMPUTE= ON | OFF | SOR

27、T_IN_TEMPDB= ON | OFF )| SORT_IN_TEMPDB= ON | OFF ) ON filegroup ON filegroup 其中：其中：UNIQUEUNIQUE：为表或视图创建唯一索引，即不允许两行具有相同的索引键值。：为表或视图创建唯一索引，即不允许两行具有相同的索引键值。 省略省略UNIQUEUNIQUE时，创建的索引是非唯一索引。时，创建的索引是非唯一索引。CLUSTEREDCLUSTERED：指定创建的索引为聚集索引。：指定创建的索引为聚集索引。NOCLUSTEREDNOCLUSTERED：指定创建的索引为非聚集索引。省略：指定创建的索引为非聚集索引。省

28、略CLUSTERED |CLUSTERED | NOCLUSTERED NOCLUSTERED，则建立的是非聚集索引。，则建立的是非聚集索引。index_nameindex_name：指定创建的索引的名称。：指定创建的索引的名称。table | viewtable | view：用于创建索引的表或视图的名称。：用于创建索引的表或视图的名称。columncolumn：索引所基于的一列或多列。指定两个或多个列名，可为指定列的：索引所基于的一列或多列。指定两个或多个列名，可为指定列的 组合值创建组合索引。组合值创建组合索引。一个组合索引中最多可组合一个组合索引中最多可组合1616列列。组合索。组合索

29、 引键中的所有列必须在引键中的所有列必须在同一个表或视图同一个表或视图中。中。ASC | DESCASC | DESC：确定特定索引列的升序或降序排序方向。默认值为：确定特定索引列的升序或降序排序方向。默认值为ASCASC。PAD_INDEXPAD_INDEX：指定填充索引的内部节点的行数至少应大于等于两行。：指定填充索引的内部节点的行数至少应大于等于两行。 PAD_INDEXPAD_INDEX选项只有在选项只有在FILLFACTORFILLFACTOR选项指定后才起作用，因为选项指定后才起作用，因为 PAD_INDEXPAD_INDEX使用与使用与FILLFACTORFILLFACTOR相同

30、的百分比。相同的百分比。FILLFACTOR=filefactorFILLFACTOR=filefactor：指定一个百分比，表示在索引创建或重新生成过：指定一个百分比，表示在索引创建或重新生成过 程中数据库引擎应使每个索引页的叶级别达到的填充程度。程中数据库引擎应使每个索引页的叶级别达到的填充程度。 fillfactorfillfactor必须为介于必须为介于1 1至至100100之间的整数值。默认值为之间的整数值。默认值为0 0。 如果如果fillfactorfillfactor为为100100或或0 0，数据库引擎将创建叶级页达到其，数据库引擎将创建叶级页达到其 填充容量的索引。填充容量

31、的索引。IGNORE_DUP_KEYIGNORE_DUP_KEY：指定对唯一聚集索引或唯一非聚集索引执行多行插入操：指定对唯一聚集索引或唯一非聚集索引执行多行插入操 作时出现重复键值的错误响应。默认值为作时出现重复键值的错误响应。默认值为OFFOFF。当为。当为ONON时，发出一条时，发出一条 警告信息，但只有违反了唯一索引的行才会失败；为警告信息，但只有违反了唯一索引的行才会失败；为OFFOFF时，发出错时，发出错 误信息，并回滚整个误信息，并回滚整个INSERTINSERT事务。事务。IGNORE_DUP_KEYIGNORE_DUP_KEY设置仅适用于创设置仅适用于创 建或重新生成索引后发

32、生的插入操作。建或重新生成索引后发生的插入操作。 DROP_EXISTINGDROP_EXISTING：指定应删除并重新生成已命名的先前存在的聚集或非聚集：指定应删除并重新生成已命名的先前存在的聚集或非聚集索引。默认值为索引。默认值为OFFOFF。当为。当为ONON时，删除并重新生成现有索引。指定的索引名时，删除并重新生成现有索引。指定的索引名称必须与当前的现有索引相同；但可以修改索引定义；当为称必须与当前的现有索引相同；但可以修改索引定义；当为OFFOFF时，如果指时，如果指定的索引名已存在，则会显示一条错误。使用定的索引名已存在，则会显示一条错误。使用DROP_EXESTINGDROP_E

33、XESTING不能更改索引不能更改索引类型。类型。STATISTICS_NORECOMPUTESTATISTICS_NORECOMPUTE：指定是否重新计算分发统计信息。默认值为：指定是否重新计算分发统计信息。默认值为OFFOFF。当为。当为ONON时，不会自动重新计算过时的统计信息；为时，不会自动重新计算过时的统计信息；为OFFOFF时，启用统计信息时，启用统计信息自动更新功能。自动更新功能。SORT_IN_TEMPDBSORT_IN_TEMPDB：指定是否在：指定是否在tempdbtempdb中存储临时排序结果。默认值为中存储临时排序结果。默认值为OFFOFF。当为当为ONON时，在时，在

34、tempdbtempdb中存储用于生成索引的中间排序结果。为中存储用于生成索引的中间排序结果。为OFFOFF时，中间时，中间排序结果与索引存储在同一个数据库中。排序结果与索引存储在同一个数据库中。ON filegroupON filegroup：为指定文件组创建指定索引。如果未指定位置且表或视图：为指定文件组创建指定索引。如果未指定位置且表或视图尚未分区，则索引将与基本表或视图使用相同的文件组。该文件组必须已尚未分区，则索引将与基本表或视图使用相同的文件组。该文件组必须已存在。存在。【例例9-99-9】为表为表“Book”Book”基于基于“BookId”BookId”字段创建一个唯一的聚集索

35、引。字段创建一个唯一的聚集索引。FILLFACTOR经验值n为几乎没有数据修改活动的表使用为几乎没有数据修改活动的表使用100%填填充因子充因子;n低活动的使用低活动的使用80%90%;n中等活动的使用中等活动的使用60%70%n为索引键上的高活动使用为索引键上的高活动使用50%或更低的百或更低的百分比。分比。 9.8 9.8 管理索引管理索引 创建索引之后，由于数据的变更操作会引起索引页出现创建索引之后，由于数据的变更操作会引起索引页出现碎块碎块，为了提高系统的性能，必须对索引进行维护。，为了提高系统的性能，必须对索引进行维护。 9.8.1 9.8.1 修改索引修改索引 修改索引是指修改索引

36、是指禁用禁用、重新生成重新生成或或重新组织索引重新组织索引，或通过设置索引的，或通过设置索引的相关选项操作修改现有的索引。相关选项操作修改现有的索引。重新生成重新生成索引将会删除并重新创建索引，将根据指定的或现有的填充索引将会删除并重新创建索引，将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。行重新排序。重新组织索引重新组织索引是用最少系统资源重新组织索引。是用最少系统资源重新组织索引。禁用索引禁用索引可防止用户访问该索引，对于聚集索引，还可以防止用户访可防止用户访问该索引，对于聚集索引

37、，还可以防止用户访问基础表数据。问基础表数据。修改索引：修改索引：SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL2. 2. 使用使用ALTER INDEXALTER INDEX命令修改索引命令修改索引(1) (1) 重新生成索引重新生成索引重新生成索引的语法格式如下：重新生成索引的语法格式如下：ALTER INDEX index_name ON table_or_view_name ALTER INDEX index_name ON table_or_view_name REBUI

38、LDREBUILD其中：其中：(2) (2) 重新组织索引重新组织索引ALTER INDEX index_name ON table_or_view_name ALTER INDEX index_name ON table_or_view_name REORGANIZEREORGANIZE(3) (3) 禁用索引禁用索引ALTER INDEX index_name ON table_or_view_name ALTER INDEX index_name ON table_or_view_name DISABLEDISABLE9.8.2 9.8.2 删除索引删除索引n使用使用Management StudioManagement Studio工具删除索引工具删除索引2. 2. 使用使用DROP INDEXDROP INDEX命令删除索引命令删除索引DROP INDEX . ,n DROP INDEX . ,n