【数据库原理与SQL Server应用】Part07——数据完整性、规则和索引

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

【数据库原理与SQL Server应用】Part07——数据完整性、规则和索引

一、数据完整性

引入
当操作表中数据时由于种种原因经常会遇到一些问题。
例如Student表中StudentID列是不允许重复的。但有可能在工作人员输入信息时误操作将两个学生的StudentID列值输入相同了
或者有个学生的StudentID列值在Mark表中存在但在Student表中找不到。
这就要求数据库管理系统能自动实现这样的错误校正。

  • 数据完整性是指存储数据库的数据的一致性和准确性。
  • 数据完整性包括实体完整性、参照完整性和用户自定义完整性。
  • SQL Server 提供了强大的数据完整性功能也分为3类实体完整性、域完整性和引用完整性。
  • 在SQL Server 中将完整性约束又称为表约束。

1.1 实体完整性

1.1.1 主键约束

前文我们已经介绍了在表结构设计器中可以设置主键。某列例如StudentID列、CourseID列、AuthorID列被设置为主键该列取值就不能为空也不能重复。当表中主键列值违反主键约束即出现重复值或有空值存在系统自动提示错误信息。例如当在Course表中输入一门新课如果CourseID号与其他数据重复违反了主键约束系统将提示出错如图所示
在这里插入图片描述

主键约束是表对象在表的键中可以查看。如图所示
在这里插入图片描述

例使用CREATE TABLE语句创建Course1表。

USE COLLEGE
GO
CREATE TABLE Course1
(
CourseID   NCHAR(3) NOT NULL PRIMARY KEY,
CourseName NCHAR(20) NOT NULL,
Credit     INT NULL,
)
GO

CourseID被设置为主键需要PRIMARY KEY字句。PRIMARY KEY字句可以放在列名后面也可以放在最后。

例使用CREATE TABLE语句创建Mark1表。

USE COLLEGE
GO
CREATE TABLE Mark1
(
CourseID  NCHAR(3) NOT NULL,
StudentID NCHAR(10) NOT NULL,
Score     INT NULL,
CONSTRAINT PK_Mark1 PRIMARY KEY(CourseID,StudentID)
)
GO

CourseID和StudentID被设置为主键组合多列主键PRIMARY KEY就不能放在列名后只能放在最后。CONSTRAINT字句用来命名主键。
当使用INSERT、UPDATE语句违反主键约束时系统也会提示错误信息。

例使用INSERT语句给Course表添加新记录。

USE COLLEGE
GO
INSERT Course
VALUES('226','数据结构',5)
GO

如果CourseID列值与其他数据重复违反了主键约束系统将提示出错。
在这里插入图片描述

1.1.2 唯一性约束

一个表只能有一个主键。但有的表中除了主键不能为空和不能有重复值存在之外还有其它列也有同样的要求例如Course表的CourseName列、School表的SchoolName列也不允许为空和不能有重复值存在。这时就可以设置唯一性来实现
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

唯一性约束是表对象在表的键中可以查看。如图所示。主键约束和唯一性约束都在表的键中以键对象的形式存在。它们不仅以名称区分还以图标显示区分。 图标金黄色表示主键对象 图标蓝色表示唯一性约束对象。
在这里插入图片描述

如果唯一性约束设置需要修改或删除可以在表结构设计器中修改或删除也可以针对键对象修改或删除。如图所示
在这里插入图片描述

当用户在School表中输入一个新学院如果SchoolName与其他数据重复违反了唯一性约束系统将提示出错如图所示

在这里插入图片描述
在这里插入图片描述

使用T-SQL语句也可以实现唯一性约束
在CREATE TABLE或ALTER TABLE语句中使用UNIQUE子句实现唯一性约束的创建、修改或删除。

例使用ALTER TABLE语句修改Course表。

USE COLLEGE
GO
ALTER TABLE Course
  ADD CONSTRAINT IX_Course UNIQUE(CourseName)
GO

当使用INSERT、UPDATE语句违反唯一性约束时系统也会提示错误信息。
主键约束和唯一性约束从约束效果看基本相同。
不同的是一个表只能有一个主键约束但可以有多个唯一性约束。

1.2 域完整性

域完整性也称为列完整性是指一个数据集对某一个列是否有效和确定是否允许为空值。在SQL Server中域完整性可以通过空值约束、默认约束和检查约束实现。

