Database Reference
In-Depth Information
create unique clustered index IDX_T2_T1ID_T2ID
on dbo.T2(T1ID, T2ID);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N5)
insert into dbo.T1(T1ID)
select Num from Nums;
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N3)
insert into dbo.T2(T1ID, T2ID)
select T1ID, Num
from dbo.T1 cross join Nums;
In the next step, let's run two selects, as shown in Listing 25-18.
Listing 25-18.
Parallelism: Test queries
select count(*)
from
(
select t1.T1ID, count(*) as Cnt
from dbo.T1 t1 join dbo.T2 t2 on
t1.T1ID = t2.T1ID
group by t1.T1ID
) s
option (maxdop 1);
select count(*)
from
(
select t1.T1ID, count(*) as Cnt
from dbo.T1 t1 join dbo.T2 t2 on
t1.T1ID = t2.T1ID
group by t1.T1ID
) s
We would force a serial execution plan for the first query using
MAXDOP 1
as a query hint. The second query would
have a parallel execution plan. Figure
25-14
illustrates this scenario.
