在SQL Server中，设计有效的索引(Index)是影响数据库性能的重要因素之一，合理的索引可以显著提高数据库的查询性能。
视图是一个虚拟表，视图中数据来源于由定义视图所引用的表，并且能够实现动态引用，即表中数据发生变化，视图中的数据随之变化。
统计信息是查询优化器进行查询优化的依据，及时更新统计信息对优化的效果至关重要。SQL Server提供了自动和手动两种方式实现对统计信息的创建及更新功能。

规划索引

索引的用途

SQL Server的索引是为了加速对表中数据检索而创建的一种分散的、物理的数据结构。
数据库中的索引的形式与图书的目录相似，键值就像目录中的标题，指针相当于页码。

索引是一个逻辑文件（无法打开查看），包含从表或视图中一个或多个列生成的键，以及映射到指定数据行的存储位置指针。

当SQL Server执行查询时，查询优化器会对可用的多种数据检索方法的成本进行估计，从中选用最有效的查询计划。

使用索引的优点：

1. 加速数据检索：索引能够以一列或多列值为基础实现快速查找数据行。
2. 优化查询：查询优化器是依赖于索引起作用的，索引能够加速连接、排序和分组等操作。
3. 强制实施行的唯一性：通过给列创建唯一索引，可以保证表中的数据不重复。

索引的类型

SQL Server 2016中常用的有聚集索引、非聚集索引和唯一索引3种类型。
注意：
聚集索引和非聚集索引，唯一索引和非唯一索引 是两种截然不同的索引类型划分方法：

• 聚集索引和非聚集索引是按照索引的存储结构划分的
• 唯一索引和非唯一索引是按照索引取值划分的。

聚集索引(聚簇索引)
在聚集索引中，索引键值的顺序与数据表中记录的物理顺序相同，即聚集索引决定了数据库表中记录行的存储顺序，每个表只能创建一个聚集索引。
聚集索引按 B 树索引结构实现，B 树索引结构支持基于聚集索引键值对行进行快速检索。

非聚集索引
非聚集索引存储的数据顺序一般与表中记录的物理顺序不同。
非聚集索引具有独立于数据行的结构，但非聚集索引的每一个键值项都含有指向该键值数据行的指针。

唯一索引
唯一索引可确保所有表中任意两行的索引列值（不包括NULL）不重复，如果在多列创建唯一索引，则该索引可以确保索引列中每个值组合都是唯一的。

在表中创建主键约束时，如果表上还没有创建聚集索引，则SQL Server将自动在创建主键约束的列或组合上创建聚集唯一索引。

创建索引

SQL Server 2016中创建索引的方法包括：

1. 使用SSMS创建索引和利用CREATE INDEX语句创建索引。
2. 在CREATE TABLE或ALTER TABLE语句中定义或修改表结构时创建索引。

创建索引之前，应该考虑到权限问题，只有表的拥有者才能在表上创建索引，每个表最多可以创建249个非聚集索引。
在创建聚集索引时还要考虑到数据库剩余空间的问题，创建聚集索引时所需要的可用空间是数据库表中数据量的120%。如果空间不足会降低性能，甚至导致索引操作失败。

利用SSMS创建索引

使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下：

• 启动SQL Server Management Studio，展开“资源管理器”窗口中teaching数据库“表”子目录。
• 选择student表并展开，右击“索引”项，如图8-1所示。在快捷菜单中选择“新建索引”“聚集索引”命令。

使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下：

• 在弹出的“新建索引”对话框中，选择“常规”选项卡，输入所引名称Idx_student，取代默认名称。如图8-2所示。其中各项说明如下。
  ① 表名：指出创建索引的表的名称，用户不可更改。
  ② 索引名称：输入所创建索引的名称，由用户设定。
  ③ 索引类型：本例在索引类型组合框中选择“聚集”。
  ④ 唯一：选中表示创建唯一性索引。本例唯一复选框为选中状态。

• 设置完成后，按照提示，单击索引键列的“添加”按钮，出现如图8-3所示的从Student表中选择列对话框。

• 在“表列”列表中选中要建立索引的一列或多列，如选择studentno列。

• 索引键列设置完毕，单击“确定”按钮，返回到“新建索引”对话框，在“索引键列”中的“排序顺序”组合框中可以选择的“升序”或“降序”。如图8-4所示。

进行必要的设置后，单击“脚本”，可以查看创建本索引Idx_student的代码。代码如下：

单击“确定”按钮，即完成了创建聚集索引的操作。此时，就可以在当前索引子目录中查看创建的索引文件。

利用CREATE INDEX命令创建索引

SQL Server 2016提供的创建索引的Transact-SQL语句是CREATE INDEX，其基本语法格式如下：

