1、 引入索引机制,是为了提升对数据库查询效率。索引、创建与管理方法6.1 索引第1页1INSERT INTO ASD VALUES(7,F,79,22)INSERT INTO ASD VALUES(2,B,89,21)INSERT INTO ASD VALUES(5,E,94,20)INSERT INTO ASD VALUES(10,D,76,22)INSERT INTO ASD VALUES(6,A,64,20)INSERT INTO ASD VALUES(4,C,88,21)INSERT INTO ASD VALUES(1,H,91,22)INSERT INTO ASD VALUES(8,G
2、,68,19)Create table ASD(NO int not null,NAME nchar(4)not null,SCORE numeric(4,1)null,AGE int not null)go先建立一个名为“ASD”表，并插入统计：在未建立主键约束,并未建立索引时,统计显示次序是物理次序第2页21索引概念(1)创建索引，能够防止全表扫描，从而提升查询速度.(2)索引是数据库对象,分别用CREATE命令建立,用DROP命令删除,用ALTER命令修改.(3)索引与与表（或视图）关联，并按表中指定列值排列次序映象表。建立索引后表存放由两部分组成:一是用来存放表数据页面;二是用来存放索
3、引索引页面。索引就存放在索引页面上。(4)SQL Server读取数据过程首先确定是否存在索引，然后查询优化器（负责生成查询优化执行计划组件）从表扫描和使用索引这两种方法中，确定对于数据访问哪种方式更为有效。(5)数据检索方式:先搜索索引页面，从中找到所需数据指针，再直接经过指针从数据页面中读取数据。（6）应注意问题:不应该在每一个列上都创建索引,以免降低系统速度。插入、删除或更新索引列比非索引列要花更长时间。第3页3索引次序1 A2 B3 C4 D5 E6 F 7 G8 HCREATE INDEX ASD_NAME_IND ON ASD(NAME,ASD)索引页面数据页面行定位器已按表中NA
4、ME字段建立索引表存放。第4页4索引键值次序索引键值次序被引用数据行存放次序被引用数据行存放次序12345674713256行定位器索引页面索引页面数据页面数据页面第5页5l2索引存放结构l(1)簇索引(Clusteredlndex)l对表物理数据页中数据按列进行排序，然后再重新存放到磁盘上。l所以一个表只能有一个簇索引。簇索引查找数据很快。l（2)适于使用簇索引情况 经惯用于排序从表中检索数据列。经常次序访问列。l每个索引行包含一个键值和 一个指针，分别指向数据行。(3）表中按次序排列序列（如拼音次序、日期和数字次序）在查询时候不需要逐行查找，所以查询速度快。(4)以下数据类型列不可用作聚集
5、索引键：ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml或image 第6页6CREATE CLUSTERED INDEX ASD_CLU_NOON ASD(NO ASC)建立簇索引后统计重新按指定列值排列了次序,且则行 定位器中存放是簇索引索引键：索引页行定位器数据页第7页72)非簇索引(Nonclusteredlndex)。非簇索引将行定位器按关键字值，用一定方式排序。则行定位器存放是指向数据行指针。非簇索引检索效率较 低。一个表最多能够建248个非簇索引。索引列中数据频繁更改时应建立非簇索引。第8页8索引次序1 A2 B3
6、C4 D5 E6 F 7 G8 HCREATE INDEX ASD_NAME_IND ON ASD(NAME,ASD)索引页面数据页面行定位器 在非簇索引中，行定位器存放是指向数据行指针。数据没有按索引键重新排列次序。第9页912345674713256索引键值次序索引键值次序被引用数据行存放次序被引用数据行存放次序行定位器第10页10CREATE CLUSTERED INDEX Asd_clu_nocf2 ON ASD(NO ASC)建立了聚集索引后,统计按索引顺序重新物理地排列了统计次序.一个表只能有一种物理排列方式,所以一个表只能建一个聚集索引.第11页11 3索引键组成依据索引键组成，
7、能够将索引分为三种类型。1)唯一索引创建唯一索引，可确保表内索引列中不包含重复数据内容。2)复合索引在一个表中，经过连接或附接两个或多个列值而创建索引。3)覆盖索引当索引中包含了需要全部信息时，这个索引称为覆盖索引。它键值包含了满足查询条件全部数据。第12页12612 创建索引1.创建索引时，要注意几点:(1)当在表中创建主关键字约束或唯一性约束时，SQL Server自动创建一个唯一性索引。(2)假如表中已经有数据，那么在创建索引时，SQL Server会检验数据正当性。当有不正当数据时，创建索引将失败。(3)当有多个列作为关键字时，应创建复合索引，即索引包含有两个或多个列。(4)基于相同列
8、但列次序不一样复合索引也是不一样。第13页13简单建立索引命令CREATE INDEX 索引类型索引类型 ON (列名列名 排序方式排序方式，n)索引类型有:CLUSTERED-簇索引NONCLUSTERED-非簇索引UNIQUE -唯一索引第14页14l2.创建索引完整格式：l CREATE 索引类型 INDEX lON (列名1ASC I DESC ,n )l WITH l ON l其中:l索引类型有:UNIQUE、CLUSTERED、NONCLUSGTEREDl索引选项为以下属性组合：l PAD INDEXl FILLFACTOR=填充因子l IGNORE_DUP_KEYl DROP_E
9、XISTINGl STATISTICS_NORECOMPUTEl SORT_IN_TEMPDBl 其中，第15页15l1)UNIQUEl 创建唯一索引。创建唯一索引后，假如执行INSERT或UPDATE操作后会造成有重复索引值出现时，该INSERT或UPDATE操作会失败。l 2)CLUSTEREDl 指明创建索引为簇索引。默认为创建索引为非簇索引。l 3)NONCLUSTEREDl创建索引为非簇索引。第16页164)PAD_INDEX 指定填充索引内部节点行数，最少应大于等于两行。PAD_INDEX选项只有在 FILLFACTOR选项指定后才起作用，因为PAD_INDEX使用与FILLFAC
10、TOR相同百分比。默认时SQLServer确保每个索引页最少有能容纳一条最大索引行数据空闲空间。假如FILLFACTOR指定百分比不够容纳一行数据，SQLServer会自动内部更改百分比。第17页175)FILLFACTOR；填充因子它指定创建索引时每个索引页数据占索引页大小百分比。fillfactor值为1到100，它其实同时指出了索引页保留自由空间占索引页大小 百分比，即100-fillfactor。第18页186)IGNORE DUP KEY此选项控制了包含一个唯一约束列中插入重复数据时SQLServer所作反应。当选择此选项时，SOL Server返回一个错误信息，跳过此行数据插入继续
11、执行下面插入数据操作。当没选择此选项时，SQLServer不但会返回一个错误信息，还会回滚整个INSERT语句。7)DROP EXISTING指定要删除同名索引并重新创建。第19页19 例 在图书表中为出版社创建索引。CREATE INDEX idx_press ON book (press)例 创建出版社和作者复合索引。使用DROP EXISTING是因为前面例子已经创建了索引idx_press，所以先删除同名索引，然后再创建新索引。CREATE INDEX idx_press ON book (press，author)WITH DROP_EXISTING第20页20 例6-3创建唯一非簇
12、索引。CREATE UNIQUE INDEX idx_press ON book (book_id)WITH DROP_EXISTING 例6-4使用填充因子创建唯一非簇索引。CREATE UNIQUE INDEX idx_press ON book (book_id)WITH PAD_INDEX,FILLFACTOR=50,DROP EXISTING第21页21613 删除索引1.索引删除语法：DROP lNDEX 表名.索引名,n2.几点说明：(1)DROP lNDEX命令不能删除由CREATE TABLE或ALTER TABLE命令创建PRIMARY KEY或UNIQUE约束索引。(2)
13、不能删除系统表中索引。(3)在删除簇索引时，表中全部非簇索引都将被重建。第22页22614 索引优化关于创建索引提议以下。(1)将更新尽可能多行查询写入单个语句内，而不要使用多个查询更新相同行 (2)使用索引优化分析查询并取得索引提议。(3)对聚集索引使用整型键，另外，在唯一列、非空列或标识列上创建聚集索引能够取得较佳性能。(4)在查询经惯用到全部列上创建非聚集索引。(5)检验列唯一性。第23页2372 自定义函数第24页247.2.1 自定义函数基本概念1.自定义函数:是用户为实当代码封装和重用,将一组T-SQL语句按一定格式定义得到,并有输入参数,运行后有返回值。2.自定义函数优点：(1)
14、允许模块化程序设计。一次创建可屡次调用。(2)执行速度更加快(首次优化编译，今后直接运行)。(3)降低网络流量。3.自定义函数与存放过程比较项目自定义函数存放过程参数不允许输出参数允许多个输入输出参数返回值有且只有一个返回值能够没有返回值调用能够嵌入查询语句中使用必须单独调用第25页25722 自定义函数三种类型1标量函数(1)函数返回单个数值(返回值类型在RETURNS子句中指定)。(2)函数返回单个数值，返回由BEGINEND块指定多个值。返回值不可为text、ntext、image、cursor和timestamp数据类型。第26页26 2表值函数(1)内嵌表值函数 返回由选择结果组成统
15、计集表。它能够替换视图,且比视图逻辑功效愈加强大。(2)多语句表值函数返回由选择结果组成统计集表,函数需要由BEGINEND限定函数体。第27页27多语句表值函数主体中只允许使用语句：赋值语句。除错误捕捉语句 TRYCATCH外流程控制语句。定义局部数据变量和局部游标DECLARE语句。SELECT语句，其中选择列表包含为局部变量分配值表示式。游标操作，该操作引用在函数中申明、打开、关闭和释放局部游标。只允许使用以INTO子句向局部变量赋值FETCH语句；不允许使用将数据返回到客户端FETCH语句。修改table局部变量INSERT、UPDATE和DELETE语句。调用扩展存放过程EXECUT
16、E语句。第28页28723 创建与使用用户自定义函数CREATE FUNCTION (参数名 数据类型 =默认值 ,)RETURNS 返回值数据类型 WITH 选项 AS BEGIN SQL语句 RETURN 返回表示式 END 其中：选项有二：（1）ENCRYPTION能够实现用户自定义函数加密；（2）SCHEMA BINDING将自定义函数绑定到它所引用数据库对象。第29页29例7-17自定义标量函数，实现对图书价格高与低评价。USE 图书馆If EXISTS(SELECT name FROM sysobjects WHERE name=fc_price AND type=FN)DROP
17、FUNCTION fc_price GO CREATE FUNCTION fc_price(priceinput money)RETURNS nvarchar(10)BEGIN DECLARE returnstr nvarchar(10)If priceinput40 SET returnstr=较贵图书 ELSE SET returnstr=廉价图书 RETURN returnstr END使用该函数。SELECT top 20 book_name,price,dbo.fc_price(price)FROM book 第30页30例7-18自定义内嵌表值函数fc_press，依据指定出版社参
18、数查询该出版社出版图书，返回结果统计集。USE library GO CREATE FUNCTION fc_press(press varchar(30)RETURNS table AS RETURN (SELECT bookname，author，press，price FROM book WHERE press =press )GO 下面语句说明了怎样使用新建立内嵌表值函数来获取清华大学出版社出版图书信息。select *From fc_press(清华大学出版社)第31页31Create function XY_fc(专业 nvarchar(8)Returns tableASReturn
19、select*from 班级 WHERE 专业=专业GO select*FROM XY_fc(软件工程)第32页32第33页33例7-19自定义多语句表值函数 fc_press_1，依据指定出版社参数查询该出版社出版图书，返回结果统计集。请注意与定义内嵌表值函数fc_press 对比。USE library GO CREATE FUNCTION fc_press_1(press nvarchar(30)RETURNS tb_press table (book_name nvarChar(30)NOT NULL，author nvarchar(20)NULL，press nvarChar(30)
20、NULL，plice money NULL )AS BEGIN INSERT tb_Press SELECT book_name,author,press,price FROM book WHERE press=press RETURN END GO 下面这两个语句分别调用多语句表值函数fcjoress_l宋查询清华大学出版社和科学出版社出版图书。SELECT*from dbofc press l(清华大学出版社)GO SELECT*from dbofc_press_l(科学出版社)GO第34页34724 自定义函数管理1查看自定义函数 EXEC sp_help 如要查看fcrice信息详细语
21、句为：EXEC sp_help fc price利用这个语句，能够看到函数名称及相关参数。但要看到函数详细定义，需要用系统存放过程sphelptext。其语法以下：EXEC sp_helptext函数名 查看自定义函数fc price语句以下：EXEC sp_helptext fc_price 第35页35 2修改自定义函数 修改自定义函数经过ALTERFUNCTION关键字来完成。其语法以下：ALTER FUNCTION函数名 (Le参数名参数数据类型 =默认值 ,.n)RETURNS返回值数据类型 WITH选项 AS BEGIN SQL语句 RETURN返回表示式 END 能够看出，修改自
22、定义函数与创建自定义函数语法基本一样，各个参数意义与创建 自定义函数参数意义相同。3删除自定义函数DROP FUNCTION函数名 如要删除自定义函数fIrice能够使用以下语句：DROP FUNCTION fc_price第36页367.2.4 自定义函数的管理第37页371查看自定义函数语句（1）查看自定义函数信息(名称及相关参数):EXEC sp_help 比如：EXEC sp_help fc_price（2）查看自定义函数定义（语句）：EXEC sp_helptext 比如：EXEC sp_helptext fc_price第38页382 2修改自定义函数修改自定义函数语法以下：ALTER FUNCTION (参数名 数据类型 =默认值 ,.n)RETURNS 返回值数据类型 WITH 选项 AS BEGIN SQL语句 RETURN 返回表示式 END修改与创建自定义函数语法基本一样，参数意义相同。第39页393 3删除自定义函数删除自定义函数语法以下：DROP FUNCTION 比如：DROP FUNCTION fc_price第40页40第41页41
