sqlserver聚集索引和非聚集索引实例

createdatabasemyIndexDemo
go
usemyIndexDemo
go
createtableABC
(
Aintnotnull,
Bchar(10),
Cvarchar(10)
)
go
insertintoABC
select1,"B","C"
union
select5,"B","C"
union
select7,"B","C"
union
select9,"B","C"
go

select*fromABC

--在ABC表上创建聚集索引
createclusteredindexCLU_ABC
onABC(A)
GO

--查看索引
sp_helpIndexABC

--插入数据
insertintoABC
values(2,"B","C")

--因为有聚集索引所以整个表的物理结构发生了变化
--此时按照该索引查询的内容为:
select*fromABCWITH(index=CLU_ABC)WHEREA>1ANDA<5

--删除索引后
DropindexABC.CLU_ABC

--查询内容物理顺序还是按照顺序的
select*fromABC


--在ABC表上创建非聚集索引
createnonclusteredindexNONCLU_ABC
onABC(A)

--查看索引
sp_helpIndexabc

--插入数据
insertintoABC
values(4,"B","C")

--因为有聚集索引所以整个表的物理结构发生了变化
--此时查询的内容为:
select*fromABCWITH(index=NONCLU_ABC)

--删除索引后
DropindexABC.NONCLU_ABC

--查询内容物理顺序是按照插入的顺序
select*fromABC