Database Reference
In-Depth Information
if (right(@List,1) <> ',')
select @List += ','
;with CTE(F, L)
as
(
select 1, charindex(',',@List)
union all
select L + 1, charindex(',',@List,L + 1)
from CTE
where charindex(',',@List,L + 1) <> 0
)
insert into @IDList(ID)
select distinct convert(int,substring(@List,F,L-F))
from CTE
option (maxrecursion 0);
return
end
Now let's run a
SELECT
statement that calculates the total amount for all orders for all customers. We will build a
comma-separated list of values from 1 to 250 and use a
dbo.ParseIDList
function to parse it. We will join the
Orders
table with the function, as shown in Listing 12-4, and then examine the execution plan, as shown in Figure
12-1
.
Listing 12-4.
Using temporary tables to optimize queries: Joining the Orders table with a multi-statement
table-valued function
declare
@List varchar(8000)
-- Populate @List with comma-separated list of integers
-- from 1 to 250
;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
,IDs(ID) as (select row_number() over (order by (select NULL)) from N4)
select @List = convert(varchar(8000),
(
select ID as [text()], ',' as [text()]
from IDs
where ID <= 250
for xml path('')
)
)
select sum(o.Amount)
from dbo.Orders o join dbo.ParseIDList(@List) l on
o.CustomerID = l.ID
