SQL server 2008 索引与数据完整性

。第6章索引和数据完整性，索引，数据完整性，综合应用程序培训，6.1指数分类，6.1.1指数1。聚集索引聚集索引对表中数据行的键值进行排序，并存储相应的数据记录，使数据表的物理顺序与索引顺序一致。SQLServer2008以B树(BTREE)方式组织聚集索引，B树被构造为包含多个节点的树。顶部节点形成索引的起点，称为根。每个节点包含几个索引列值。一个节点中的每个值都指向另一个节点或指向表中的一行。一个节点中的值必须按顺序排列。指向一行的节点称为叶页面。叶页面本身也是相互关联的。一页有一个指向下一组的指针。这样，表中的每一行在索引中都有相应的值。查询时，可以根据索引值直接找到该行。聚集索引中的B树的叶节点存储数据页信息。聚集索引在索引的叶级保存数据。这意味着无论表的哪个字段(或哪些字段)在聚集索引中，这些字段都将按顺序存储在表中。由于这种排序，每个表只有一个聚集索引。嘿。6.1.1指数分类，2。非聚集索引非聚集索引完全独立于数据行的结构。SQLServer2008还通过B树来组织非聚集索引，这与聚集索引的不同之处在于，非聚集索引B树的叶节点不存储数据页信息，而是存储非聚集索引的键值，并且每个键值项都有一个指向包含键值的数据行的指针。对于非聚集索引，表中的数据行不以非聚集键的顺序存储。在非聚集索引中，从索引行到数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚合中。对于堆集，行定位器是指向行的指针。对于具有聚集索引的表，行定位器是聚集索引键。只有在表上创建聚集索引时，表中的行才会以特定的顺序存储。这些行按聚集键顺序存储。如果表只有非聚集索引，则其数据行存储在无序堆中。嘿。6.1.2系统表sysindexes，这里将介绍另一个系统表sysindexes。当用户创建数据库时，系统将自动创建系统表sysindexes。用户创建的每个索引都将在系统表sysindexes中注册。创建索引时，如果索引已经存在，系统将报告错误。因此，在创建索引之前，应该查询sysindexes表。如果要定义的索引已经存在，应该在创建索引之前将其删除。当然，也可以采用其他措施，例如，如果检测到索引已经存在，则不创建要定义的索引。系统表sysindexes中的主要字段如表6.1所示。表6-1系统表中sysindexes的主要字段。在6.1.3中创建索引，1。在接口模式下创建索引以下是通过在XS表中按图书卡号建立主键索引和按名称建立非唯一索引(索引组织是非聚集索引)来创建索引的示例。启动“对象资源管理器”选择“DBO”中的“数据库XSBook”。“表”中的“XS”右键单击其中的“索引”项，并在弹出的快捷菜单中选择“新建索引(N)”菜单项。嘿。6.1.3索引创建，此时，用户可以在弹出的“新建索引”窗口中输入索引名称(索引名称在表中必须是唯一的)，如PK_XS，选择索引类型为“聚合”，勾选“唯一”复选框，在新建索引窗口中点击“添加”按钮，在弹出的选择列窗口中选择要添加的列(如图6.1所示)，添加完成后点击“确定”按钮，在主界面中为索引键列设置相关属性，并，图6.1添加索引键列，6.1.3在表设计器窗口中创建索引，如下所示：(1)右键单击“dbo。XS”表，在弹出的快捷菜单中选择“设计”菜单项，并打开“表设计器”窗口。(2)在表格设计器窗口中，选择图书证号属性栏，右键单击鼠标，并在弹出的快捷菜单中选择索引/键菜单项。单击打开的“索引/键”窗口中的“添加”按钮，并在右侧“标识”属性区域的“名称”列中确定新索引的名称(使用系统默认名称或重命名)。单击右侧常规属性区域中“列”列后的“”按钮，修改要编制索引的列。如果“唯一”列设置为“是”，则索引是唯一的。在“表设计器”列下的“创建为聚集”选项中，可以设置是否创建为聚集索引。因为XS表中已经存在聚集索引，所以不能修改此选项，如图6.2所示。图6.2“索引/键”窗口，6.1.3索引创建，2。使用SQL命令创建索引使用CREATEINDEX语句为表创建索引。语法格式：CREATEUNIQUE/*指定索引是否唯一*/群集|非群集/*索引的组织方式*/INDEXindex_name/*索引名称*/在数据库_name。模式名称。| schema _ name。表_或_视图_名称(列ASC | desc)，n)/*基于*/include的索引定义(列名，和，n)/*索引选项*/on partition _ scheme _ name(column _ name)/*指定分区方案*/|filegroup_name/*指定文件组*/| default FILESTREAM _ on FILESTREAM _ file group _ name | partition _ scheme _ name | null /*指定文件流数据位置*/；，6.1.3索引创建，其中：3360= pad _ index= ON | OFF | fill factor=fill factor | sort _ in _ tempdb= ON | OFF | ignore _ dup _ key= ON | OFF | statistics _ no computer= ON | OFF | DROP _ EXISTING= ON | OFF | ONLINE= ON | OFF | ALLOW _ LOCKS= ON | OFF | ALLOW _ PAGE _ LOCKS= ON | OFF | MaxDOP=MAX _ DENCE _ LOCK _ LOCKS，6.1.3索引创建，示例6.1对于JY表，索引由图书证号ISBN创建。/*创建一个简单的索引*/usexbookpoints(选择名称从系统索引位置名称=JY _ NUM _ IND) DropIndexJY。JY _ NUM _ INDGOCreateIndexJY _ NUM _ INDONJY(图书馆卡号，ISBN)GO示例6.2基于图书表的ISBN列创建一个唯一的聚集索引。因为指定了clustered子句，所以索引将对磁盘上的数据进行物理排序。/*创建唯一的集群索引*/创建唯一的集群扩展图书_标识_印度尼西亚图书(ISBN)转到，6.1.3索引创建，示例6.3索引是根据XS表中的借款金额字段创建的，其中使用了FILLFACTOR子句。Createnoncludedindexnum _ indonss(借阅量)WITHFILLFACTOR=60示例6.4根据XS表中的图书馆卡号字段创建唯一的聚集索引。如果输入了重复的键值，INSERT或UPDATE语句将被忽略。用IGNORE_DUP_KEY创建Createuniqueclusteredindexxs _ indonss(图书馆卡号)。6.1.3索引创建，有以下几点来解释索引创建：(1)在计算列上创建索引。对于UNIQUE或PRIMARYKEY索引，只要满足索引条件，就可以包含计算列，但计算列必须是确定性的和准确的。如果计算列中有函数，则当函数具有相同的参数输入且输出结果必须相同时，计算列将被确定。虽然有些函数(如getdate()函数)每次调用时输出不同的结果，但不能在计算列上定义索引。无法在文本、ntext或图像的计算列上创建索引。(2)在视图上创建索引。可以在视图上定义索引，索引视图可以自动反映创建索引后对基表数据所做的更改。创建索引6 . 1 . 3示例6.5创建视图并为视图创建索引。/*定义视图。在下面的示例中，由于使用了WITHSCHEMABINDING子句，因此在定义视图时，SELECT子句中的表名必须是架构名。视图名称的形式。*/CreateView1 with ChemaBindingassSelect呼叫号码、标题、来自dbo的名称。dbo JY。BOOK，Dbo . xswhere jy . ISBN=BOOK . ISBN andxs . library card number=jy . library card number GO/* define index on VIEW 1 */createuniqueclusteredindeexistence 1 andbo . VIEW 1(call number ASC)GO，6.1.4删除索引。1.通过接口删除索引通过接口删除索引的主要步骤如下：启动“SQL server managementstudio”展开数据库“XSBook”“Table”“DBO”。XS 对象资源管理器中的索引，选择要删除的索引，右键单击，在弹出的快捷菜单中选择删除菜单项。在打开的“删除对象”窗口中单击“确定”。2.通过以下命令删除索引语法格式：删除索引索引_名称表_或_视图_名称，n |表_或_视图_名称。索引名称，n示例6.6删除由示例6.1为XSBOOK数据库中的表JY创建的索引JY_num_ind。usexsbookgoifeexis(selectnamfromsindexswherename=jy _ num _ ind)dropbindexjy . jy _ num _ indgo，6.2数据完整性，6.2.1数据完整性分类1。域完整性域完整性也称为列完整性，是指列数据条目的有效性。实现域完整性的方法包括：限制类型(通过数据类型)、格式(通过检查约束和规则)或可能的值范围(通过检查约束、默认定义、非空定义和规则)等。CHECK约束通过显示输入到列中的值来实现域完整性；定义DEFAULT后，如果列中没有输入值，则填充默认值以实现域完整性。域完整性也可以通过将列定义为非空来实现，以限制输入值为空。例如，如果数据库XSBOOK的XS表允许读者当前最多借阅20本书，为了限制读者当前的借阅量，可以在定义XS表时指定“0借阅20”的约束条件来达到目的。嘿。6.2.1数据完整性分类，示例6.7定义表XS时，定义借入数量字段的约束条件。以下语句定义了字段并定义了约束条件*/借出金额检查(借出金额=0，借出金额=20)不为空，照片变量二进制(最大值)为空。6.2.1数据完整性分类，2。实体完整性实体完整性也称为行完整性，需要表中的主键，其值不能为空，并且可以唯一标识相应的记录。数据的实体完整性可以通过索引、UNIQUE约束、PRIMARYKEY约束或IDENTITY属性来实现。例如，对于XSBOOK数据库中的XS表，以图书卡号为主键，每个读者的图书卡号可以唯一标识该读者对应的行记录信息，因此在输入数据时，不能有相同图书卡号的行记录，通过在图书卡号字段上建立主键约束，可以实现表XS的实体完整性。3.参照完整性参照完整性也称为参照完整性。参照完整性确保主表中数据和从表中数据的一致性。在SQLServer2008中，通过定义外键(外部代码)和主键(主代码)之间或外键和唯一键之间的对应关系来实现引用完整性。引用完整性确保所有表中的键值一致。表6.2和表6.3显示了数据完整性的分类以及XS表和JY表之间的对应关系。嘿。6.2.1数据完整性的分类，如果定义了两个表之间的引用完整性，则需要：(1)不存在的键值不能从表中引用。例如，JY表中某一行记录的图书馆卡号必须已经存在于XS表中。(2)如果主表中的键值发生变化，那么在整个数据库中，对从表中键值的所有引用都应该一致地发生变化。例如，如果修改了XS表中的某个图书馆卡号，则JY表中所有相应的图书馆卡号都应相应地进行修改。(3)如果要删除主表中的记录，应首先从表中删除与该记录匹配的相关记录。嘿。6.2.2域完整性的实现，1。CHECK约束的定义和删除。无法为时间戳和标识字段定义CHECK约束。(1)通过界面创建和删除CHECK约束。对于XSCOOK数据库的XS表，如果要求读者的图书卡号由8个数字字符组成，不能为“00000000”，通过企业管理器对图书卡号字段定义此约束可以如下进行：启动“SQLServerManagementStudio”展开“数据库”“XSCOOK”“表”选择“dbo”。XS”并右击鼠标选择“设计”菜单项。(2)在打开的表格设计器窗口中，选择库卡号属性栏，右键单击鼠标选择 CHECK constraint 菜单项。嘿。6.2.2域完整性实现，在打开的“CHECK约束”窗口中(如图6.3所示)，点击“添加”按钮添加一个“CHECK约束”。在常规属性区的“表达式”栏后点击“”按钮(或直接在文本框中输入内容)，打开“CHECK约束表达式”窗口，将相应的CHECK约束表达式编辑为：“图