with c1 As (

select 1 As '1' , 2  As '2' , 3 As '3'
) 

, c2 As (

select 4  As '4', 5  As '5',  6 As '6'
union all
select 1 , 2 ,3 from c1  -- >>>>>  select from c1 here 


) select * from c2 
  union all
  select * from c1 -- >>>>>> and select from c1 here 

According to the query above , I try to reuse the subquery by put  the subquery into 'with cte'  name (c1)  then i select this  2 times .

if i do this way , how many time this subquery (c1) excute ?

if 1 time then this is the right way to reuse this subquery .

if 2 times  , it is not then what should i do to reuse this subquery ?

It will be executed twice. If you want to execute it only once, you may run the result into a temp table and then use the temp table instead. I found in some cases that temp table outperforms the direct cte query approaches.

One way to find out what is going on is to run with

SET STATISTICS IO ON 

before running the

from the example, the C1 CTE is executed twice. if you change the 'Union ALL' to 'Union' in the last select statement, you will get the values of C1 only once. The output and your 'reusing' the subquery statement doesnot match. Actually, you are reusing the CTE. Can you please explain more on your question?

Hi Hemkoe,

You query is no difference from the below one with derived tables.

select * from (
select 4  As '4', 5  As '5',  6 As '6'
union all
select 1 , 2 ,3 from (select 1 As '1' , 2  As '2' , 3 As '3') c1
) c2
union all
select 1 , 2 ,3 from (select 1 As '1' , 2  As '2' , 3 As '3') c1

SQL Server query optimizer might optimize for the query with repeatable derived tables but I don't think they're reused. If you need to refer to something more than once, use a VIEW or temp table can somewhat work to "reuse".

If you have any question, feel free to let me