Database Reference
In-Depth Information
Temporary tables are often used to simplify large and complex queries by splitting them into smaller and simpler
ones. This helps the Query Optimizer find a better execution plan in a few ways. First, simpler queries usually have a
smaller number of possible execution plan options. This reduces the search area for Query Optimizer, and it improves
the chances of finding a better execution plan. In addition, simpler queries usually have better cardinality estimates
because the number of errors tends to grow quickly when more and more operators appear in the plan. Moreover,
statistics kept by temporary tables allow Query Optimizer to use actual cardinality data rather than relying on those,
often-incorrect, estimates.
Let's look at one such example. In the first step, shown in Listing 12-2, we create a temporary table and populate
it with data.
Listing 12-2.
Using temporary tables to optimize queries: Table creation
create table dbo.Orders
(
OrderId int not null,
CustomerId int not null,
Amount money not null,
Placeholder char(100),
constraint PK_Orders
primary key clustered(OrderId)
);
create index IDX_Orders_CustomerId on dbo.Orders(CustomerId);
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
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.Orders(OrderId, CustomerId, Amount)
select ID, ID % 250 + 1, Id % 50
from IDs
At this point, the table has 65,536 order rows evenly distributed across 250 customers. In the next step, let's
create a multi-statement table-valued function that accepts a comma-separated list of ID values as the parameter and
returns a table with individual ID values in the rows. One possible implementation of such a function is shown
in Listing 12-3.
Listing 12-3.
Using temporary tables to optimize queries: Function creation
create function dbo.ParseIDList(@List varchar(8000))
returns @IDList table
(
ID int
)
as
begin
if (IsNull(@List,'') = '')
return
