use tempdb;

drop table if exists #tmp1, #tmp2;

create table #tmp1 (
      [id] int index IX clustered,
      [customerID] int index NIX,
      [name] sysname,
      [surname] sysname,
      [patronymic] sysname,
      [comment] nvarchar(4000)
);

create table #tmp2 (
      [id] int,
      [customerID] int index IX clustered,
      [name] sysname,
      [surname] sysname,
      [patronymic] sysname,
      [comment] nvarchar(4000)
);

create table #tmp3 (
      [id] int index IX clustered,
      [customerID] int,
      [name] sysname,
      [surname] sysname,
      [patronymic] sysname,
      [comment] nvarchar(4000)
);
create nonclustered index NIX on #tmp3 ([customerID]) include ([name], [surname], [patronymic], [comment]);



insert into #tmp1
select top (50000)
      row_number() over (order by 1/0),
      1+ cast(CRYPT_GEN_RANDOM(4, 0x25F18060) as bigint) % 10,
      cast(newid() as nvarchar(128)),
      cast(newid() as nvarchar(128)),
      cast(newid() as nvarchar(128)),
      replicate('A', cast(CRYPT_GEN_RANDOM(4) as bigint) % 4000)
from master.dbo.spt_values c1
    cross join master.dbo.spt_values c2;

insert into #tmp2 select * from #tmp1;
insert into #tmp3 select * from #tmp1;


set statistics io, time on;

print '------------------------TEST-----------------------'
print '#tmp1 nonclustered index seek'
select [id] from #tmp1 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp1 nonclustered index seek + keylookup' 
select * from #tmp1 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp1 clustered index seek'
select * from #tmp1 where [id]  < 40;
print '---------------------------------------------------'
go

print '#tmp2 clustered index seek specific column'
select [id] from #tmp2 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp2 clustered index seek all columns' 
select * from #tmp2 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp2 clustered index scan'
select * from #tmp2 where [id]  < 40;
print '---------------------------------------------------'
go

print '#tmp3 nonclustered index seek specific column'
select [id] from #tmp3 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp3 nonclustered index seek all columns' 
select * from #tmp3 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp3 clustered index scan'
select * from #tmp3 where [id]  < 40;
print '---------------------------------------------------'
go