1.2.1 空值约束

在前面的文章中已经介绍了在表结构设计器中可以设置允许Null值。某列被设置为NULL或NOT NULL该列取值就可以为空或不能为空。一旦某列被设置为主键或唯一性索引系统自动将其设置为NOT NULL。当表中某列被设置为NOT NULL而其值违反约束系统自动提示错误信息。
在这里插入图片描述

如果空值约束设置需要修改或删除可以在表结构设计器中修改或删除。
使用T-SQL语句也可以实现空值约束。在CREATE TABLE或ALTER TABLE语句中使用NULL或NOT NULL实现空值约束的创建、修改或删除。
在这里插入图片描述

1.2.2 默认约束

默认约束也称为默认值约束在前面的文章中也已经介绍了。当某列例如Sex列设置默认值即设置了默认约束。该列取值可以是新输入的数据如果没有则取默认值。默认约束是表对象在表的约束中可以查看。如果默认约束对象需要重命名或删除可以选择右键菜单选项。如果修改默认约束的值可以在表结构设计器中修改或删除。
在这里插入图片描述
在这里插入图片描述

使用T-SQL语句也可以实现默认约束
在CREATE TABLE或ALTER TABLE语句中使用DEFAULT子句实现默认约束的创建、修改或删除。
例使用CREATE TABLE语句创建Student1表。

USE COLLEGE
GO
CREATE TABLE Student1
(
  StuID  INT PRIMARY KEY,
  Name  NCHAR(10),
  Sex    NCHAR(10) DEFAULT('女')    	/ * 设置默认约束 * /
)
GO

例使用ALTER TABLE语句修改Course表。

USE COLLEGE
GO
ALTER TABLE Course
  ADD CONSTRAINT DE_Course_Credit DEFAULT 4 FOR Credit  	/ * 添加默认约束 * /
GO

1.2.3 检查约束

例如在Student表中Sex列只能取值男或女。如果误输入其它值则肯定错误。又例如在Mark表中Score列通常只能取值0~100超出范围也错误。这些错误都是逻辑性错误。这时就可以设置检查约束来进行约束。检查约束又称为CHECK约束。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

CHECK约束是表对象在表的约束中可以查看。如果CHECK约束对象需要重命名或删除可以选择右键菜单选项。如果修改CHECK约束的约束表达式可以在CHECK约束对话框中操作。
在这里插入图片描述

使用T-SQL语句也可以实现CHECK约束
在CREATE TABLE或ALTER TABLE语句中使用CHECK子句实现。

例使用CREATE TABLE语句创建Teacher表。

USE COLLEGE
GO
CREATE TABLE Teacher
(
name NCHAR(10),
sex  NCHAR(2) DEFAULT '男' ,
CONSTRAINT CK_Emp_sex CHECK(sex='男' OR sex='女')     	/* 创建CHECK约束 */
)
GO

例使用ALTER TABLE语句修改Mark表。

USE COLLEGE
GO
ALTER TABLE Mark
  /* 添加CHECK约束 */
  ADD CONSTRAINT CK_Mark_Score CHECK(Score>=0 and Score<=100)
GO

当使用INSERT、UPDATE语句违反唯一性约束时系统也会提示错误信息。

1.3 引用完整性

在表结构设计器中设置外键关系可以在主键表中进行也可在外键表中进行。在SQL Server中通常在外键表中操作。例如打开Mark表在表结构设计器选择右键菜单关系选项。进入外键关系对话框。选择添加按钮添加一个新外部关系对象。
在这里插入图片描述
在这里插入图片描述

选择表和列规范选项弹出表和列对话框。用户选择主键表和主键列以及外键表和外部键列。注意设置时主键表和外键表的列应该对应例如Student表的StudentID列对应Mark表的StudentID列而且列数也应相同。
在这里插入图片描述

使用T-SQL语句也可以实现外部约束关系
在ALTER TABLE语句中使用FOREIGN KEY子句和REFERENCES子句实现。

例使用CREATE TABLE语句创建Mark2表同时创建和Course表的外键关系。

USE COLLEGE
GO
CREATE TABLE Mark2
(
CourseID  NCHAR(3),
StudentID  NCHAR(10),
Score      INT,
CONSTRAINT PK_Mark1_Course FOREIGN KEY(CourseID) REFERENCES Course(CourseID)
)
GO

