创建索引、视图、存储过程及触发器

6.1.2索引的分类
聚簇索引 数据表的物理顺序和索引表的顺序相同，它根据表中的
一列或多列的值排列记录。每一个表只能有一个聚簇 索引，因为一个表的记录只能以一种物理顺序存放， 在通常情况下，使用的都是聚簇索引。 聚簇索引有利于范围搜索，由于聚簇索引的顺序与数据 行存放的物理顺序相同，因此，聚簇索引最适合于范 围搜索，因为相邻的行将被物理地存放在相同或相邻 近的页面上。 创建聚簇索引的几个注意事项： 每张表只能有一个聚簇索引 由于聚簇索引改变表的物理顺序，所以应先建聚簇索
引，后创建非聚簇索引 创建索引所需的空间来自用户数据库，而不是
TEMPDB数据库 主键是聚簇索引的良好候选者
6.1.2索引的分类
索引页
根结点
数据页
6.1.2索引的分类
非聚簇索引
对于非聚簇索引，表的物理顺序与索引顺序不同，即表 的数据并不是按照索引列排序的。索引是有序的，而 表中的数据是无序的。一个表可以同时存在聚簇索引 和非聚簇索引，而且，一个表可以有多个非聚簇索引。 例如对记录网站活动的日志表可以建立一个对日期时 间的聚簇索引和多个对用户名的非聚簇索引。
在一个表的一个或多个列上创建索引时，应考虑以下几点： ① 当在一个表上创建PRIMARY KEY约束或UNIQUE约 束时，SQL Server自动创建唯一性索引。不能在已经创 建PRIMARY KEY约束或UNIQUE约束的列上创建索引。 定义PRIMARY KEY 约束或UNIQUE约束与创建标准索 引相比应是首选的方法。 ② 必须是表的拥有者才能创建索引。 ③ 在一个列上创建索引之前，确定该列是否已经存在 索引。
系部名称 社会科学部 经济管理系
建筑系 基础科学部 传播技系
农林系 机电工程系
计算机系
系主任 刘克忠 刘国峰 王未起
王彬 田建国 陈瑞修 王伟东 刘海军
6.1.3索引的操作
SQL Server 2005中，创建索引有两种方法： 1.使用T-SQL语句创建索引 使用SQL Server Management Studio创建索引
6.1.3索引的操作
3. 创建唯一索引 索引按照结构可分为聚簇索引和非聚簇索引两种不同的类型。按照实 现的功能分，有一类索引被称作“唯一索引”。它既可以采用聚簇索 引的结构，又可以采用非聚簇索引的结构。 唯一索引的特征：
① 不允许两行具有相同的索引值。 ② 可用于实施实体完整性。 ③ 在创建主键约束和唯一约束时自动创建唯一索引。 在已有数据的表上创建唯一索引时，如果在该列数据存在重复值，那 么系统将返回错误信息。在实际的编程应用中会经常使用到唯一索引。 因为在一个表中，可能会有很多列的列值需要保证其唯一性，如：有 身份证号、工号、学号等，可在这些列上创建唯一索引。
单元 6 索引、视图、存储过程及触发器的应用
单元6 索引、视图、存储过程及触发器的应用
在SQL Server 2000的数据库对象中，除了 表之外，索引、视图、存储过程及触发器是 数据库中最重要的4种对象，能否恰当地使用 这些对象，对于充分发挥SQL Server 2000的 性能至关重要。 提高查询速度——索引 定制数据——视图 定制功能——存储过程 自动处理数据——触发器
6.1.1 索引的基础知识
4）建立索引的原则 考虑建索引的列 如果在一个列上创建索引，该列就称为索引列。索引列中的值
称为关键字值。考虑建索引的列有如下这些： ① 主键 通常，检索、存取表是通过主键来进行的。因此，应该考
虑在主键上建立索引。 ② 连接中频繁使用的列 用于连接的列若按顺序存放，系统可以很快地执行连接。
6.1.3索引的操作
在student数据库中，为“课程”表创建一个基于“课程号” 的名为khh_index，唯一性聚簇索引，升序，填充因子50%。
USE student GO
CREATE UNIQUE CLUSTERED INDEX khh_index ON 课程(课程号ASC ) WITH FILLFACTOR = 50
定索引非叶级中每个索引页上保持开放的空间，即非叶级 的索引页的数据充满度。PAD_INDEX 必须和Fillfactor一 起使用，而且Fillfactor的值决定了PAD_INDEX指定的充满 度。PAD_INDEX 选项只有在指定了 FILLFACTOR 时才 有用，因为 PAD_INDEX 使用由 FILLFACTOR 所指定的 百分比。
表中排序的索引列，并且记录了索引列在数据表中的物理存储位置， 实现了表中数据的逻辑排序。 2）使用索引的意义
可以大大加快数据检索速度。 通过创建唯一索引，可以保证数据记录的唯一性。 在使用ORDER BY和GROUP BY子句进行检索数据时，可以显著 减少查询中分组和排序的时间。 使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性 能。 可以加速表与表之间的连接，这一点在实现数据的参照完整性方 面有特别的意义。
6.1.3索引的操作
在以上语法形式中： UNIQUE：指定创建的索引是唯一索引。如果不使用这个关键字， 创建的索引就不是唯一索引。 CLUSTERED|NONCLUSTERED：指定被创建索引的类型。使用 CLUSTERED创建的是聚簇索引；使用NONCLUSTERED创建的 是非聚簇索引。这两个关键字中只能选其中的一个。 index_name：为新创建的索引指定的名字。 table_name：创建索引的基表的名字。 column_name：索引中包含的列的名字。 ASC|DESC：确定某个具体的索引列是升序还是降序排序。默认 设置为ASC升序。 PAD_INDEX和FILLFACTOR：填充因子，它指定 SQL Server 创 建索引的过程中，各索引页的填满程度。 DROP_EXISTING：删除先前存在的、与创建索引同名的聚簇索 引或非聚簇索引。
6.1.2索引的分类
根据索引的顺序与数据表的物理顺序是否相同，可以把索 引分成两种类型：聚簇索引与非聚簇索引。 聚簇索引（Clustered Index）
数据表的物理顺序和索引表的顺序相同，它根据表中的 一列或多列值的组合排列记录。 非聚簇索引（Nonclustered Index）
数据表的物理顺序和索引表的顺序不相同,索引表仅仅包 含指向数据表的指针，这些指针本身是有序的，用于在表 中快速定位数据。
任务6 创建索引、视图、存储过程及触发器
一、任务目的 1．学会索引的创建及其作用。 2．明确索引的查看、索引的分析与维护的常用方法。 3．学会视图的创建及其作用。 4．明确视图的查看及修改视图的方法。 5．学会存储过程的创建及其使用 6．学会触发器的创建及其使用 二、知识准备 1．了解聚集索引和非聚集索引的概念。 2．掌握使用企业管理器、SQL语法和索引向导创建索引的步骤。 3．了解索引更名系统存储过程的用法。 4．了解删除索引的SQL命令的用法。 5．了解索引分析与维护的常用方法。 6．掌握创建视图的方法。 7．掌握修改视图的SQL语句的语法格式。 8．了解视图更名的系统存储过程的用法。 9．了解删除视图的SQL语句的用法。 10．掌握存储过程的创建及其使用方法。 11．掌握触发器的创建及其使用方法
单元6 索引、视图、存储过程及的应用
6.1 索引的应用 6.2 视图的应用 6.3存储过程和触发器的应用
6 .1 索引的应用
索引是一种特殊类型的数据库对象，它 保存着数据表中一列或几列组合的排序结构。 为数据表增加索引，可以大大提高数据的检 索效率。索引是数据库中一个重要的对象， 本章将详细介绍索引的基本概念、使用索引 的意义、创建索引的方法以及对索引的操作。
6.1.1 索引的基础知识
3）使用索引的代价 ①创建索引要花费时间和占用存储空间。 创建索引需要占用存储空间，如创建聚簇索引需要占用的存储空 间是数据库表占用空间的1.2倍。在建立索引时，数据被复制以便 建立聚簇索引，索引建立后，再将旧的未加索引的表数据删除。 创建索引也需要花费时间。 ② 建立索引加快了数据检索速度，却减慢了数据修改速度。 因为每当执行一次数据的插入、删除和更新操作，就要维护索引。 修改的数据越多，涉及维护索引的开销也就越大。如果将一些数 据行插入到一个已经放满行的数据页面上，还必须将这个数据页 面中最后一些数据移到下一个页面中去，这样，还必须改变索引 页中的内容，以保持数据顺序的正确性。这就是对索引的维护。 由于修改数据时要动态维护其索引，所以，对建立了索引的表执 行修改操作要比未建立索引的表执行修改操作所花的时间要长。 因此，创建索引虽然可以加快数据查询的速度，但是却会减慢数 据修改的速度。
6.1.3索引的操作
4.创建复合索引 有些索引列只有一列，而有些索引列由两列或更多列组成。我们把由两 列或更多列组成的索引称作“复合索引”。 ⑴ 复合索引的特征
① 把两列或更多列指定为索引列。 ② 将复合列作为一个整体进行搜索。 ③ 创建复合索引中的列序不一定与表定义列序相同。 在student数据库中，为“教师任课”表创建一个基于“教师编号，课程 号”组合列的聚集、复合索引jsrk_index，升序，填充因子50% 。 USE student GO CREATE CLUSTERED INDEX jsrk_index ON 教师任课 (教师编号 ASC, 课程号 ASC) WITH FILLFACTOR = 50 GO
6.1.3索引的操作
FILLFACTOR FILLFACTOR的作用是，当系统新建或重建索引时，在每一
个索引页上预先留出一部分空间，使得系统在新增索引信 息时能够保持索引内容在索引页上尽量连续。它使得索引 的页分裂度最小，并可以对性能微调。
PAD_INDEX Fillfactor只能指定叶级索引页的数据充满度。PAD_INDEX指
如外键，除用于实现参照完整性外，还经常用于进行表 的连接。 ③ 在某一范围内频繁搜索的列和按排序顺序频繁检索的列。
6.1.1 索引的基础知识
2. 不考虑建索引的列 建立索引需要产生一定的存储开销，在进行插入和更新数据 的操作时，维护索引也要花费时间和空间，因此，没有必要 对表中的所有列都建立索引。创建索引与否以及在哪些列上 建立索引，要看建立索引和维护索引的代价与因建立索引所 节省的时间相比哪个更合算。一般来说，如下一些列不考虑 建立索引： ① 很少或从来不在查询中引用的列，因为系统很少或从来 不根据这个列的值去查找数据行。 ② 只有两个或很少几个值的列（如性别，只有两个值“男” 或“女”），以这样的列创建索引并不能得到建立索引 的好处。 ③ 以bit、text、image数据类型定义的列。 ④ 数据行数很少的小表一般也没有必要创建索引。
6.1 索引的应用
6.1.1 索引的基础知识 6.1.2 索引的分类 6.1.3 索引的操作 6.1.4 设置索引选项 6.1.5 索引的分析与维护 6.1.6 索引视图 6.1.7 案例中的索引
6.1.1 索引的基础知识
索引
1）索引的概念 索引是针对一个表，以表列为基础建立的数据库对象，它保存着
6.1.3索引的操作
2. 创建索引的SQL语句语法 创建索引使用的是CREATE INDEX语句。CREATE INDEX 语句的语法形式如下：
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON table_name（column_name [ ASC | DESC ] [ ,...n ] ） [WITH [PAD_INDEX] [[，] FILLFACTOR = fillfactor] [[，] DROP_EXISTING] ]
创建非聚簇索引的几个注意事项： 创建非聚簇索引实际上是创建了一个表的逻辑顺序的 对象 索引包含指向数据页上的行的指针 一张表可创建多达249个非聚簇索引
6.1.2索引的分类
索引的分类
系部代 码 01 02 03 04 05 06 07 08
指针地 址 8 2 5 4 1 7 6 3
系部代码 05 02 08 04 03 07 06 01