Немного теории

use tempdb;
go
 
/*Подготовка данных*/
create table dbo.Cities
(
 city_id int not null,
 city_Name varchar(50) not null,
 city_LeftBoundary as cast(cast(city_id as binary(4)) + 0x00000000 as bigint),
 city_RightBoundary as cast(cast(city_id + 1 as binary(4)) + 0x00000000 as bigint),
 first_street int null,
 last_street int null,
 constraint PK_Cities primary key clustered (city_id)
);
 
create table dbo.Streets1
(
 street_id int identity not null,
 city_id int not null references dbo.Cities (city_id),
 street_Name varchar(100) not null,
 constraint PK_Streets1 primary key clustered (street_id)
);
 
create table dbo.Streets2
(
 street_id int identity not null,
 city_id int not null references dbo.Cities (city_id),
 street_Name varchar(100) not null,
 constraint PK_Streets2 primary key clustered (street_id)
);
 
create table dbo.Streets3
(
 street_id int identity not null,
 city_id int not null references dbo.Cities (city_id),
 street_Name varchar(100) not null
);
 
create table dbo.Streets4
(
 row_id int identity not null,
 city_id int not null references dbo.Cities (city_id),
 street_Name varchar(100) not null,
 street_id as (cast(city_id as binary(4)) + 0x00000000) | cast(row_id as bigint) persisted not null,
 constraint PK_Streets4 primary key clustered (street_id)
);
 
insert into dbo.Cities
 (city_id, city_Name)
values
 (1, 'Город1'),
 (2, 'Город2'),
 (3, 'Город3'),
 (4, 'Город4');
 
insert into dbo.Streets1
 (city_id, street_Name)
 select
  t.city_id, t.street_Name
 from
  (
   select top(5000000)
    cast(rand(checksum(newid())) * 4 as int) + 1,
    cast(newid() as varchar(36))
   from
    master.dbo.spt_values a cross join
    master.dbo.spt_values b
  ) t(city_id, street_Name)
 order by
  newid();
 
update c
 set
  first_street = t.first_street,
  last_street = t.last_street
from
 (select city_id, min(street_id), max(street_id) from dbo.Streets1 group by city_id) t(city_id, first_street, last_street) join
 dbo.Cities c on c.city_id = t.city_id;
go
 
--alter table dbo.Streets1 add constraint PK_Streets1 primary key clustered (street_id);
 
set identity_insert dbo.Streets2 on;
insert into dbo.Streets2 (street_id, city_id, street_Name) select street_id, city_id, street_Name from dbo.Streets1;
set identity_insert dbo.Streets2 off;
 
set identity_insert dbo.Streets3 on;
insert into dbo.Streets3 (street_id, city_id, street_Name) select street_id, city_id, street_Name from dbo.Streets1;
set identity_insert dbo.Streets3 off;
 
set identity_insert dbo.Streets4 on;
insert into dbo.Streets4 (row_id, city_id, street_Name) select street_id, city_id, street_Name from dbo.Streets1;
set identity_insert dbo.Streets4 off;
 
create index IX_Street2__city_id on dbo.Streets2(city_id);
 
create unique clustered index IX_Streets3__city_id__street_id on dbo.Streets3 (city_id, street_id);
alter table dbo.Streets3 add constraint PK_Streets primary key nonclustered (street_id);
go
 
select * from dbo.Cities;
go
 
declare @s varchar(100), @city_id int = 1, @show_plan bit = 0, @show_io bit = 1;
 
if @show_plan = 1
 set statistics xml on;
if @show_io = 1
 set statistics io on;
set statistics time on;
 
/*
 Случай 1 - индекса по city_id в Streets нету.
 Поэтому будет применено сканирование диапазона кластерного индекса от first_street до last_street
*/
select
 @s = s.street_Name
from
 dbo.Cities c join
 dbo.Streets1 s on s.city_id = c.city_id and s.street_id between c.first_street and c.last_street
where
 c.city_id = @city_id
option
 (maxdop 1);
 
/*
 Случай 2 - индекс по city_id в Streets есть.
 Оптимизатор отказался от его использования, потому что сканирование диапазона кластерного индекса дешевле, чем сканирование диапазона индекса по city_id с последующим вытаскиванием значений street_Name из таблицы.
 В результате план выполнения аналогичен Случаю 1.
*/
select
 @s = s.street_Name
from
 dbo.Cities c join
 dbo.Streets2 s on s.city_id = c.city_id and s.street_id between c.first_street and c.last_street
where
 c.city_id = @city_id
option
 (maxdop 1);
 
/*
Аналогично Случаю 2
Показано как при наличии индекса по city_id, можно обойтись без first_street и last_street
*/
select
 @s = s.street_Name
from
 dbo.Cities c cross apply
 (select min(street_id), max(street_id) from dbo.Streets2 where city_id = c.city_id) d(first_street, last_street) join
 dbo.Streets2 s on s.city_id = c.city_id and s.street_id between d.first_street and d.last_street
where
 c.city_id = @city_id
option
 (maxdop 1);
 
/*
 Случай 4 - кластерный индекс по city_id, street_id. ПК некластерный по street_id
*/
select
 @s = s.street_Name
from
 dbo.Cities c join
 dbo.Streets3 s on s.city_id = c.city_id
where
 c.city_id = @city_id
option
 (maxdop 1);
 
/*
 Случай 5 - кластерный индекс по комбинированному столбцу из city_id и street_id
 Почти как Случай 4, но позволяет эффективно искать по ПК
*/
select
 @s = s.street_Name
from
 dbo.Cities c join
 dbo.Streets4 s on s.street_id >= c.city_LeftBoundary and s.street_id < c.city_RightBoundary
where
 c.city_id = @city_id
option
 (maxdop 1);
 
set statistics time off;
if @show_plan = 1
 set statistics xml off;
if @show_io = 1
 set statistics io off;
go
 
drop table dbo.Streets1, dbo.Streets2, dbo.Streets3, dbo.Streets4, dbo.Cities;
go