使用T-SQL语句创建外键关系FOREIGN KEY子句后面指定本表的外部键对应的列名REFERENCES子句后面指定主键表表名和主键的列名。
当使用INSERT、UPDATE、DELETE语句违反唯一性约束时系统也会提示错误信息。

二、规则

规则RULES不是数据库中必须定义的对象但定义规则可以保证表中的数据都能满足设计者的要求。虽然规则功能强大在SQL Server系列中一直存在但微软公司在SQL Server联机帮助中说明规则将在后续版本中删除建议用户使用检查约束。所以只做简单介绍。

2.1 规则的概念

规则是用于执行一些与检查约束相同的功能但检查约束比规则更简明。一个列或别名数据类型只能被绑定一个规则。不过一个列可以同时有一个规则以及一个或多个检查约束与其相关联。在这种情况下SQL Server将评估所有限制。约束是某一个表的对象只作用于某一个表。规则是数据库对象作用于整个数据库。CHECK约束被指定为CREATE TABLE语句或ALTER TABLE语句的一部分而规则作为独立的对象创建然后绑定在指定的列上。规则也是一种维护数据库中数据完整性的手段使用它可以避免表中出现不符合逻辑的数据。

2.2 创建规则

创建规则只能使用T-SQL语句命令方式而且创建规则只能在当前的数据库中创建。。T-SQL语句提供了规则创建语句CREATE RULE但是不能在单个批处理中将CREATE RULE语句与其他T-SQL语句组合在一起。其语法格式如下

CREATE RULE [ schema_name . ] rule_name 
AS condition_expression
[ ; ]

例使用CREATE RULE语句创建规则指定变量取值在0~100之间。

USE COLLEGE
GO
CREATE RULE Score_Rull
AS @s_r BETWEEN 0 AND 100
GO

在当前数据库的可编程性对象的规则对象中新建一个名为Score_Rull的规则对象。

2.3 查看规则

创建规则只能使用T-SQL语句命令方式创建但可以在SQL Server Management Studio的对象资源管理器中看到规则。也可以调用相关系统存储过程例如执行p_help系统存储过程获得关于规则的报告。以规则名称作为参数来执行sp_helptext系统存储过程显示规则的文本。若要重命名规则可以执行sp_rename系统存储过程。

2.4 绑定规则

绑定规则是指将已经存在的规则应用到列或用户自定义的数据类型中。创建规则后只有将规则绑定到列规则才会起到约束功能。执行sp_bindrule系统存储过程可将规则绑定到列或别名数据类型。规则必须与列数据类型兼容。只有当尝试在别名数据类型的数据库列中插入值或进行更新时绑定到别名数据类型的规则才会激活。其语法格式如下

sp_bindrule [ @rulename = ] 'rule' , 
     [ @objname = ] 'object_name' 
     [ , [ @futureonly = ] 'futureonly_flag' ]

例执行sp_bindrule系统存储过程绑定规则。

USE COLLEGE
GO
EXEC sp_bindrule 'Score_Rull','Mark.Score'
GO

2.5 解除规则

如果某列已经不适用于某规则则可以解除规则。执行存储过程sp_unbindrule可以将规则从绑定列或用户自定义的数据类型解除。其语法格式如下

sp_unbindrule [ @objname = ] 'object_name' 
     [ , [ @futureonly = ] 'futureonly_flag' ]

例执行sp_unbindrule解除规则。

USE COLLEGE
GO
EXEC sp_unbindrule 'Mark.Score'
GO

2.6 删除规则

如果此数据库不需要某规则则可以删除。在创建同名的新规则之前必须首先删除原有规则而在删除原有规则之前必须先解除绑定。
可以选择右键菜单的“删除”选项删除规则也可以执行DROP RULE语句删除。其语法格式如下
DROP RULE { [ schema_name . ] rule_name } [ ,...n ] [ ; ]

例执行CREATE RULE语句删除规则。

USE COLLEGE
GO
DROP RULE Score_Rull
GO

三、索引

索引是关系数据库的一个基本概念。用户使用数据库最常用的操作就是查询数据在数据量比较大时搜索满足条件的数据可能需要很长的时间。为了提高数据检索的能力数据库中引入了索引的概念。

