1. if not exists (select * from SysUsers where UserID='1')这个括号中的SQL是查询,一步。2. if (select count(*) from SysUsers where UserID='1')=0这个括号中的SQL有聚合。 相同环境下,上边的查询和下边的聚合应该没多大差别。where后的条件是一样的,不管有没有走索引,I/O是相同的。
不知道为什么,我弄了个1000万的数据表,两者执行计划相同。create table dg(dg int) insert into dg select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9select g.dg*1000000+a.dg*100000+b.dg*10000+c.dg*1000+d.dg*100+e.dg*10+f.dg as n into dm from dg a cross join dg b cross join dg c cross join dg d cross join dg e cross join dg f cross join dg gselect count(*) from dm /* 10000000 */ /* if not exists (select * from dm where n=888) select 1if (select count(*) from dm where n=888)=0 select 1 */ 然后我上个图。
(11492 行受影响) select SYSDATETIME() if exists (select * from Question where QuestionID='9c1e40d0-65f1-4e09-b464-0002f36a6fd7') begin select SYSDATETIME() end select SYSDATETIME() if (select COUNT(*) from Question where QuestionID='9c1e40d0-65f1-4e09-b464-0002f36a6fd7')<>0 begin select SYSDATETIME() end 结果时间一样 求解?
相同环境下,上边的查询和下边的聚合应该没多大差别。where后的条件是一样的,不管有没有走索引,I/O是相同的。
insert into dg select 0
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9select g.dg*1000000+a.dg*100000+b.dg*10000+c.dg*1000+d.dg*100+e.dg*10+f.dg as n into dm
from dg a
cross join dg b
cross join dg c
cross join dg d
cross join dg e
cross join dg f
cross join dg gselect count(*) from dm
/*
10000000
*/
/*
if not exists (select * from dm where n=888)
select 1if (select count(*) from dm where n=888)=0
select 1
*/
然后我上个图。
select SYSDATETIME()
if exists (select * from Question where QuestionID='9c1e40d0-65f1-4e09-b464-0002f36a6fd7')
begin
select SYSDATETIME()
end select SYSDATETIME()
if (select COUNT(*) from Question where QuestionID='9c1e40d0-65f1-4e09-b464-0002f36a6fd7')<>0
begin
select SYSDATETIME()
end 结果时间一样 求解?