Execution Plan did not like a #temp table

[lawlerpat]

In an attempt to compare two diff. SP methods I have created a T-sql batch to replicate the SP for the purposes of looking at the execution plan. One of my tests uses a simple temporary table but Quary Analyzer will not give me a plan instead I keep getting an error however I can run the query just fine.

Thanks
Patrick

 

[SQLBill]

What's the error?

Also, how are you naming the temp table? A single # is a local temp table and a double ## is a global temp table. Your temp table could be 'out-of-scope'.

If a session creates a local temp table, no other session can use that table and it's gone (dropped) when the creating stored procedure completes. A global temp table is available to all sessions and is dropped when the creating session closes.

For more information, see the BOL, use the Index tab, enter CREATE TABLE, choose the option for Transact SQL and scroll down to TEMPORARY TABLE section. There are also issues with the table name.

-SQLBill

 

[lawlerpat]

The error is
"invalid object name '#temp1' "
I have tried both # and ##. The query runs fine but the error is returned when I try to get the execution plan
************************************************
Begin
Declare @cat_flag varchar(10)
Declare @dog_flag varchar(10)
set @cat_flag = 'yes'
set @dog_flag = 'yes'

Create table #temp1 (field1 varchar(10))

Begin
If @dog_flag = 'yes'
Insert into #temp1
select 'dog'
If @cat_flag = 'yes'
insert into #temp1
select 'cat'
end
select * from #temp1
drop table #temp1
end
*******************************************
Thanks
Patrick

 

[vadimg]

Patrick,
sql server will not come up with an execution plan for ad-hoc statements that have temp tables.
If you need to see an execution plan prior to executing, use a table variable instead:

***************************************
Begin
Declare @cat_flag varchar(10)
Declare @dog_flag varchar(10)
set @cat_flag = 'yes'
set @dog_flag = 'yes'

declare @temp1 table (field1 varchar(10))

Begin
If @dog_flag = 'yes'
Insert @temp1
select 'dog'
If @cat_flag = 'yes'
insert @temp1
select 'cat'
end
select * from @temp1
end
***************************************


i'm not sure why you cannot see it with temp tables. I assume it's because the # table doesn't exist and within the ad-hoc statements, the optimizer doesn't know what to expect -- there are no statistics or prior executions. (unlike stored procs -- there has been at least 1 run, and optimizer will reuse it if possible).

 

[lawlerpat]

Thanks,
That worked.
Unfortunately toggling the flag variable between yes and no does not affect the execution plan. I am wondering if the actual speed in a real query would be affected.

Thanks for the help.
Patrick

 

[nigelrivett]

Unfortunately table variables may not give the same plan as temp tables as temp tables give the optimiser more options.

You can't get an estimated plan with a temp table as it does not eprsist the object and so gives an error when accessing it. There is no problem with getting the actual plan used by an SP though.

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

 

[klinney]

i am trying to create a web page. It runs a stored procedure that creates a temporary table. My problem is that I want to use temporary tables because i dont want to waste space on the server BUT I cannot delete and insert rows into a temporary table because it goes out of scope.
What is the solution here? Please help.