索引如同书籍的目录有了索引用户可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询可以提高查询性能。对于包含SELECT、UPDATE或DELETE语句的各种查询索引会很有用。索引还可以强制表中的行具有唯一性从而确保表数据的完整性。

SQL Server在存储数据时数据按照输入的时间顺序被放置在数据页上。一般情况下数据存放的顺序与数据本身是没有任何联系的。而索引是与表或视图关联的磁盘上的结构可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键使SQL Server可以快速有效地查找与键值关联的行。

索引主要有以下作用

  • 快速存取、查询数据。
  • 保证数据的一致性。
    -实现表与表之间的参照完整性。
    -在使用GROUP BY、ORDER BY子句进行查询时利用索引可以减少排序和分组的时间。

但索引也有自身的缺点

  • 索引和维护索引要耗费时间。
  • 索引需要占用物理存储空间。
  • 当对表中的数据进行添加、修改和删除时索引也要动态维护。

因此没有必要对表中所有列建立索引而应该根据实际需要建立索引。

3.1 索引的分类

如果一个表没有创建索引则数据行不按任何特定顺序存储这种结构称为堆集。SQL Server支持在表中任何列包括计算列上定义索引。SQL Server中可用的索引类型如下表所示
在这里插入图片描述

在SQL Server中除了表可以创建索引视图也可以创建索引。根据视图创建的索引称为视图索引。
虽然SQL Server中提供了许多种索引类型但通常可以简单的将索引根据其索引键值是否唯一分为唯一索引和不唯一索引。根据索引列个数可以分为单列索引和组合索引或符合索引。而最常见的索引分类是按索引的组织方式分类分为聚集索引和非聚集索引。

1. 聚集索引
聚集索引基于聚集索引键按顺序排序和存储表或视图中的数据行。聚集索引按B树索引结构实现B树索引结构支持基于聚集索引键值对行进行快速检索。在聚集索引中表中各行的物理顺序与索引键值的逻辑顺序相同。每个表只能有一个聚集索引因为数据行本身只能按一个顺序排序。

只有当表包含聚集索引时表中的数据行才按排序顺序存储。如果表具有聚集索引则该表称为聚集表。如果表没有聚集索引则其数据行存储在一个称为堆的无序结构中。

以Student表为例如果经常按照Name列查询记录则可以在Name列上创建聚集索引。
在这里插入图片描述

  • 聚集索引对表的物理数据页中的数据按索引键值列进行排序然后再重新存储到磁盘上。
  • 聚集索引的叶节点中存储的是实际的数据行。
  • 表中数据记录实际存储的顺序与聚集索引中相对应的键值的存储顺序完全相同 。

在这里插入图片描述

2. 非聚集索引
因为一个表中只能有一个聚集索引但是由于实际需要要在一个表建立多个索引则可以创建非聚集索引。非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值并且每个键值项都有指向包含该键值的数据行的指针。

从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆行定位器是指向行的指针。对于聚集表行定位器是聚集索引键。

聚集索引和非聚集索引都可以是唯一索引的。每当修改了表数据后都会自动维护索引。如果对表列定义了主键约束即PRIMARY KEY约束和唯一约束即UNIQUE约束时会自动创建索引。如果是主键约束系统默认是聚集、唯一索引。

非簇索引与簇索引一样有B树结构有两个重大差别

  • 数据行不按非簇索引键的顺序排序和存储。
  • 非簇索引的叶层不包含数据页。

一个表只能建一个簇索引当用户需要建立多个索引时就需要使用非簇索引了。一个表最多可以建249个非簇索引。
在这里插入图片描述

3.2 创建索引

创建索引时首先必须考虑一些设计准则。

1. 设计索引时应考虑的准则

  • 创建索引之前应考虑的准则
    • 了解数据库本身的特征。
    • 了解最常用的查询的特征。
    • 了解查询中使用的列的特征。
    • 确定哪些索引选项可在创建或维护索引时提高性能。
  • 设计索引时应考虑的数据库准则
    • 一个表如果建有大量索引反而会影响INSERT、UPDATE和DELETE语句的性能因为在表中的数据更改时所有索引都必须进行适当的调整。
    • 避免对经常更新的表进行过多的索引并且索引应保持较窄就是说列要尽可能少。
    • 使用多个索引可以提高更新少而数据量大的查询的性能。大量索引可以提高不修改数据的查询例如SELECT语句的性能因为查询优化器有更多的索引可供选择从而可以确定最快的访问方法。
    • 对小表进行索引可能不会产生优化效果因为查询优化器在遍历用于搜索数据的索引时花费的时间可能比执行简单的表扫描还长。因此小表的索引可能从来不用但仍必须在表中的数据更改时进行维护。
    • 视图包含聚集函数、连接或聚集函数和连接的组合时视图的索引可以显著地提升性能。

