Производительность соединений по нескольким полям

use tempdb;
go
 
create table dbo.t1 (id1 int, id2 int, id3 int, v int);
create table dbo.t2 (id1 int, id2 int, id3 int, v int);
 
insert into dbo.t1
select top (1000000)
 row_number() over (order by rand()),
 row_number() over (order by rand()),
 row_number() over (order by rand()),
 1
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
 
insert into dbo.t2 with (tablock)
select * from dbo.t1;
go
 
create unique clustered index IX_t1 on dbo.t1 (id1);
create unique clustered index IX_t2 on dbo.t2 (id1);
go
 
declare @v int;
 
set statistics xml, time on;
 
select
 @v = t1.v + t2.v
from
 dbo.t1 join
 dbo.t2 on t2.id1 = t1.id1
option
 (maxdop 1, loop join);
 
select
 @v = t1.v + t2.v
from
 dbo.t1 join
 dbo.t2 on t2.id1 = t1.id1
option
 (maxdop 1, merge join);
 
select
 @v = t1.v + t2.v
from
 dbo.t1 join
 dbo.t2 on t2.id1 = t1.id1
option
 (maxdop 1, hash join);
 
set statistics xml, time off;
go
 
create unique clustered index IX_t1 on dbo.t1 (id1, id2, id3) with (drop_existing = on);
create unique clustered index IX_t2 on dbo.t2 (id1, id2, id3) with (drop_existing = on);
go
 
declare @v int;
 
set statistics xml, time on;
 
select
 @v = t1.v + t2.v
from
 dbo.t1 join
 dbo.t2 on t2.id1 = t1.id1 and t2.id2 = t1.id2 and t2.id3 = t1.id3
option
 (maxdop 1, loop join);
 
select
 @v = t1.v + t2.v
from
 dbo.t1 join
 dbo.t2 on t2.id1 = t1.id1 and t2.id2 = t1.id2 and t2.id3 = t1.id3
option
 (maxdop 1, merge join);
 
select
 @v = t1.v + t2.v
from
 dbo.t1 join
 dbo.t2 on t2.id1 = t1.id1 and t2.id2 = t1.id2 and t2.id3 = t1.id3
option
 (maxdop 1, hash join);
 
set statistics xml, time off;
go
 
drop table dbo.t1, dbo.t2;
go