创建SQL Server索引

语法
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name   
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )   
    [ WITH <backward_compatible_index_option> [ ,...n ] ]  
    [ ON { filegroup_name | "default" } ]  
  
<object> ::=  
{  
    [ database_name. [ owner_name ] . | owner_name. ]   
    table_or_view_name  
}  
  
<backward_compatible_index_option> ::=  
{   
    PAD_INDEX  
  | FILLFACTOR = fillfactor  
  | SORT_IN_TEMPDB  
  | IGNORE_DUP_KEY  
  | STATISTICS_NORECOMPUTE   
  | DROP_EXISTING   
}
参考

UNIQUE:为表或视图创建唯一索引。 唯一索引不允许两行具有相同的索引键值。 视图的聚集索引必须唯一。如果要建唯一索引的列有重复值,必须先删除重复值。

CLUSTERED:表示指定创建的索引为聚集索引。创建索引时,键值的逻辑顺序决定表中对应行的物理顺序。 聚集索引的底层(或称叶级别)包含该表的实际数据行。

NONCLUSTERED:表示指定创建的索引为非聚集索引。创建一个指定表的逻辑排序的索引。 对于非聚集索引,数据行的物理排序独立于索引排序。

index_name:表示指定所创建的索引的名称。

database_name:表示指定的数据库的名称。

owner_name:表示指定所有者。

table:表示指定创建索引的表的名称。

view:表示指定创建索引的视图的名称。

column:索引所基于的一列或多列。 指定两个或多个列名,可为指定列的组合值创建组合索引。

[ ASC | DESC]:表示指定特定索引列的升序或降序排序方向。 默认值为 ASC。

on filegroup_name:为指定文件组创建指定索引。 如果未指定位置且表或视图尚未分区,则索引将与基础表或视图使用相同的文件组。 该文件组必须已存在。

on default:为默认文件组创建指定索引。

PAD_INDEX = {ON |OFF }:指定是否索引填充。默认为 OFF。

ON 通过指定的可用空间的百分比fillfactor应用于索引中间级别页。

OFF 或 fillfactor 未指定,考虑到中间级页上的键集,将中间级页填充到接近其容量的程度,以留出足够的空间,使之至少能够容纳索引的最大的一行。

PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用,因为 PAD_INDEX 使用由 FILLFACTOR 指定的百分比。

FILLFACTOR = fillfactor:用于指定在创建索引时,每个索引页的数据占索引页大小的百分比,fillfactor 的值为1到100。

SORT_IN_TEMPDB = {ON |OFF }:用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 默认为 OFF。

ON 用于生成索引的中间排序结果存储在tempdb。 这可能会降低仅当创建索引所需的时间tempdb位于不同的与用户数据库的磁盘集。

OFF 中间排序结果与索引存储在同一数据库中。

IGNORE_DUP_KEY = {ON |OFF }:指定在插入操作尝试向唯一索引插入重复键值时的错误响应。默认为 OFF。

ON 向唯一索引插入重复键值时将出现警告消息。 只有违反唯一性约束的行才会失败。

OFF 向唯一索引插入重复键值时将出现错误消息。 整个 INSERT 操作将被回滚。

STATISTICS_NORECOMPUTE = {ON |OFF}:用于指定过期的索引统计是否自动重新计算。 默认为 OFF。

ON 不会自动重新计算过时的统计信息。

OFF 启用统计信息自动更新功能。

DROP_EXISTING = {ON |OFF }:表示如果这个索引还在表上就 drop 掉然后在 create 一个新的。 默认为 OFF。

ON 指定要删除并重新生成现有索引,其必须具有相同名称作为参数 index_name。

OFF 指定不删除和重新生成现有的索引。 如果指定的索引名称已经存在,SQL Server 将显示一个错误。

ONLINE = {ON |OFF}:表示建立索引时是否允许正常访问,即是否对表进行锁定。默认为 OFF。

ON 它将强制表对于一般的访问保持有效,并且不创建任何阻止用户使用索引和/表的锁。

OFF 对索引操作将对表进行表锁,以便对表进行完全和有效的访问。

例子

创建唯一聚集索引

create unique clustered        --表示创建唯一聚集索引
index UQ_Clu_StuNo        --索引名称
on Student(S_StuNo)        --数据表名称(建立索引的列名)
with 
(
    pad_index=on,    --表示使用填充
    fillfactor=50,    --表示填充因子为50%
    ignore_dup_key=on,    --表示向唯一索引插入重复值会忽略重复值
    statistics_norecompute=off    --表示启用统计信息自动更新功能
)