2. 创建索引

管理工具界面方式创建
在SQL Server Management Studio中用户在表设计器中打开Student表。鼠标右键单击索引/键选项进入索引/键对话框。由于Student表已经将StudentID列设置为主键所以已经存在了一个以主键为索引键的唯一索引。
在这里插入图片描述
在这里插入图片描述

如果需要根据Name列创建索引选择“添加”按钮添加一个索引/键对象。选择类型选项设置为索引选择列选项设置Name列选择是唯一的选项设置为是。关闭并保存退出即可。用户在Student表的索引对象中可以查看索引。
在这里插入图片描述
在这里插入图片描述

用户也可以使用向导创建索引。这是一种最完整的创建方式。例如在Book表中根据BookName列创建索引。右键单击索引选项选择右键菜单的新建索引的非聚集索引选项。不能选择聚集索引选项的原因是Book表设置有主键系统已经默认创建了一个聚集索引而且一个表只能创建一个聚集索引。进入新建索引对话框新建索引。在新建索引对话框中可以通过选择不同的选项来设置索引。
在这里插入图片描述
在这里插入图片描述

在常规页中在索引名称中输入新建索引名称BookNameIndex在索引类型中默认为非聚集。选择添加按钮将BookName列添加到索引键列中。其它页还可以设置索引的其它属性。关闭保存退出即可。用户在Book表的索引对象中可以查看索引。
在这里插入图片描述

命令行方式创建
在SQL Server中T-SQL提供了索引创建语句CREATE INDEX。其语法格式如下

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

CREATE INDEX语句语法说明

  • index_name是所创建的索引名。
  • UNIQUE、CLUSTERED、NONCLUSTERED是索引类型。其中UNIQUE表示唯一索引CLUSTERED表示聚集索引NONCLUSTERED表示非聚集索引而且必须先创建UNIQUE索引然后才能创建NONCLUSTERED索引。
  • ON column用于指定创建索引的列。
  • ASC表示索引按升序创建DESC表示索引按降序创建。

例已经新建一个Student2表列和Student表相同没有任何索引设置。根据StudentName列创建一个名为NameIndex的聚集索引。

USE COLLEGE
GO
CREATE UNIQUE CLUSTERED INDEX NameIndex
ON Student2(Name)

3.3 使用系统存储过程和系统视图查看索引

索引创建完毕不但可以在“对象资源管理器”中查看索引还可以使用系统存储过程和系统视图查看索引。
1. 使用sp_helpindex系统存储过程
sp_helpindex是SQL Server系统存储过程可以查看指定表或视图的索引信息。其语法格式如下
sp_helpindex table_name | view_name

例执行sp_helpindex系统存储过程查看Student表的索引信息。

USE COLLEGE
GO
sp_helpindex Student
Go

结果中显示了Student表的索引信息包括索引名称、索引列名称等。

2. 从系统视图sys.indexes中查询索引
sys.indexes是SQL Server系统视图它保存了指定数据库中的所有表或视图等对象的索引信息。

例使用连接方式将系统视图sys.indexes与系统视图sys.objects相关联获得更详细的结果集。

USE COLLEGE
GO
SELECT o.name AS '表名',i.name AS '索引名',i.type_desc AS '类型描述'
FROM sys.objects o JOIN sys.indexes i
ON o.object_id=i.object_id
GO

结果中显示了COLLEGE数据库中所有的索引信息包括表名、索引名称、索引类型。

3.4 修改索引

如果索引设置不合适可以修改。
1. 管理工具界面方式修改
索引可以在表结构设计器中修改也可以选择右键菜单选项对索引进行删除、重命名、禁用、重新生成、重新组织等。
2. 命令行方式修改
在SQL Server中T-SQL提供了索引修改语句ALTER INDEX。其语法格式如下

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD | DISABLE | REORGANIZE }

