WITH clause in sql server 2008

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

All Forums

General SQL Server Forums

New to SQL Server Programming

WITH clause in sql server 2008

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-04-08 : 04:23:18

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

5072 Posts

Posted - 2013-04-08 : 11:52:47

Actually it will execute twice.

CTEs are executed each time they are referenced.

You can verify this by looking at the execution plan.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-04-09 : 01:21:02

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) :)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-09 : 11:41:45


-- 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources