mssql sqlserver 快速了解聚集索引的方法分享


摘要:
下文讲述mssql sqlserver 聚集索引简介,可帮助读者快速了解聚集索引,如下所示:
实验环境:sql server 2008 R2



聚集索引简介:

聚集索引是sqlserver数据库引擎根据索引列的顺序将物理表或视图中的值按顺序依次存储,

即表中数据的顺序和聚集索引索引的顺序是一致,
由于聚集索引拥有这一特性,所以每张表或视图只能拥有一个聚集索引。

如果一张表拥有聚集索引,我们称之为聚集表,
反之称之为堆表

聚集索引常见应用场景:

1.经常用于查询和排序的列
2.需列具有唯一属性
3.范围查询的列上

聚集索引创建方法:

  CREATE TABLE [dbo].[maomao365.com](
	[keyId] [int] NULL,
	[infoA] [numeric](5, 2) NULL,
	[infoB] [decimal](5, 2) NULL
) 
 go

---创建聚集索引的方法-包含单列
 CREATE CLUSTERED INDEX [IX_maomao365.com_cluster] ON [dbo].[maomao365.com] 
(
	[keyId] ASC
)WITH (PAD_INDEX  = OFF,
 STATISTICS_NORECOMPUTE  = OFF, 
 SORT_IN_TEMPDB = OFF, 
 IGNORE_DUP_KEY = OFF, 
 DROP_EXISTING = ON, 
 ONLINE = OFF, 
 ALLOW_ROW_LOCKS  = ON, 
 ALLOW_PAGE_LOCKS  = ON)

go
---创建聚集索引的方法-包含多列
 CREATE CLUSTERED INDEX [IX_maomao365.com_cluster] ON [dbo].[maomao365.com] 
(
	[keyId] ASC,infoA DESC 
)WITH (PAD_INDEX  = OFF,
 STATISTICS_NORECOMPUTE  = OFF, 
 SORT_IN_TEMPDB = OFF, 
 IGNORE_DUP_KEY = OFF, 
 DROP_EXISTING = ON, 
 ONLINE = OFF, 
 ALLOW_ROW_LOCKS  = ON, 
 ALLOW_PAGE_LOCKS  = ON)
go