SQLServer数据库索引语法-职坐标

SQLServer数据库索引语法

小职 2018-07-02 来源：阅读 1246 评论 0

摘要：本文主要向大家介绍了SQLServer数据库索引语法，通过具体的内容向大家展示，希望能对大家学习SQLServer数据库有所帮助。

本文主要向大家介绍了SQLServer数据库索引语法，通过具体的内容向大家展示，希望能对大家学习SQLServer数据库有所帮助。

从CREATE开始

· 　　通过显式的CREATE INDEX命令

· 　　在创建约束时作为隐含的对象

　　随约束创建的隐含索引

　　当向表中添加如下两种约束之一时，就会创建隐含索引。

1. 　　主键约束(聚集索引)

2. 　　唯一约束(唯一索引)

一、CREATE INDEX语法

　　CREATE INDEX语句所做的事情与其听上去一样-用于在指定表或视图上基于声明的列创建索引:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]

INDEX <index name> ON <table or view name>(<column name> [ASC|DESC][,...n])

INCLUDE (<column name> [,...n])

[

WITH

[PAD_INDEX = {ON | OFF}]

[[,] FILLFACTOR = <fillfactor>]

[[,] IGNORE_DUR_KEY = {ON | OFF}]

[[,] DROP_EXISTING = {ON | OFF}]

[[,] STATISTICS_NORECOMPUTE = {ON | OFF}]

[[,] SORT_IN_TEMPDB = {ON | OFF}]

[[,] ONLINE = {ON | OFF}]

[[,] ALLOW_ROW_LOCKS = {ON | OFF}]

[[,] ALLOW_PAGE_LOCKS = {ON | OFF}]

[[,] MAXDOP = <maxinum degree of parallelism>

]

[ON {<filegroup> | <partition scheme name> | DEFAULT}]

　　CREATE INDEX语句必须随表或者视图出现，并且需要声明列所在(ON)的表。下面解释个选项的作用

　　1、ASC/DESC

　　这两个选项允许为索引选择升序和降序排列顺序。默认选项为ASC，它是升序。

　　为什么需要升序和降序两个选项呢？不是反序查看索引不就行了吗？但是如果一列按升序排列，但是其他列要求按降序排列，怎么办呢？因为索引的列是存储在一起的，所以对一列反向查看索引也将倒转其他列的顺序。如果显示地声明某一列是升序，而另一列是降序，那么将直接在索引的物理数据中倒转第二列-突然间就不必改变访问数据的方式了。

　　2、INCLUDE

　　这是SQL Server2005及后续版本支持的选项。它的目的是为覆盖查询(covered queries)提供更好的支持。

　　当包含(INCLUDE)列而不是将列放在ON列表上时，SQL Server仅仅在索引的叶级上添加它们。因为在索引叶级上的每一行对应于一个数据行，所以所做的事情在本质上是将更多的原始数据包含在索引的叶级上。这样做有一个好处，因为SQL Server在有了它实际需要的内容就停止工作。SQL Server在遍历索引时没有继续访问实际的数据行就找到所需的所有数据，那么就不必再到达数据行。通过在索引中包含特定的列，可以在叶级“覆盖”利用该特定索引的查询，从而节省了与使用索引指针到达数据页相关的I/O。实际是，比如你为一个日期列创建索引，但是INCLUDE一个订单ID列。那么查找某日期的订单ID，就不必再到实际数据行了，因为在索引中就有了所需的数据。但是注意不要滥用该选项，当包含列时，将增加索引页的叶级的大小。这意味着每页中的行数将更少，因此需要更多的I/O来查看相同数量的行。结果可能是，加快了一个查询的同时可能减慢了其他的查询。要考虑对系统各个部分的影响，而不是仅仅考虑某个时候正在使用的特定查询。

　　3、WITH

　　WITH非常简单-它只是告诉SQL Server将要提供一个或者多个跟在后面的选项。

　　4、PAD_INDEX

　　该选项确定了第一次创建索引时，索引的非叶级页将有多满(用百分比表示)。不用在PAD_INDEX中声明百分比，因为将使用后面的FILLTACTOR选项指定的百分比。设置不带有FILLFACTOR选项的PAD_INDEX=ON将是没有意义的。

　　5、FILLFACTOR

　　当SQL Server第一次创建索引时，默认情况下将尽可能地将页填满，仅留两个记录的控件，可以将FILLTACTOE设置为在0-100之间的任意值。一旦索引构造完成，这个数字将表示页相对满的程度的百分比。但是在进行页拆分时，数据将仍然在两页之间对半分布-除了定期重建索引外，不能不断地控制填充百分比。

　　当需要调整页密度的时候，使用FILLTACTOR需要从以下几方面考虑：

· 　　如果是OLTP系统(经常添加和删除)，那么需要较低的FILLFACTOR。

· 　　如果是OLAP或者其他非常稳定(几乎没有添加和删除)的系统，那么需要尽可能高的FILLFACTOR。

· 　　如果事务比例中等，且有很多基于它的报表类型查询，那么可能需要中等水平的FILLFACTOR(不太低，也不太高)。

· 　　如果没有提供值，那么SQL Server将把页填充至差两行满为止，同时保证每页至少有一行。(如果行是8000字符宽，那么每页只能放一行，所以无法达到差两行满)。

　　6、IGNORE_DUP_KEY

　　IGNORE_DUP_KEY选项几乎是一种回避系统的方法。简而言之，它使得唯一约束与其应有的操作方式有些不同。

　　通常，唯一约束(或唯一索引)不允许任何种类的重复-如果事务尝试基于定义为唯一的列创建重复值，那么事务将被回滚并且拒绝。然而，一旦设置了IGNORE_DUP_KEY选项，就将得到混合的行为。仍然接收错误信息，但是错误将仅仅是一种警告-记录仍然没有被插入。

　　从IGNORE_DUP_KEY的角度看，不能会事务进行回滚(错误仍是警告错误，而不是关键错误)，但重复的行将被拒绝。

　　一句话，这个东西的态度是，重复行完全没问题，但是你要有一个该值的行存在就OK了(插入时，重复行被忽略，还是全部都不允许插入)。

　　当你创建唯一索引时，你可以指定IGNORE_DUP_KEY选项，因此本文最开始创建唯一索引的选项可以是:

　　CREATE UNIQUE NONCLUSTERED INDEX AK_Product_Name ON Production.Product ( [Name] ) WITH ( IGNORE_DUP_KEY = OFF );

　　IGNORE_DUP_KEY这个名字容易让人误会。唯一索引存在时重复的值永远不会被忽略。更准确的说，唯一索引中永远不允许存在重复键。这个选项的作用仅仅是在多列插入时有用。

　　比如，你有两个表，表A和表B，有着完全相同的结构。你可能提交如下语句给SQL Server。

　　INSERT INTO TableA SELECT * FROM TableB;

　　SQL Server会尝试将所有表B中的数据插入表A。但如果因为唯一索引拒绝表B中含有和表A相同的数据插入A怎么办？你是希望仅仅重复数据插入不成功，还是整个INSERT语句不成功？

　　这个取决于你设定的IGNORE_DUP_KEY参数，当你创建唯一索引时，通过设置设个参数可以设定当插入不成功时怎么办，设置IGNORE_DUP_KEY的两种参数解释如下:

　　IGNORE_DUP_KEY=OFF

　　整个INSERT语句都不会成功并弹出错误提示，这也是默认设置。

　　IGNORE_DUP_KEY=OFF

　　只有那些具有重复键的行不成功，其它所有的行会成功。并弹出警告信息。

　　IGNORE_DUP_KEY 选项仅仅影响插入语句。而不会被UPDATE,CREATE INDEX,ALTER INDEX所影响。这个选项也可以在设置主键和唯一约束时进行设置。

　　7、DROP_EXISTING

　　如果指定DROP_EXISTING选项，那么如果之前已经存在同名索引将在构造新索引之前被删除。当和群集索引一起使用该选项时，这个选项比简单删除并重新创建现有的索引更加有效。如果重新创建与现有索引完全匹配的索引，那么SQL Server知道它不需要涉及非群集索引，然而为了适应不同的行位置，显式删除和创建将导致重新构建所有非群集索引两次。如果使用DROP_EXISTING改变索引的结构，那么NCI只被重新构建一次，而不是两次。

　　8、STATISTICS_NORECOMPUTE

　　默认情况下，SQL Server试图自动化在表和索引上更新统计信息的过程。通过选择该选项，表示将由自己手动负责更新统计信息。为了关闭这个选项，需要运行UPDATESTATISTICS命令，但不使用NORECOMPUTE。

　　强烈建议不要使用该选项，因为查询优化器使用索引上的统计信息来指出索引对于给定的查询有多大用处。随着表中数据大量增多或减少，以及列特定值改变。索引上的统计信息会不断变化。基于这两点，可以知道不更新统计信息则查询优化器将基于过时的信息运行查询，打开自动统计信息功能意味着统计信息将周期地更新(多长时间更新一次取决于对表更新的本质和频繁程度)。相反关闭自动更新统计信息意味着信息会过时，或者需要设定计划手动运行UPDATE STATISTICS。

　　9、SORT_IN_TEMPDB

　　只有在tempdb存储在与包含新索引的数据库物理上分离的驱动器上时，该选项才有意义。为什么？

　　当SQL Server建立索引时，它必须执行多个读操作以处理各种索引构造步骤。

　　1、遍历所有的数据，构建对应于实际数据每一行的叶行。类似于实际数据和最后的索引，这些内容进入用于临时存储的页。这些中间页不是最终的索引页，而是每次排序缓冲器已满时临时存储的位置。

　　2、通过这些中间页单独运行，以将他们合并到最终叶级页。

　　3、当填充叶级页时，构建非叶级页。

　　如果没有使用SORT_IN_TEMPDB选项，那么中间页将被写入在其中存储数据库的相同物理文件中。这意味着实际数据的读操作必须与构建过程的写操作竞争。这两种情况造成磁头需要移动到一个不同的位置(读和写)。结果是磁头经常地来回移动-这会花费时间。

　　另一方面，如果使用SORT_IN_TEMPDB，那么中间页将被写入tempdb中，而不是数据库自己的文件。如果它们在单独的物理驱动器上，这意味着在索引构建的读和写操作之间没有竞争。但是要牢记，只有在tempdb位于与数据库文件分离的独立物理驱动器上，这才会有效。否则，只是名义上发生改变，而I/O竞争仍然是问题。

　　如果要使用SORT_IN_TEMPDB，那么确保在tempdb中有用于支持大文件的足够空间。

　　10、ONLINE

　　如果将这个选项设置为ON，那么它将强制表对于一般的访问保持有效，并且不创建任何阻止用户使用索引和/表的锁。默认情况下，全索引操作将获得所需的锁(最终得到表锁)，以便对表进行完全和有效的访问，然而，副作用是这将会阻止用户(这是矛盾的：一方面可能正在建立索引以使数据库更为有用，但是同时又使表变得不可用)。

　　11、ALLOW ROW/PAGE LOCKS

　　这里的ALLOW设置用于确定索引是否允许行锁和页锁。

　　12、MAXDOP

　　该选项用于为构建索引覆盖关于最大并行度的系统设置。并行度是指将有多少个进程用于一个数据库操作。有一个称为最大并行度的系统设置，允许限制每个操作中的处理器数。索引创建的MAXDOP选项允许将并行度设置为高于或者低于基本系统设置。只要合适就行。

　　13、ON

　　SQL Server允许通过使用ON选项将数据和索引单独存放。这样做有以下优点：

· 　　索引需要的空间可以分散到其他的驱动器中。

· 　　用于索引操作的I/O不会加重物理数据检索的负担。

下面简单补充下XML索引的概念。

　　XML索引是SQL Server2005新增功能。

　　除了IGNORE_DUP_KEY和ONLINE之外，XML的创建语法支持前面的CREATE语句中所看到的所有相同选项。

　　在SQL Server中，可以再类型为XML的列上创建索引。这样做的主要要求如下。

· 　　在包含需要索引的XML的表上必须具有群集索引。

· 　　在创建“辅助”索引之前，必须先在XML数据列上创建“主”XML索引。

· 　　XML索引只能放在XML类型的列上创建(而且XML索引是可以再改类型的列上创建的唯一一种索引)。

· 　　XML列必须是基表的一部分-不能在视图上创建索引。

　　1、主XML索引

　　在XML索引上创建的第一个索引必须声明为"主索引"。当创建主索引时，SQL Server创建一个新的群集索引，这个群集索引将基表的群集索引和来自任何指定的XML节点的数据组合在一起。

　　2、辅助XML索引

　　类似于指向群集索引的群集键的非群集索引，辅助XML索引以很相似的方法指向主XML索引。一旦创建了主XML索引，就只能在XML列上创建多达248个以上的XML索引。

二、修改索引

　　ALTER INDEX命令在其用来做什么方面多少有些欺骗性。截止到现在，ALTER命令总是与修改对象的定义有关。例如ALTER表以添加或禁用约束和列。ALTER INDEX是不同的-该命令与维护有关，而与结构完全不相干。如果需修改索引的组成，那么只能DROP然后CREATE索引，或者用DROP_EXISTING=ON选项CREATE并使用索引。

　　ALTER INDEX的语法类似于下面这样：

ALTER INDEX {<name of index> | ALL}ON<table or view name>

{ REBUILD[[ WITH (

　　[PAD_INDEX = {ON | OFF}]

　　　　| [[,] FILLFACTOR = <fillfactor>

　　　　| [[,] SORT_IN_TEMPDB = { ON | OFF }]

　　　　| [[,] IGNORE_DUP_KEY = { ON | OFF }]

　　　　| [[,] STATISTICS_NORECOMPUTE = { ON | OFF }]

　　　　| [[,] ONLINE = { ON| OFF }]

　　　　| [[,] ALLOW_ROW_LOCKS = { ON | OFF }]

　　　　| [[,] ALLOW_PAGE_LOCKS = { ON | OFF }]

　　　　| [[,] MAXDOP = <max degree of parallelism>

　　)]

　　|[ PARTITION = <partition number>

　　　　[ WITH (< partition rebuild index option>

　　　　[,...N])]]]

　　　　| DISABLE

　　　　| REORGANIZE

　　　　[ PARTITION = <partition number> ]

　　　　[ WITH (LOB_COMPACTION = { ON | OFF })]

　　　　| SET ([ ALLOW_ROW_LOCKS = { ON | OFF} ]

　　　　| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]

　　　　| [[,] IGNORE_DUP_KEY = { ON | OFF } ]

　　　　| [[,] STATISTICS_NORECOMPUTE = { ON | OFF }]

　　)

}[;]

　　其中一些选项与CREATE INDEX命令相同，因此这里将略过对这些选项的重新定义。除此之外，相当多的ALTER特定选项都是细节性的，且与处理碎片之类的事情有关。下面解释下参数

　　1、索引名

　　如果想维护一个特定的索引可以指定该索引，或者使用ALL表明想要维护与指定的表相关联的所有索引。

　　2、表名或视图名

　　要在其上维护的特定对象(表或视图)的名称。注意，必须是一个特定的表(可以给它提供一个列表，然后说“请处理所有这些！”)。

　　3、REBULD

　　如果使用该选项运行ALTER INDEX，那么将完全丢弃旧的索引并重新生成新的索引。结果是真正优化的索引，其中所有叶级和非叶级的页都按照定义进行了重新构建。如果是群集索引，那么也会重新组织物理数据。

　　默认情况下，页将被重新组织为差两行满。和CREATE TABLE语法一样，可以将FILLFACTOR设置为0~100之前的任何值。该值是在数据库完成重新组织后页被填满的程度(以百分比表示)。但在进行页拆分时，数据将被对半分部在两个页上-除了定期重建索引外，不得不断控制填充的百分比。
　　要小心使用该选项，一旦开始REBUILD，在完成索引重建钱，正在使用的索引实际上就没有了。依赖该索引的所有查询可能会变得异常慢。对于这类事情，首先需要在离线系统上测试，以了解整个过程将花多少时间。然后，计划在非高峰时段运行。

　　4、DISABLE

　　该选项名副其实，只是方式有些过激。如果该命令的全部作用只是为了让索引离线，直至您决定了进一步要做什么，则它是不错的选择，但它实际会把索引标记为不可用，一旦禁用了某个索引，在重新激活之前，必须重建索引(不是重新组织，而是重建)。

　　如果对表禁用了群集索引，那么也会禁用表。数据仍会保留，但在重建群集索引钱，不能被所有索引(因为他们都依赖群集索引)访问。

　　5、REORGANIZE　　
　　如果重新组织索引，就得到了比完全重建索引稍逊一点的完全优化，但这种方法可以联机进行(用户仍能使用索引)。

　　稍逊一点指的是什么？其实是REORGANIZE只在索引的叶级起作用，而不触及非叶级。这意味着未获得完全优化。但是，对于大部分的索引而言，那不是真正产生碎片的地方。

三、删除索引

　　如果不断地重新分析情况和添加索引，那么也不要忘记删除索引。记住在插入索引上的系统开销。　　

　　删除索引的语法如下：

　　DROP INDEX <table or view name>.<index name>

　　这样就可以删除索引了。

四、SQL聚集索引、非聚集索引、唯一索引、主键约束的关系

索引类型

1. 唯一索引：唯一索引不允许两行具有相同的索引值

2. 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

3. 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

4. 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

聚集索引与非聚集索引，既可以是唯一索引也可以不是

主键索引是针对主键而言的，复合索引是针对多个列作为一个索引而言的，聚集索引并没有特殊之处，因为他仍然是使用一个列做索引，加一个唯一号码作为相同时的序号，索引有时候要避免使用复合索引。

唯一索引键可以为空，主键索引不可以。

对于大多数数据库引擎，创建主键会同时创建聚集索引，同时作为主键索引。

而创建一个唯一约束列，则会自动为该列创建唯一索引，该索引同时为非聚集索引。

自动创建的索引可删除，且

索引类型：再次用汉语字典打比方，希望大家能够明白聚集索引和非聚集索引这两个概念。

唯一索引：

唯一索引不允许两行具有相同的索引值。

如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。例如，如果在stuInfo表中的学员员身份证号(stuID) 列上创建了唯一索引，则所有学员的身份证号不能重复。

提示：创建了唯一约束，将自动创建唯一索引。尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束或唯一约束。

主键索引：

在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

聚集索引（clustered index）

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。拼音字母a，b，c，d……x，y，z就是索引的逻辑顺序，而页码1，2，3……就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字（词）对应的页码也较大。如拼音“ha”对应的字(词)页码就比拼音“ba” 对应的字(词)页码靠后。

非聚集索引(Non-clustered)

如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引（nonclustered index）有更快的数据访问速度。例如，按笔画排序的索引就是非聚集索引，“1”画的字（词）对应的页码可能比“3”画的字（词）对应的页码大（靠后）。

提示：SQL Server中，一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引

对于Oracle数据库：

我们指定了ID列作为主键，Oracle数据库会自动创建一个同名的唯一索引

那么唯一键约束的情况是怎样的呢？Oracle同样自动创建了一个同名的唯一索引，而且也不允许再在此列上创建唯一索引或非唯一索引。

唯一键约束并没有非空要求。

唯一索引与唯一键约束一样对列值非空不做要求。

如果我们让主键约束或者唯一键约束失效，Oracle自动创建的唯一索引是否会受到影响？当主键约束或者唯一键约束失效时，Oracle会标记隐式创建的唯一索引为删除状态。

如果我们先创建唯一索引，再创建主键或者唯一键约束，情况又会怎样呢？实验结果表明，先创建的唯一索引不受约束失效的影响。

总结如下：

（1）主键约束和唯一键约束均会隐式创建同名的唯一索引，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除；