2

I need to run 3 consecutive queries using the same derived table... I'm using MS SQL Server 2008

Select a,b,c from categories inner join (select x,y,z from derivedTable where…) …
Select a,b,c from users inner join (select x,y,z from derivedTable where…) …
Select a,b,c from orders inner join (select x,y,z from derivedTable where…) …

Is there a way to run the 3 sql queries at the same time in a way the derived table 

(select x,y,z from derivedTable where ...)

is executed only once?

I’m using .net so... I’m wondering if I could return the 3 tables and load a dataset with the 3 tables.

Thanks

Andriy M
  • 76,112
  • 17
  • 94
  • 154
user996760
  • 33
  • 4

1 Answers1

2

You could use WITH:

Note: As pointed by @Martin WITH is evaluated several times so the correct solution is the temporary table.

WITH derivedTable (x,y,z)
as
(
    select x,y,z
      from derivedTable
      ...
)
SELECT a, b, c
  FROM users
  join derivedTable on ...
 WHERE ...
union all
SELECT a, b, c
  FROM orders
  join derivedtable on ...
 WHERE ... 
union all
 ...

or a temporary table:

select x,y,z
  into #derivedTable
  from derivedTable
  ...

SELECT a, b, c
  FROM users
  join #derivedTable on ...
 WHERE ...
union all
SELECT a, b, c
  FROM orders
  join #derivedtable on ...
 WHERE ... 
union all
 ...
DavidEG
  • 5,857
  • 3
  • 29
  • 44
  • 3
    +1 Though temporary not temporal. Just to make clear the CTE will be evaluated multiple times the `#temp` solution won't. – Martin Smith Oct 15 '11 at 12:01
  • Thank you David and Martin. So, Martin what are you saying is that using "WITH derivedTable" the querie will be executed several times? – user996760 Oct 15 '11 at 12:07
  • Thank you @Martin for the corrections. In Spanish is "temporal" :) @user996760 Yes, `with` could be evaluated several times. – DavidEG Oct 15 '11 at 12:13
  • @user996760 - Yes. The execution plan will show that the CTE is not materialised up front into a temporary table and the base tables are accessed multiple times. You can also see this from `;WITH CTE(id) AS (SELECT NEWID()) SELECT * FROM CTE c1 JOIN CTE c2 ON c1.id = c2.id` (Returns zero results) – Martin Smith Oct 15 '11 at 12:14
  • 1
    @user996760: That's right, the `derivedTable` CTE will be evaluated as many times as it is referenced in the query. One other advantage of the `#temp` solution over the CTE one (though maybe not necessarily useful in your particular case) is that you can use the temporary table in *separate* queries (within the same scope, of course), while the CTE is only accessible within the statement where it is defined. – Andriy M Oct 15 '11 at 12:19
  • More details on how non-recursive CTEs work: [1](http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx) & [2](Non Recursive Common Table Expressions). – Bogdan Sahlean Oct 15 '11 at 14:45