SQL建立索引

if exists (select * From sysindexes where name='IX_CLASS_TNAME')
drop index classinfo.IX_CLASS_TNAME

create nonclustered index IX_CLASS_TNAME
on classinfo(objexplain)
with fillfactor=30
go

declare @d datetime
set @d=getdate()

SELECT objexplain as 索引 FROM classinfo(index=IX_CLASS_TNAME) WHERE objexplain like '%班%'    --指定按‘IX_CLASS_TNAME’索引查询

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

declare @d1 datetime
set @d1=getdate()

SELECT objexplain as 没用索引 FROM classinfo WHERE objexplain like '%班%' 

select [执行时间]=datediff(ms,@d1,getdate())

 

drop table test

create table test(
  id int primary key
 )
go
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (8 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (7)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (22)
go

 

select * From test

/*得出三个数据连续数中的开始不连续的数值*/
select * From (select id,(select min(id) from test where id>t.id)id2 from test t) t where id2-id>1