SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

mmkrishna1919
Yak Posting Veteran

India
88 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?

Thanks.

M.MURALI kRISHNA

russell
Pyro-ma-ni-yak

USA
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

LoztInSpace
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

russell
Pyro-ma-ni-yak

USA
5072 Posts

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

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

-- 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
UNION
SELECT	top 10 a, c FROM CTE

drop table #t
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000