ALTER INDEX语句语法说明

  • REBUILD子句指定重新生成索引。
  • DISABLE子句指禁用索引。
  • REORGANIZE子句指重新组织索引。

例禁用前文创建的NameIndex索引。

USE COLLEGE
GO
ALTER INDEX NameIndex
ON Student2 DISABLE

例重新启用该NameIndex索引。

USE COLLEGE
GO
ALTER INDEX NameIndex
ON Student2 REBUILD

3.5 删除索引

如果索引不需要了可以删除。

1. 管理工具界面方式删除
索引可以在表结构设计器中删除也可以选择右键菜单选项对索引进行删除。

2. 命令行方式删除
在SQL Server中T-SQL提供了索引修改语句ALTER INDEX。其语法格式如下
DROP INDEX index_name

3.6 其他类型索引

除了以上常用的索引之外SQL Server其他的索引例如全文索引、空间索引、筛选索引、XML数据类型列索引等。除了全文索引之外其他类型索引不再详细介绍。

1. 全文索引
全文索引是一种特殊类型的基于标记的功能性索引它是由SQL Server全文引擎生成和维护的。生成全文索引的过程不同于生成其他类型的索引。全文引擎并非基于特定行中存储的值来构造B树结构而是基于要编制索引的文本中的各个标记来生成倒排、堆积且压缩的索引结构。
从SQL Server 2012开始全文索引与数据库引擎集成在一起而不是像SQL Server早期版本那样位于文件系统中。对于新数据库全文目录现在为不属于任何文件组的虚拟对象它仅是一个表示一组全文索引的逻辑概念。

  • 定义全文索引
    全文索引可以通过全文索引向导定义。例如给Course表定义全文索引。单击Course表选择右键菜单全文索引选项的定义全文索引子选项。进入全文索引向导初始界面。
    在这里插入图片描述
    在这里插入图片描述
    选择下一步按钮进入下一界面选择索引而且必须选择唯一索引。

在这里插入图片描述

选择下一步按钮进入下一界面选择表列。
在这里插入图片描述

选择下一步按钮进入下一界面选择更改跟踪模式。其中自动表示当基础数据发生变化全文索引将自动更新。手动表示不希望基础数据发生变化全文索引自动更新对基础数据的更改将保留下来不过如果要将更改应用到全文索引必须手动启动或安排此进程。不跟踪更改表示不希望使用基础数据的更改对全文索引进行更新。选择“下一步”按钮进入下一界面选择全文目录。如果还没有全文目录可以新建目录。
在这里插入图片描述
在这里插入图片描述
选择下一步按钮进入下一界面定义填充计划。在此可以创建全文索引和全文目录的填充计划也可以在下一步后在创建完全文索引后再创建填充计划。选择下一步按钮进入下一界面全文索引向导说明。最后选择完成按钮完成全文索引定义。

在这里插入图片描述
在这里插入图片描述

  • 查看和修改全文向导
    全文索引定义完毕可以查看或修改全文索引。选择右键菜单全文索引选项的属性子选项查看或修改全文索引的设置。用户可以在全文索引属性对话框中查看、修改等操作。
    在这里插入图片描述
    在这里插入图片描述

  • 启用、禁用和删除全文索引
    全文索引定义完毕不会立即自动启用需要手工启动。右键单击Course表选择全文索引选项的启用全文索引子选项、禁用全文索引子选项、删除全文索引子选项可以启用全文索引、禁用全文索引和删除全文索引。
    在这里插入图片描述

  • 填充全文索引
    填充全文索引实际就是更新全文索引其目的是让全文索引能够反映最新的表数据。
    SQL Server支持3种类型的填充完全填充、基于更改跟踪的自动或手动填充以及基于时间戳的增量式填充。
    完全填充方式发生在首次填充全文目录或全文索引时。启用全文索引即进行了第一次的完全填充。以后就可以使用基于更改跟踪的自动或手动填充和以及基于时间戳的增量式填充。
    基于更改跟踪的自动或手动填充也是选择快捷菜单选项设置。
    基于时间戳的增量式填充在全文索引属性对话框中选择计划选项卡。新建一个全文索引表计划。设置计划的名称、执行一次的日期时间等。
    设置完成可以修改填充类型。

  • 使用全文索引
    设置完全文索引并填充完毕之后就可以通过全文搜索来查询数据了。使用全文搜索来查询数据所用到的T-SQL语句也是SELECT语句只是在设置查询条件时和前面所说过的SELECT语句的查询条件设置有些不同。在T-SQL语言中可以在SELECT语句的WHERE子句里设置全文搜索的查询条件也可以在FROM子句里设置查询条件此时将返回结果作为FROM子句中的表格来使用。

