《SQL Server应用教程 教学课件 ppt 作者 郑阿奇 刘启芬 顾韵华 第5章 索引与数据完整性》由会员分享，可在线阅读，更多相关《SQL Server应用教程 教学课件 ppt 作者 郑阿奇 刘启芬 顾韵华 第5章 索引与数据完整性（67页珍藏版）》请在金锄头文库上搜索。
1、第5章 索引与数据完整性,5.1 索引,5.2 默认值约束及默认值对象,5.3数据完整性,5.1 索引,索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。 在数据库系统中建立索引主要有以下作用： 快速存取数据； 保证数据记录的唯一性； 实现表与表之间的参照完整性； 在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。,5.1.1 索引的分类,1聚集索引 聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。SQL Server 2000是按B树组织聚集索引的，B树的叶节点存放数据页信息。 由于数
2、据记录按聚集索引键的次序存储，因此聚集索引对查找记录很有效。 一个表只能有一个聚集索引。 2非聚集索引 非聚集索引完全独立于数据行的结构。SQL Server 2000也是按B树组织非聚集索引的，与聚集索引不同之处在于：非聚集索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。 对于非聚集索引，表中的数据行不按非聚集键的次序存储。 在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键，只有在表上创建聚集索引时，表内
3、的行才按特定顺序存储。这些行按聚集索引键顺序存储。如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储。 一个表中最多只能有一个聚集索引，但可有一个或多个非聚集索引。当在 SQL Server 2000 上创建索引时，可指定是按升序还是降序存储键。,5.1.2 索引的创建, 对于XS表，按学号建立主键索引（PRIMARY KEY约束），索引组织方式为聚集索引； 对于XS表，按姓名建立唯一索引（UNIQUE约束），索引组织方式为非聚集索引； 对于KC表，按课程号建立主键索引或者唯一索引，索引组织方式为聚集索引； 对于XS_KC表，按学号+课程号建立唯一索引，索引组织方式为聚集索引。 在SQ
4、L Server 2000中，可利用企业管理器创建上述索引，也可以利用SQL命令，通过查询分析器建立索引。 下面以XS表中按学号建立索引为例，介绍索引的创建方法。 1. 通过企业管理器建立索引 第1步 进入企业管理器，打开XSCJ数据库； 第2步 选择选择XS表的图标，右击，出现如图5.1所示的快捷菜单，选择“设计表”菜单项，出现如图5.2所示XS的表设计器界面；,在XSCJ数据库中，经常要对XS、KC、XS_KC三个表查询和更新，为了提高查询和更新速度，可以考虑对三个表建立如下索引：,5.1.2 索引的创建,图5.1 XS表图标快捷菜单,5.1.2 索引的创建,图5.2 XS表设计器界面,5
5、.1.2 索引的创建,第3步 在XS表设计器界面上右击， 出现如图5.3所示的快捷菜单，快捷菜单中选择“索引/键”菜单项，出现如图5.4所示XS的属性界面；,图5.3 XS表设计器的快捷菜单,5.1.2 索引的创建,图5.4 表XS的属性界面,5.1.2 索引的创建,第4步 在“索引键”属性页选择“新建”命令，然后，确定新索引的名称（用系统缺省的名或重新取名）；在“列名”下选择要创建索引的列，在此选择“学号”这一列，最多可选择16 列。为获得最佳性能，最好只选择一列或两列。然后关闭，索引即创建好了。 进入XS表设计器的快捷菜单（在XS表设计器界面上右击），选择“索引键”菜单项，可以查看已建立的
6、索引。对于前面建立的索引，如图5.5所示。,此下拉表中列出了表XS创建的所有索引,图5.5 表XS的属性界面,5.1.2 索引的创建,2利用SQL命令建立索引 语法格式： CREATE UNIQUE /*是否为唯一索引*/ CLUSTERED | NONCLUSTERED /*索引的组织方式*/ INDEX index_name /*索引名称*/ ON table | view ( column ASC | DESC ,.n ) /*索引定义的依据*/ WITH ,.n /*索引选项*/ ON filegroup /*指定索引文件所在的文件组*/ 其中： := PAD_INDEX | FILL
7、FACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB ,5.1.2 索引的创建,说明: UNIQUE表示为表或视图创建唯一索引（即不允许存在索引值相同的两行）。例如：对于XS表，根据学号创建唯一索引，即不允许有两个相同的学号出现。此关键字的使用有两点需注意： (1) 对于视图创建的聚集索引必须是 UNIQUE 索引。 (2) 如果对已存在数据的表创建唯一索引，必须保证索引项对应的值无重复值。 CLUSTERED、 NONCLUSTERED用于指定创建聚集索引还
8、是非聚集索引，前者表 示创建聚集索引，后者表示创建非聚集索引。一个表或视图只允许有一个聚集索引，并 且必须先为表或视图创建唯一聚集索引，然后才能创建非聚集索引。 index_name为索引名，索引名在表或视图中必须唯一，但在数据库中不必唯一；参数table、view用于指定包含索引字段的表名或视图名，指定表名、视图名时，可包含数 据库和表所有者。注意，必须使用 SCHEMABINDING 定义视图才能在视图上创建索引。 column用于指定建立索引的字段，参数n表示可以为索引指定多个字段。指定索引字段时，要注意如下两点： (1) 表或视图索引字段的类型不能为：ntext、text 或 imag
9、e。 (2) 通过指定多个索引字段可创建组合索引，但组合索引的所有字段必须取自于同一表。,5.1.2 索引的创建, ASC表示索引文件按升序建立，DESC表示索引文件按降序建立，默认设置为 ASC。 PAD_INDEX用于指定索引中间级中每个页（节点）保持开放的空间，此关键字必须与 FILLFACTOR 子句同时用。FILLFACTOR子句通过参数 fillfactor指定在SQL Server创建索引的过程中，各索引页叶级的填满程度。 IGNORE_DUP_KEY用于确定对唯一聚集索引字段插入重复键值时的处理方式，如果为索引指定了IGNORE_DUP_KEY，插入重复值时，SQL Serve
10、r将发出警告消息并取消重复行的插入操作；如果没有为索引指定 IGNORE_DUP_KEY，SQL Server会发出一条警告消息，并回滚整个 INSERT 语句。 DROP_EXISTING指定删除除已存在的同名聚集索引或非聚集索引。 ON filegroup子句指定索引文件所在的文件组，filegroup 为文件组名。 【例5.1】为KC 表的课程名列创建索引。 /*使用简单索引*/ USE XSCJ IF EXISTS (SELECT name FROM sysindexes WHERE name = kc_name_ind ) DROP INDEX KC. kc_name_ind GO
11、CREATE INDEX kc_name_ind ON KC (课程名) GO,5.1.2 索引的创建,其中IF语句是判断是否存在名字为kc_name_ind的索引名，如果已经存在，需要先删除该索引，然后创建索引。为了表述简单，后面相关内容将省略IF判断语句。 【例5.2】根据KC表的课程号列创建唯一聚集索引，因为指定了 CLUSTERED 子句，所 以该索引将对磁盘上的数据进行物理排序。 /*使用唯一聚集索引*/ USE XSCJ CREATE UNIQUE CLUSTERED INDEX kc_id_ind ON KC (课程号) GO 注意：在表设计时，把课程号设为主键时，系统默认为表创
12、建了聚集索引。表只能创建一个聚集索引，此时执行此语句可能会出错，可以先去除课程号这个主键，然后执行语句。下面例子若有相同情况，可参考本例子。 【例5.3】根据XS_KC表的学号列和课程号列创建复合索引。 /*使用简单组合索引*/ USE XSCJ CREATE INDEX xs_kc_ind ON XS_KC (学号, 课程号) GO 【例5.4】根据XS表中的总学分列创建索引，例中使用了FILLFACTOR 子句。 USE XSCJ CREATE NONCLUSTERED INDEX score_ind ON XS (总学分) WITH FILLFACTOR = 60 GO,5.1.2 索引
13、的创建,【例5.5】根据XS表中学号列创建唯一聚集索引。如果输入了重复的键，将忽略该 INSERT或UPDATE语句。 /*使用 IGNORE_DUP_KEY */ USE XSCJ CREATE UNIQUE CLUSTERED INDEX xs_ind ON XS(学号) WITH IGNORE_DUP_KEY 创建索引有如下几点要说明： （1）在计算列上创建索引 对于UNIQUE 或 PRIMARY KEY 索引，只要满足索引条件，就可以包含计算列，但计算列必须具有确定性、必须精确，且不能包含 text、ntext 或 image 列。 （2）在视图上创建索引 可以在视图上定义索引。索引
14、视图是一种在数据库中存储视图结果集的方法，可减少动态生成结果集的开销。索引视图还能自动反映出创建索引后对基表数据所做的修改。,5.1.2 索引的创建,【例5.6】 创建一个视图，并为该视图创建索引。 USE XSCJ GO -定义视图，如下例子中，由于使用了WITH SCHEMABINDING子句，因此，定 -义视图时，SELECT子句中表名必须为：所有者名.表名 的形式。 CREATE VIEW View1 WITH SCHEMABINDING AS SELECT 姓名,成绩 FROM dbo.XS,dbo.XS_KC WHERE dbo.XS.学号=dbo. XS_KC.学号 GO -设置
15、选项 SET NUMERIC_ROUNDABORT OFF SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON -在视图上创建索引 CREATE UNIQUE CLUSTERED INDEX Inx1 ON View1(姓名) GO,5.1.2 索引的创建,（3）权限 CREATE INDEX 的权限默认授予给 sysadmin 固定服务器角色、db_ddladmin 和 db_owner 固定数据库角色和表所有者，且不能转让。 （4）SET选项设置
16、 使用索引视图或计算列上的索引时，必须对如下七个选项设置： 下列六个SET选项必须设置为 ON： ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER 必须将选项 NUMERIC_ROUNDABORT 设置为 OFF。 5.1.3 索引的删除 索引的删除既可通过企业管理器界面删除也可通过执行SQL命令删除。 1通过企业管理器界面删除索引 第1步 进入企业管理器，并与指定的数据库相连接； 第2步 为要删除索引的表打开表设计器，在表设计器中右击，然后从快捷菜单中选择“属性”命令； 第3步 选择“索引/键”选项卡，从“选定的索引”列表中选择要删除的索引，选择“删
