SELECT CONVERT(CHAR(30),GETDATE(),121)
select * from t where id not in (select id from r)
SELECT CONVERT(CHAR(30),GETDATE(),121)
select * from t a where not exists(select id from r where id=a.id)
SELECT CONVERT(CHAR(30),GETDATE(),121)
测试方法:
create table t(id int identity(5,1),n int)
go
create table r(id int identity(5,1),n int)
go
insert t select 1
declare @i int
set @i=0
while @i<=20
begin
insert t select N from t
set @i=@i+1
end
go
insert r(n)
select n from t
go
SELECT CONVERT(CHAR(30),GETDATE(),121)
select * from t where id not in (select id+1 from t)
SELECT CONVERT(CHAR(30),GETDATE(),121)
select * from t a where not exists(select id from t where id+1=a.id)
SELECT CONVERT(CHAR(30),GETDATE(),121)
go
create clustered index id1 on t(id)
go
create clustered index id2 on r(id)
go
SELECT CONVERT(CHAR(30),GETDATE(),121)
select * from t where id not in (select id+1 from t)
SELECT CONVERT(CHAR(30),GETDATE(),121)
select * from t a where not exists(select id from t where id+1=a.id)
SELECT CONVERT(CHAR(30),GETDATE(),121)
go
结果:
有索引比没有索引快,但很不明显。
not in和not exists 完全一样,(应该是自动优化的)
create table t(id int identity,n int)
go
insert ta select 1
declare @i int
set @i=0
while @i<=20
begin
insert ta select a from ta
set @i=@i+1
end
select @@identity---208万
select * from t where id not in (select id+1 from t)
select * from t a where not exists(select id from t where id+1=a.id)
go
--都是35秒
create clustered index id1 on t(id)
go
select * from t where id not in (select id+1 from t)
select * from t a where not exists(select id from t where id+1=a.id)
go
--都是1分9秒