CREATE  [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
ON    { table_or_view_name }
(column [ ASC|DESC ][ ,...n ] )
  [ INCLUDE (column_name[ ,...n])]
  [ ON { filegroup_name |default  } ][ ; ]

例8.1】在 teaching 数据库中的 student 表的Email列上创建唯一索引IDX_Email。

      CREATE UNIQUE  INDEX  IDX_Email
           ON  student(Email)

执行如下插入语句：

INSERT INTO student(studentno,sname,sex,birthdate,classno,Email)
VALUES('18125121105','梁欣','女','1999-6-3',
'180802','bing@126.com ')

唯一性约束确保索引列不包含重复的值，则插入操作出现重复键值时会发出错误消息，如

【例8.2】在 teaching 数据库中 student 表的 studentno 和 classno 列上创建组合索引 IDX_sc。

IF EXISTS(SELECT name FROM sysindexes WHERE name='IDX_sc')
   DROP INDEX student.sc
GO
CREATE INDEX IDX_sc ON student(studentno,classno)

本例首先在系统表sysindexes中查找是否存在名称为IDX_sc的索引，如存在则将其删除，然后在student表上创建非聚集非唯一索引。本索引键值由列studentno和classno的值组合而成。

维护索引

在SQL Server 2016中修改索引的方法有两种：使用SQL Server Management Studio图形工具和Transact-SQL语句。

在SSMS中修改索引

使用SSMS修改索引的参考操作步骤如下：
（1）启动SQL Server Management Studio，展开“资源管理器”窗口中的“teaching数据库” “表”student 子目录。
（2）选择并展开“索引”项，右击Idx_student索引，在快捷菜单中选择“属性”命令。
（3）出现“索引属性”对话框，在各选项卡中可以修改索引的设置。在“常规”选项卡中可以添加或删除索引键列、改变键列排序。
（4）在“选项”选项卡中可实现对于在访问索引时是否使用行锁和页锁、填充因子等索引选项的修改。如选中“设置填充因子”和“填充索引”复选框，并设置填充因子为80%。
（5）在“存储”选项卡中可实现对于索引的文件组和分区属性的修改。切换至“扩展属性”选项卡，可以修改与索引相关的扩展信息。
（6）切换至“碎片”选项卡，该选项卡用于查看索引碎片数据以确定是否需要重新组织索引，如图8-9所示。
（7）修改完毕，单击“脚本”按钮，可以查看修改索引的代码。单击“确定”按钮，即可完成操作。

利用ALTER INDEX命令修改索引

SQL Server 2016提供的创建索引的Transact-SQL语句是ALTER INDEX，其语法基本格式如下：

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
    [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ] ]
    | DISABLE
    | REORGANIZE   }[ ; ]

索引碎片检测

SQL Server 2016的索引数据是随着表数据的插入、更新或删除操作而自动维护的。这些修改可能会导致索引中的信息分散在数据库中，本来可以存储在一个页中的索引却不得不存储在两个或更多的页上，这样的情况称为索引中存在碎片。
当索引包含的页中基于键值的逻辑排序与数据文件中的物理排序不匹配时，就会存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。
SQL Server可以通过重新组织索引或重新生成索引来修复索引碎片，以解决上述问题。决定使用哪种碎片整理方法的前提是检测索引碎片并分析以确定碎片程度。
SQL Server 2016提供了查看和检测有关索引碎片信息的方法，并且可以通过对检测结果的分析，确定处理碎片的最佳方法。在检测结果中，逻辑碎片的百分比属性中的取值可用来决定下一步的处理方法。
一般情况下，如该属性值<=30%，推荐采用索引重组，如果该属性值>30%，推荐采用索引重建。也可以使用sys.dm_db_index_physical_stats()函数获取索引平均碎片。
【例8.3】使用sys.dm_db_index_physical_stats()函数获取score表中所有索引的平均碎片。
SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_id(‘score’),null,null,null)
4 . 索引重组
索引重组是通过对索引的叶级页进行物理重新排序，使其与叶结点的逻辑顺序相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。
索引重组需要注意的问题。
索引在分配给它的现有页内重新组织。如果索引跨越多个文件，则将一次重新组织一个文件。
重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于设置的填充因子值。
重新组织进程使用最少的系统资源，且是自动联机执行的。索引碎片不太多时，可以重新组织索引。如果索引碎片非常多，重新生成索引则可以获得更好的结果。
使用ALTER INDEX REORGANIZE语句可实现对索引的重新组织。
【例8.4】重新组织 teaching 数据库中 student 表上的 IDX_sc 索引。
ALTER INDEX IDX_sc ON dbo.student
REORGANIZE
5 .索引重建
索引重建将删除已存在的索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。
使用ALTER INDEX REBUILD语句重建索引
其基本语法格式为：
ALTER INDEX { index_name | ALL } ON

原文地址：http://www.cnblogs.com/kingwz/p/16894779.html