2. 空间索引
SQL Server 2012及更高版本支持空间数据。这包括对平面空间数据类型geometry的支持该数据类型支持欧几里得坐标系统中的几何数据点、线和多边形。
3. 筛选索引
筛选索引是一种经过优化的非聚集索引尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。与全表索引相比设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。
4. XML数据类型列索引
可以对xml数据类型列创建XML索引。它们对列中XML实例的所有标记、值和路径进行索引从而提高查询性能。
XML索引分为下列类别

  • 主XML索引
  • 辅助XML索引
    XML类型列的第一个索引必须是主XML索引。使用主XML索引时支持下列类型的辅助索引PATH、VALUE和PROPERTY。根据查询类型的不同这些辅助索引可能有助于改善查询性能。

3.7 优化索引

1. 索引性能分析
SQL Server提供了多种分析索引和查询性能的方法。常用的有SHOWPLAN和STATISTICS IO两种命令。

  • SHOWPLAN
    通过在查询语句中设置SHOWPLAN选项用户可以选择是否让SQL Server显示查询计划。在查询计划中系统将显示SQL Server在执行查询的过程中连接表时所采用的每个步骤以及选择哪个索引从而可以帮助用户分析创建的索引是否被系统使用。
    设置显示查询计划的语句有
    SET SHOWPLAN_XML | SHOWPLAN_TEXT | SHOWPLAN_ALL ON
    本句执行后如果是SHOWPLAN_XMLSQL Server不执行SQL语句而返回如何在正确的XML文档中执行语句的执行计划信息。如果是SHOWPLAN_TEXTSQL Server以文本格式返回每个查询的执行计划信息。如果是SHOWPLAN_ALL输出比SHOWPLAN_TEXT更详细的信息。设置完并执行SQL后还要关闭该设置。

例使用SHOWPLAN选项查询并显示查询处理过程。

USE COLLEGE
GO
SET SHOWPLAN_XML ON
GO
SELECT Name,Sex,Birthday
FROM Student
GO
SET SHOWPLAN_XML OFF
GO

查询结果显示的是一行链接提示信息。查看链接系统显示显示本次查询处理过程的情况。

  • STATISTICS IO
    通过在查询语句中设置STATISTICS IO选项用户可以使SQL Server显示数据检索语句执行后生成的有关磁盘活动量的文本信息。

例 使用STATISTICS IO选项查询并显示查询处理过程。

USE COLLEGE
GO
SET STATISTICS IO ON
GO
SELECT CourseName,Credit
FROM Course
GO
SET STATISTICS IO OFF
GO

查看查询结果的消息显示本次查询的磁盘I/O的信息。

2. 查看索引碎片
无论何时对基础数据执行插入、更新或删除操作SQL Server都会自动维护索引。随着时间的推移这些修改可能会导致索引中的信息分散在数据库中包含碎片。当索引包含的页中的逻辑顺序与数据文件的物理顺序排序不匹配时就存在碎片。碎片过多的索引可能会减低查询性能导致应用程序相应缓慢。用户可以通过重新组织索引或重新生成索引来修复索引碎片。
选择索引对象的属性选项进入索引属性对话框。选择碎片选项可以查看索引碎片详细信息。

3. 重组索引和重建索引
重组索引是通过对页级进行物理重新排序使其与叶节点的逻辑顺序相匹配从而对表或视图的聚集索引和非聚集索引的页级别进行碎叶整理使页有序可以提高索引扫描性能。
重建索引将删除已存在的索引并创建一个新的索引。此过程中将删除碎片通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间并在连续页中对索引进行重新排序。这样可以减少获取所请求数据所需的页读取数从而提高磁盘性能。
用户可以通过选择重新生成和重新组织选项。
或使用T-SQL语句进行操作。

四、数据库关系图

