set statistics time off
set nocount on
create database GraphTest;
go
use GraphTest
go
create table Node (
  id int identity not null primary key clustered,
  x int not null default(100),
  y int not null default(100)
)

go
create table Link (
    id int identity not null primary key clustered,
    sourceNode int not null foreign key references Node(id),
    targetNode int not null foreign key references Node(id),
    layerNumber int not null default 0)
go
create table Routes(
    sourceLink int not null foreign key references Link(id),
    targetLink int not null foreign key references Link(id),
    isPrimaryRoute bit not null,
    constraint RoutePK primary key clustered(sourceLink, targetLink, isPrimaryRoute) 
)
go
create function CalcRoute(@linkID int) returns table
as return
(
  with AllRoutes(sourceLink, targetLink, isPrimaryRoute) as
  ( select sourceLink, targetLink, isPrimaryRoute from Routes where sourceLink = @linkID
    union all
    select r.sourceLink, r.targetLink, ar.isPrimaryRoute 
      from AllRoutes ar inner join Routes r on ar.targetLink = r.sourceLink 
  )
  select * from AllRoutes
)
go
declare @i int 
set @i = 0
while (@i<300)
begin
  insert into Node default values
  set @i = @i + 1
end

insert into Link(sourceNode, targetNode) 
select sourceNode.id, targetNode.id from Node as sourceNode, Node as targetNode
select count(*) as totalLinks from Link

insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 10 1 as sourceLink, id, 1 from Link where id>1 order by id; -- link #1 gets routed through 2-11;
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 100 ceiling(id/10), id, 1 from Link where id>=20 order by id; -- links #2-11 get routed through links #20-29, 30-39, 110-119, etc
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 ceiling(id/10), id, 1 from Link where id>=200 order by id; -- links #20-120 get routed through links #200-209, 210-219, ..., 1200-1209
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 id, id+1000, 1 from Link where id >= 200 order by id
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 id, id+1000, 1 from Link where id >= 1200 order by id
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 id, id+1000, 1 from Link where id >= 2200 order by id
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 id, id+1000, 1 from Link where id >= 3200 order by id
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 id, id+1000, 1 from Link where id >= 4200 order by id
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 id, id+1000, 1 from Link where id >= 5200 order by id
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 id, id+1000, 1 from Link where id >= 6200 order by id
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 id, id+1000, 1 from Link where id >= 7200 order by id
insert into Routes(sourceLink, targetLink, isPrimaryRoute)
  select top 1000 id, id+1000, 1 from Link where id >= 8200 order by id
select count(*) as totalRoutes from Routes 

checkpoint
dbcc dropcleanbuffers

set statistics time on 
select * from CalcRoute(1) 
set statistics time off

use master
drop database GraphTest