use tempdb;
set nocount on;
go

create table dbo.t (id int identity primary key, g int, v int);
go

insert into dbo.t
 (g, v)
 select top (1000000)
  rand(checksum(newid())) * 1000,
  rand(checksum(newid())) * 100
 from
  master.dbo.spt_values a cross apply
  master.dbo.spt_values b;
go

--create index IX_t__g__v on dbo.t (g, v);
go

declare @r table (d varchar(30), m varchar(50));
declare @m varchar(50), @s1 varchar(max), @s2 varchar(max), @s3 varchar(max);

select
 @m = newid(),
 @s1 = '/*' + @m + '*/declare @c int;
select
 @c = max(x.dr) over (partition by x.g)
from  
(
 select g, dr = dense_rank() over(partition by g order by v) from dbo.t
) x
option (maxdop 1);'

insert into @r
values
 ('dense_rank() + max()', @m);

select
 @m = newid(),
 @s2 = '/*' + @m + '*/declare @c int;
select
 @c = count(x.f) over (partition by x.g)
from  
(
 select g, case when row_number() over(partition by g, v order by (select 1)) = 1 then 1 end as f from dbo.t
) x
option (maxdop 1);'

insert into @r
values
 ('row_number() + count()', @m);

select
 @m = newid(),
 @s3 = '/*' + @m + '*/declare @c int;
select
 @c = t.id + x.g
from
 dbo.t t join
 (
  select g, count(distinct v) as c from dbo.t group by g
 ) x on x.g = t.g
option (maxdop 1);'

insert into @r
values
 ('group by + count(distinct)', @m);

declare @c int = 0;
while @c < 5
 begin
  exec(@s1);
  exec(@s2);
  exec(@s3);

  select
   @c += 1;
 end;

select
 r.d as [description],
 e.[elapsed time, ms],
 e.[logical reads],
 qp.query_plan
from
 sys.dm_exec_query_stats s cross apply
 sys.dm_exec_sql_text(s.sql_handle) t cross apply
 sys.dm_exec_query_plan(s.plan_handle) qp join
 @r r on t.text like '%/*' + r.m + '*/%' cross apply
 (select s.total_elapsed_time / cast(s.execution_count as money) / 1000, s.total_logical_reads / execution_count) as e([elapsed time, ms], [logical reads])
order by
 e.[elapsed time, ms];
go

drop table dbo.t;
go