以COLLEGE数据库为例除了保留主键设置以外可以先将其它约束、规则、索引都删除。
单击数据库关系图选项选择右键菜单新建数据库关系图选项。系统提示是否创建。
在这里插入图片描述
在这里插入图片描述
选择是按钮出现添加表对话框。选择添加按钮将COLLEGE数据库中所有表都添加到数据库关系图中。添加完毕进入数据库设计器。
在这里插入图片描述
在这里插入图片描述
在数据库关系图中显示添加的4个表的属性列部分即表头。用户可以根据自己的需要设置数据库关系图中表显示的形式。单击一个表根据右键菜单表视图选项可以显示标准、列名、键、仅表名等形式。默认是列名。其它的菜单选项有设置删除主键、插入列、删除列、从数据库中删除表、从关系图中删除、添加相关表、关系、索引/键、CHECK约束等选项。选择某选项如同在表结构设计器中设置一样设置效果也相同。右键单击数据库关系图空白处菜单选项有新建表、添加表等选项。选择某选项也如同在表结构设计器中设置一样设置效果也相同。
在这里插入图片描述
在这里插入图片描述

特别是在设置引用完整性时数据库关系图最直观。它将所有表都集中并可视化显示用户除了可以选择菜单选项设置之外还可以直接操作。例如Mark表的CourseID列与Course表的CourseID列存在外部约束关系。首先用鼠标选中Mark表的CourseID列按着左键不松开然后将鼠标箭头拖至Course表的CourseID列。鼠标箭头右下方出现一个+符号还有一个虚线。松开鼠标左键弹出表和列对话框。对话框已经自动设置好外部约束关系即Course表是主键表Mark表是外键表主键列和外部键列都是CourseID列。
在这里插入图片描述
在这里插入图片描述
确定退出发现在Course表与Mark表之间有一条连线。连线两头标识不一样钥匙标识指向主键所在的主键表另一头标识指向外部键所在的外键表。同样方法设置Student表和School表的外部约束关系Student表与Mark表的外部约束关系。鼠标拖拉没有顺序从主键表拖至外键表或者从外键表拖至主键表都可以。系统会自动判断哪个表是主键表哪个表是外键表。
在这里插入图片描述
在这里插入图片描述

如果外部约束关系设置不当还可以删除。单击连线选择右键菜单从数据库中删除关系选项即可删除外部约束关系。也可以选择右键菜单属性选项修改关系。
在这里插入图片描述
在这里插入图片描述
设置完毕给关系图命名存盘退出。如果关系图中的表都是空表即都没有数据记录存盘退出不会出现问题。如果关系图中的表中有数据记录系统则会按照各种设定的关系进行检查。如果有数据记录违反某个设定系统则会提示出错。

在数据库关系图中可以查看新建的关系图还可以修改、重命名、删除等操作。展开表的键选项可以查看通过数据库关系图设计器创建的键对象。与在各个表的表设计器中创建的键对象相同。
在这里插入图片描述
在这里插入图片描述

总之数据库关系图设计器将许多功能集于一身功能强大操作可视化方便快捷。

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
标签: SQL SERVER数据库
返回列表

上一篇：UPX is not available 解决办法

下一篇：鸟哥的Linux私房菜读书笔记:计算机概论

“【数据库原理与SQL Server应用】Part07——数据完整性、规则和索引” 的相关文章

肝通宵写了三万字把SQL数据库的所有命令，函数，运算符讲得明明白白讲解，内容实在丰富，建议收藏+三连好评！1年前 (2023-02-02)
TiUP部署 TiDB 数据库集群1年前 (2023-02-02)
数据库实践丨使用MTK迁移Mysql源库后主键自增列导致数据无法插入问题1年前 (2023-02-02)
谈谈对数据库中索引的理解1年前 (2023-02-02)
明清专题数据库：企业匹配官办书局距离、科举考试、商帮文化变量等1年前 (2023-02-02)
MeterSphere 数据库提取参数和断言1年前 (2023-02-02)
数据库：了解MySQL数据类型、SQL命令1年前 (2023-02-02)
JAVA校园闲置物品交易系统源码+数据库，为在校师生提供闲置物品发布、物品查询、物品交易等功能1年前 (2023-02-02)
数据库自愈1年前 (2023-02-02)
docker安装pg数据库及pg数据库基本操作1年前 (2023-02-02)

阿里云国际版