创建唯一非聚集索引

 --创建唯一非聚集索引
create unique nonclustered        --表示创建唯一非聚集索引
index UQ_NonClu_StuNo        --索引名称
on Student(S_StuNo)        --数据表名称(建立索引的列名)
with 
(
    pad_index=on,    --表示使用填充
    fillfactor=50,    --表示填充因子为50%
    ignore_dup_key=on,    --表示向唯一索引插入重复值会忽略重复值
    statistics_norecompute=off    --表示启用统计信息自动更新功能
)


--创建聚集索引

create clustered index Clu_Index
on Student(S_StuNo)
with (drop_existing=on)    

--创建非聚集索引

create nonclustered index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on)    

--创建唯一索引

create unique index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on)

创建非聚集复合索引:

--创建非聚集复合索引
create nonclustered index Index_StuNo_SName
on Student(S_StuNo,S_Name)
with(drop_existing=on)

--创建非聚集复合索引,未指定默认为非聚集索引
create index Index_StuNo_SName
on Student(S_StuNo,S_Name)
with(drop_existing=on)

在 CREATE INDEX 语句中使用 INCLUDE 子句,可以在创建索引时定义包含的非键列(即覆盖索引),其语法结构如下:

CREATE NONCLUSTERED INDEX 索引名
ON { 表名| 视图名 } ( 列名 [ ASC | DESC ] [ ,...n ] )
INCLUDE (<列名1>, <列名2>, [,… n])


--创建非聚集覆盖索引
create nonclustered index NonClu_Index
on Student(S_StuNo)
include (S_Name,S_Height)
with(drop_existing=on)

--创建非聚集覆盖索引,未指定默认为非聚集索引
create index NonClu_Index
on Student(S_StuNo)
include (S_Name,S_Height)
with(drop_existing=on)

PS:聚集索引不能创建包含非键列的索引。

--创建非聚集筛选索引
create nonclustered index Index_StuNo_SName
on Student(S_StuNo)
where S_StuNo >= 001 and S_StuNo <= 020
with(drop_existing=on)

--创建非聚集筛选索引,未指定默认为非聚集索引
create index Index_StuNo_SName
on Student(S_StuNo)
where S_StuNo >= 001 and S_StuNo <= 020
with(drop_existing=on)

修改索引:

--修改索引语法
ALTER INDEX { 索引名| ALL }
ON <表名|视图名>
{ REBUILD  | DISABLE  | REORGANIZE }[ ; ]

REBUILD:表示指定重新生成索引。

DISABLE:表示指定将索引标记为已禁用。

REORGANIZE:表示指定将重新组织的索引叶级。

--禁用名为 NonClu_Index 的索引
alter index NonClu_Index on Student disable

删除和查看索引:

--查看指定表 Student 中的索引
exec sp_helpindex Student    

--删除指定表 Student 中名为 Index_StuNo_SName 的索引
drop index Student.Index_StuNo_SName

--检查表 Student 中索引 UQ_S_StuNo 的碎片信息
dbcc showcontig(Student,UQ_S_StuNo)

--整理 Test 数据库中表 Student 的索引 UQ_S_StuNo 的碎片
dbcc indexdefrag(Test,Student,UQ_S_StuNo)

--更新表 Student 中的全部索引的统计信息
update statistics Student

索引定义原则:

避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。

在条件表达式中经常用到的、不同值较多的列上建立索引,在不同值少的列上不要建立索引。

在频繁进行排序或分组(即进行 GROUP BY 或 ORDER BY 操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。

在选择索引键时,尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针,通过这种方式,可使一个查询必需遍历的索引页面降低到最小,此外,尽可能的使用整数做为键值,因为整数的访问速度最快。

eg:

CREATE INDEX 索引名称 ON 表名 (字段名)

DROP INDEX 索引名称 ON 表名

参考:[https://www.cnblogs.com/kkxwze/p/11307945.html]

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 205,033评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 87,725评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 151,473评论 0 338
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,846评论 1 277
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,848评论 5 368
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,691评论 1 282
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,053评论 3 399
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,700评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 42,856评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,676评论 2 323
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,787评论 1 333
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,430评论 4 321
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,034评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,990评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,218评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,174评论 2 352
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,526评论 2 343