SQL语句中WHEN部分,能用索引来优化的吗?
--select datediff(mi,30,getdate()),getdate(),dateadd(mi,-10,getdate())
/*
if object_id('..f_test') is not null drop table f_test
create table f_test(
iNo int,
cName varchar(20) default '',
iLock int default 0,
dLockTime datetime default null,
cLockUser varchar(20) default ''
)
insert into f_test(iNo,cName)
select 1,'11' union all
select 2,'22' union all
select 3,'33' union all
select 4,'44'
*/
--create index f_test_iNo on f_test(iNo)
--create index f_test_cName on f_test(cName)
--create index f_test_iLock on f_test(iLock)
--create index f_test_dLockTime on f_test(dLockTime)
--create index f_test_iNo_iLock on f_test(iNo,iLock)
--create index f_test_iNo_iLock_cName on f_test(iNo,iLock,cName)
declare @iNo int,@cName varchar(20)
select @iNo=1,@cName='11'
--update f_test set iLock=1 where iNo=2
--update f_test set iLock=1,dLockTime=getdate() where iNo=3
select iNo,cName,iLock,dLockTime,cLockUser,
case when iLock=0 or (iLock>0 and (cName =@cName or dateadd(mi,-5,getdate())<dLockTime or dLockTime is null))
then 0 else 1 end as rLock
from f_test
where iNo=@iNo
上面的语句,怎么做才会优化的,上面的索引,不知道那个才会优化,WHEN部分呢?