create database myindexdemo
go
use myindexdemo
go
create table abc
(
a int not null,
b char(10),
c varchar(10)
)
go
insert into abc
select 1,’b’,’c’
union
select 5,’b’,’c’
union
select 7,’b’,’c’
union
select 9,’b’,’c’
go
select * from abc
–在abc表上创建聚集索引
create clustered index clu_abc
on abc(a)
go
–查看索引
sp_helpindex abc
–插入数据
insert into abc
values(2,’b’,’c’)
–因为有聚集索引所以整个表的物理结构发生了变化
–此时按照该索引查询的内容为:
select * from abc with(index = clu_abc) where a>1 and a<5
–删除索引后
drop index abc.clu_abc
–查询内容物理顺序还是按照顺序的
select * from abc
–在abc表上创建非聚集索引
create nonclustered index nonclu_abc
on abc(a)
–查看索引
sp_helpindex abc
–插入数据
insert into abc
values(4,’b’,’c’)
–因为有聚集索引所以整个表的物理结构发生了变化
–此时查询的内容为:
select * from abc with(index = nonclu_abc)
–删除索引后
drop index abc.nonclu_abc
–查询内容物理顺序是按照插入的顺序
select * from abc