Разъяснение GUID

use tempdb;
go
 
create table dbo.t1 (i int not null, bi bigint not null, ui uniqueidentifier not null);
create table dbo.t2 (i int not null, bi bigint not null, ui uniqueidentifier not null);
 
insert into dbo.t1
 (i, bi, ui)
 select top (500000)
  row_number() over (order by 1/0), row_number() over (order by 1/0), newid()
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
 
insert into dbo.t2 with (tablock)
 (i, bi, ui)
 select
  i, bi, ui
 from
  dbo.t1;
 
create unique index IX_t1__i on dbo.t1 (i);
create unique index IX_t1__bi on dbo.t1 (bi);
create unique index IX_t1__ui on dbo.t1 (ui);
 
create unique index IX_t2__i on dbo.t2 (i);
create unique index IX_t2__bi on dbo.t2 (bi);
create unique index IX_t2__ui on dbo.t2 (ui);
go
 
declare
 @q1 nvarchar(max) = N'/*q1*/declare @c int; select @c = count(*) from dbo.t1 a join dbo.t2 b on b.i = a.i option (maxdop 1, hash join)',
 @q2 nvarchar(max) = N'/*q2*/declare @c int; select @c = count(*) from dbo.t1 a join dbo.t2 b on b.bi = a.bi option (maxdop 1, hash join)',
 @q3 nvarchar(max) = N'/*q3*/declare @c int; select @c = count(*) from dbo.t1 a join dbo.t2 b on b.ui = a.ui option (maxdop 1, hash join)',
 @q4 nvarchar(max) = N'/*q4*/declare @c int; select @c = count(*) from dbo.t1 a join dbo.t2 b on b.i = a.i option (maxdop 1, loop join)',
 @q5 nvarchar(max) = N'/*q5*/declare @c int; select @c = count(*) from dbo.t1 a join dbo.t2 b on b.bi = a.bi option (maxdop 1, loop join)',
 @q6 nvarchar(max) = N'/*q6*/declare @c int; select @c = count(*) from dbo.t1 a join dbo.t2 b on b.ui = a.ui option (maxdop 1, loop join)',
 @q7 nvarchar(max) = N'/*q7*/declare @c int; select @c = count(*) from dbo.t1 a join dbo.t2 b on b.ui = a.ui option (maxdop 1, merge join)',
 @q8 nvarchar(max) = N'/*q8*/declare @c int; select @c = count(*) from dbo.t1 a join dbo.t2 b on b.bi = a.bi option (maxdop 1, merge join)',
 @q9 nvarchar(max) = N'/*q9*/declare @c int; select @c = count(*) from dbo.t1 a join dbo.t2 b on b.ui = a.ui option (maxdop 1, merge join)';
 
declare @qd table (id int, query_description nvarchar(50), query_mask nvarchar(30))
insert into @qd
values
 (1, N'Hash join by int', N'/*q1*/%'),
 (2, N'Hash join by bigint', N'/*q2*/%'),
 (3, N'Hash join by uniqueidentifier', N'/*q3*/%'),
 (4, N'Loop join by int', N'/*q4*/%'),
 (5, N'Loop join by bigint', N'/*q5*/%'),
 (6, N'Loop join by uniqueidentifier', N'/*q6*/%'),
 (7, N'Merge join by int', N'/*q7*/%'),
 (8, N'Merge join by bigint', N'/*q8*/%'),
 (9, N'Merge join by uniqueidentifier', N'/*q9*/%');
 
declare @c int = 5;
 
while @c > 0
 begin
  exec sys.sp_executesql @q1;
  exec sys.sp_executesql @q2;
  exec sys.sp_executesql @q3;
  exec sys.sp_executesql @q4;
  exec sys.sp_executesql @q5;
  exec sys.sp_executesql @q6;
  exec sys.sp_executesql @q7;
  exec sys.sp_executesql @q8;
  exec sys.sp_executesql @q9;
 
  set @c -= 1;
 end;
 
select
 qd.query_description, qs.execution_count, qs.total_elapsed_time,
 cast(cast(qs.total_elapsed_time as float) / qs.execution_count  as numeric(18,2)) as avg_elapsed_time,
 cast(cast(qs.total_logical_reads as float) / qs.execution_count  as numeric(18,2)) as avg_logical_reads,
 qp.query_plan
from
 sys.dm_exec_query_stats qs cross apply
 sys.dm_exec_query_plan(qs.plan_handle) qp cross apply
 sys.dm_exec_sql_text(qs.sql_handle) st join
 @qd qd on st.text like qd.query_mask
order by
 qd.id
option
 (recompile);
go
 
drop table dbo.t1, dbo.t2;
go