《第6章 索引与数据完整性》由会员分享，可在线阅读，更多相关《第6章 索引与数据完整性（83页珍藏版）》请在金锄头文库上搜索。
1、第六章 索引、存储过程与触发器,6.1 索引6.2 存储过程6.3 触发器,6.1 索引操作,6.1.1 创建索引 6.1.2 查看、修改和删除索引,一、什么叫索引,数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。,二、索引的作用,通过创建唯一索引，可以保证数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连
2、接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。,聚集索引与非聚集索引,聚集索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它的叶节点中存储的是实际的数据。 非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和行定位器。,6.1.1 创建索引,SQL Server 2000创建索引的方法（1）利用企业管理器
3、中的索引向导创建索引。（2）利用企业管理器直接创建索引。（3）利用Transact-SQL语句中的CREATE INDEX命令创建索引。（4）利用企业管理器中的索引优化向导创建索引。,1. 利用索引向导创建索引,如图6-1 -图6-6 所示。,图6-1 欢迎使用创建索引向导对话框,图6-2 选择数据库和对象对话框,图6-3 显示当前索引信息对话框,图6-4 选择创建索引的列对话框,图6-5 指定索引选项对话框,图6-6 指定索引名称对话框,2. 利用企业管理器直接创建索引,其具体步骤如下：（）在企业管理器中，展开指定的服务器和数据库，选择要创建索引的表，用右键单击该表，从弹出的快捷菜单中选择所
4、有任务项的管理索引选项（如图6-7所示），就会出现管理索引对话框，如图6-8所示。在该对话框中，可以选择要处理的数据库和表 。（）单击“新建”按钮，则出现新建索引对话框，如图6-9所示。 （）选择完成后单击“确定”按钮，即可生成新的索引；单击“取消”按钮，则取消新建索引的操作。,图6-7 选择管理索引选项对话框,图6-8 管理索引对话框,图6-9 新建索引对话框,3. 利用Transact-SQL语句中的CREATE INDEX命令创建索引,其语法形式如下：CREATE UNIQUE CLUSTERED| NONCLUSTERED INDEX index_name ON table | vie
5、w ( column ASC | DESC ,.n ) withPAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP_EXISTING ,STATISTICS_NORECOMPUTE,SORT_IN_TEMPDB ON filegroup ,CREATE INDEX命令创建索引各参数说明如下：,UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。 CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。index_name：用于指定所创建的索引的名称。 table：用
6、于指定创建索引的表的名称。 view：用于指定创建索引的视图的名称。 ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。 Column：用于指定被索引的列。PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。,FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。 DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引
7、。 STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 ON filegroup：用于指定存放索引的文件组。,例子6-1：,例子6-1：为表employees创建了一个唯一聚集索引，其程序清单如下：CREATE UNIQUE CLUSTERED INDEX number_ind ON employees (number)with pad_index, fillfactor=20, ignore_dup_key, drop_existing, statistics
8、_norecompute,例子6-2：,为表employees创建了一个复合索引，其程序清单如下：Create index employees_cpl_indon employees(name,age)with pad_index, fillfactor=50,4. 利用索引优化向导创建索引,索引优化向导可以完成以下几方面的工作： （1） 根据给定的工作负荷，通过使用查询优化器分析该工作负荷中的查询，为数据库推荐最佳索引组合。（2）分析所建议的更改将会产生的影响，包括索引的使用，查询在表之间的分布，以及查询在工作负荷中的性能。（3）推荐为执行一个小型的问题查询集而对数据库进行优化的方法。（4）
9、通过设定高级选项如磁盘空间约束、最大查询语句数和每个索引最多对应字段数等，允许定制推荐方式。,利用索引优化向导创建和优化索引的具体步骤如下：,如图6-10 -图6-18 所示。,图6-10 欢迎使用索引优化向导对话框,图6-11 选择服务器和数据库对话框,图6-12 连接服务器对话框,图6-13 选择服务器对话框,图6-14 修改缺省优化参数对话框,图6-15 选择要优化的表对话框,图6-16 索引建议对话框,图6-17 索引分析对话框,图6-18 完成索引优化向导对话框,6.1.2 查看、修改和删除索引,1. 利用企业管理器查看、修改和删除索引要查看和修改索引的详细信息，可以在企业管理器中，
10、展开指定的服务器和数据库项，用右键单击要查看的表，从弹出的快捷菜单中选择所有任务子菜单中的“管理索引”选项，则会出现管理索引对话框。选择要查看或者修改的索引，单击“编辑”按钮，就会出现修改索引对话框。在该对话框中，可以修改索引的大部分设置，还可以直接修改其SQL脚本，只需按下“编辑SQL”按钮，即可出现编辑SQL脚本对话框，其中可以编辑、测试和运行索引的SQL脚本。要删除索引，可以在企业管理器中，从管理索引对话框中或者表的属性对话框中，选择要删除的索引，单击“删除”按钮，即可删除索引。,2. 用系统存储过程查看和更改索引名称,系统存储过程sp_helpindex可以返回表的所有索引信息，其语法
11、形式如下：sp_helpindex objname=name其中objname=name参数用于指定当前数据库中的表的名称。另外，系统存储过程sp_rename可以用来更改索引的名称，其语法形式如下：sp_renameobjname=object_name,newname=new_name , objtype = object_type ,例子6-3：,更改employees表中的索引employees_name_index名称为employees_name_ind，其程序清单如下：Exec sp_rename employees.employees_name_index, employees
12、_name_ind, index,3. 使用Transact-SQL语句中的DROP INDEX命令删除索引,当不再需要某个索引时，可以将其删除，DROP INDEX命令可以删除一个或者多个当前数据库中的索引，其语法形式如下：DROP INDEX table.index | view.index ,.n 其中，table | view用于指定索引列所在的表或索引视图；index用于指定要删除的索引名称。注意，DROP INDEX命令不能删除由CREATE TABLE或者ALTER TABLE命令创建的主键或者唯一性约束索引，也不能删除系统表中的索引。,6.2 存储过程,6.2.1 创建存储过程
13、6.2.2 执行存储过程6.2.3 查看和修改存储过程6.2.4 重命名和删除存储过程,存储过程的概念,SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。,6.2.1 创建存储过程,在SQL Server中，可以使用三种方法创建存储过程 ：使用创建存储过程向导创建存储过程。利用SQL Server 企业管理器创建存储过程。使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。,创建存
14、储过程时，需要确定存储过程的三个组成部分：,所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其它存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。,1. 使用创建存储过程向导创建存储过程,图6-19 新建SQL Server组,在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导”（如图所示），则出现欢迎使用创建存储过程向导对话框，如图6-20所示。根据图6-20-图6-25提示可完成创建存储过程。,图6-20 欢迎使用创建存储过程向导对话框,图6-21 选择数据库对话框,图6-22 选择数据库对象对话框,图6-23 完成创建存储过程向导
15、对话框,图6-24 编辑存储过程属性对话框,图6-25 编辑存储过程SQL对话框,2. 使用SQL Server 企业管理器创建存储过程,（）在SQL Server企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项，如图6-26所示；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程”选项，如图6-27所示。均会出现创建存储过程对话框，如图6-28所示。（）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如
16、果要设置权限，单击“权限”按钮，如图6-29所示。,图6-26 选择新建存储过程对话框（1）,图6-27 选择新建存储过程对话框（2）,图6-28 新建存储过程对话框,图6-29 设置权限对话框,3. 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程,创建存储过程前，应该考虑下列几个事项： 不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。存储过程是数据库对象，其名称必须遵守标识符规则。只能在当前数据库中创建存储过程。一个存储过程的最大尺寸为128M。,
