use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t1 (id int identity primary key, a float);
create table dbo.t2 (id int identity primary key, t1_id int, b float);

declare @c1 int = 10000, @c2 int = 100;

insert into dbo.t1
 (a)
 select top (@c1)
  rand(checksum(newid()))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t2
 (t1_id, b)
 select
  a.id, d.b
 from
  dbo.t1 a cross apply
  (
   select top (@c2)
    rand(checksum(newid()))
   from
    master.dbo.spt_values b cross join
    master.dbo.spt_values c
  ) d(b);
go

declare @q1 nvarchar(max) = N'declare @s float;

select
 @s = a.a + b.b
from
 dbo.t1 a cross apply
 (select top (1) b from dbo.t2 where t1_id = a.id order by b) b
option
 (maxdop 1);
';

declare @q2 nvarchar(max) = N'declare @s float;

select
 @s = a.a + b.b
from
 dbo.t1 a join
 (select t1_id, b, row_number() over (partition by t1_id order by b) as rn from dbo.t2) b on b.t1_id = a.id and b.rn = 1
option
 (maxdop 1);
';

declare @qq1 nvarchar(max) = N'/*q1 with no index*/' + @q1;
declare @qq2 nvarchar(max) = N'/*q2 with no index*/' + @q2;
declare @qq3 nvarchar(max) = N'/*q1 with index*/' + @q1;
declare @qq4 nvarchar(max) = N'/*q2 with index*/' + @q2;

declare @t table (id int identity, q_label nvarchar(30));
insert into @t
values
 (N'q1 with no index'), (N'q2 with no index'), (N'q1 with index'), (N'q2 with index');

exec sp_executesql @qq1;
exec sp_executesql @qq2;

create index IX_t2__t1_id on dbo.t2 (t1_id, b);

exec sp_executesql @qq3;
exec sp_executesql @qq4;

select
 t.q_label, qp.query_plan, qs.last_elapsed_time, qs.last_worker_time, qs.last_logical_reads
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
 @t t on st.text like N'/*' + t.q_label + N'*/%'
order by
 t.id
option
 (recompile);
go

drop table dbo.t1, dbo.t2;
go