query plan - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

query plan

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-08-07 : 09:54:58

In a SP having multiple sql statements, query plan is generated for whole SP or for each individual sql statements.

------------------------
I think, therefore I am - Rene Descartes

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-07 : 10:47:34

I am not sure...but the query plan is generated for the whole SP and not the individual statements. Since it will be simpler to cache the whole SP plan and not individual statement plans!

I am basing my assumption on the fact that if you look at syscacheobjects table, entire SP plan is catched.

Harsh Athalye
India.
"Nothing is Impossible"

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-08-07 : 11:23:22

>> I am not sure...but the query plan is generated for the whole SP and not the individual statements.

I thought so.
But isn't it a problem for IF ELSE statements?

------------------------
I think, therefore I am - Rene Descartes

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-07 : 11:26:08

Try this - should answer the question
create proc x
as
if 1 = 1
select 1
else
select * from xxxxx
go

also
create proc x
@i int = 1
as
if @i = 1
select 1
else
select * from xxxxx
go

also
create proc x
@i int = 2
as
exec ('create table xxxxx (i int)')
if @i = 1
select 1
else
select * from xxxxx
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources