Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 WITH clause in sql server 2008
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

95 Posts

Posted - 04/08/2013 :  04:23:18  Show Profile  Reply with Quote
Hi All,

with cte as
select custid from customer
select *
from order o,cte c
where o.custid=c.custid

union all

select *
from stock s,cte c
where s.custid=c.custid

My question is:
In the above scenario the select statement to form CTE will execute only once or
it will reevaluate every time while we use in subsequent queries?




5072 Posts

Posted - 04/08/2013 :  11:52:47  Show Profile  Visit russell's Homepage  Reply with Quote
Actually it will execute twice.

CTEs are executed each time they are referenced.

You can verify this by looking at the execution plan.

Edited by - russell on 04/08/2013 11:53:35
Go to Top of Page

Aged Yak Warrior

940 Posts

Posted - 04/09/2013 :  01:21:02  Show Profile  Reply with Quote
Indeed it is called more than once. Now your bonus question:
Are the results consistent on each invocation? :)
a) Yes always
b) Not necessarily
c) Depends on your isolation model.

Which is it and why (with references) :)
Go to Top of Page


5072 Posts

Posted - 04/09/2013 :  11:41:45  Show Profile  Visit russell's Homepage  Reply with Quote

-- create temp table
Create Table #t (a int identity(1,1), b int default 0);

-- populate it
insert #t (b) values (default);
go 40

-- build cte
With CTE as (
	SELECT	a, b, newid() c
	FROM	#t

-- union would filter distinct, so we should
--  have 10 rows if the cte is the same every time
-- in fact it returns 20 because CTE is re-evaaluated
--  with each reference
SELECT	top 10 a, c FROM CTE
SELECT	top 10 a, c FROM CTE

drop table #t
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000