示例代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
USE [AdventureWorks]
GO
drop table [Sales].[SalesOrderDetail_hash]
GO
CREATE TABLE [Sales].[SalesOrderDetail_hash](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] ,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL ,
[LineTotal] numeric (38,6),
[rowguid] [uniqueidentifier] ,
[ModifiedDate] [datetime]
) ON [PRIMARY]
GO
insert into [Sales].[SalesOrderDetail_hash]
select * from [Sales].[SalesOrderDetail]
go
dbcc showcontig('[Sales].[SalesOrderDetail_hash]')
go
表: 'SalesOrderDetail_hash' (2011154210);索引 ID: 0,数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 1494
- 扫描区数..............................: 188
- 区切换次数..............................: 187
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 99.47% [187:188]
- 区扫描碎片 ..................: 1.06%
- 每页的平均可用字节数........................: 52.0
- 平均页密度(满).....................: 99.36%
2。创建一个同样的,但是有clustered index的表格。所以这是一颗B树。
-- 对于B树的操作
USE [AdventureWorks]
GO
drop table [Sales].[SalesOrderDetail_C]
GO
CREATE TABLE [Sales].[SalesOrderDetail_C](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] ,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL ,
[LineTotal] numeric (38,6),
[rowguid] [uniqueidentifier] ,
[ModifiedDate] [datetime],
CONSTRAINT [PK_SalesOrderDetailC_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into [Sales].[SalesOrderDetail_C]
select * from [Sales].[SalesOrderDetail]
go
dbcc showcontig('[Sales].[SalesOrderDetail_C]')WITH ALL_INDEXES
go
表: 'SalesOrderDetail_C' (1915153868);索引 ID: 1,数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 1494
- 扫描区数..............................: 189
- 区切换次数..............................: 188
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 98.94% [187:189]
- 逻辑扫描碎片 ..................: 0.40%
- 区扫描碎片 ..................: 0.53%
- 每页的平均可用字节数........................: 52.0
- 平均页密度(满).....................: 99.36%
3。创建一个同样的,但是Primary Key建立在nonclustered index上的表格。所以它是一个堆加一个B树。
USE [AdventureWorks]
GO
drop table [Sales].[SalesOrderDetail_N]
GO
CREATE TABLE [Sales].[SalesOrderDetail_N](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] ,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL ,
[LineTotal] numeric (38,6),
[rowguid] [uniqueidentifier] ,
[ModifiedDate] [datetime],
CONSTRAINT [PK_SalesOrderDetailN_SalesOrderID_SalesOrderDetailID] PRIMARY KEY nonclustered
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into [Sales].[SalesOrderDetail_N]
select * from [Sales].[SalesOrderDetail]
go
dbcc showcontig('[Sales].[SalesOrderDetail_N]')WITH ALL_INDEXES
go
表: 'SalesOrderDetail_N' (1947153982);索引 ID: 0,数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 1494
- 扫描区数..............................: 188
- 区切换次数..............................: 187
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 99.47% [187:188]
- 区扫描碎片 ..................: 19.15%
- 每页的平均可用字节数........................: 52.0
- 平均页密度(满).....................: 99.36%
DBCC SHOWCONTIG 正在扫描 'SalesOrderDetail_N' 表...
表: 'SalesOrderDetail_N' (1947153982);索引 ID: 2,数据库 ID: 6
已执行 LEAF 级别的扫描。
- 扫描页数................................: 285
- 扫描区数..............................: 38
- 区切换次数..............................: 37
- 每个区的平均页数........................: 7.5
- 扫描密度 [最佳计数:实际计数].......: 94.74% [36:38]
- 逻辑扫描碎片 ..................: 1.05%
- 区扫描碎片 ..................: 94.74%
- 每页的平均可用字节数........................: 8.2
- 平均页密度(满).....................: 99.90%
第3种的非聚集索引用了两块空间,堆和B树。
三种存储方式的比较:
存储方式 使用页面数量 使用区数量
没有任何索引 1494 190
有聚集索引 1494 190
没有聚集索引,有非聚集索引 1779 228
建立聚集索引并没有增加使用空间,强烈建议在大表上建立聚集索引。