SA Developer .NET

If a stored procedure creates #Temp table that it needs to use, do you need to check if the table exists before creating it?

The #Temp table goes out of scope when the procedure completes execution and if an error occurs, the scope of that stored procedure is also lost. It seems like an overkill to check if the #Temp table exists before creating it.

 

---

A Program is only Complete when the Programmer Dies

Unless there is a chance that the temp table could have been created in a calling stored proc, I'd say no.

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

Why don't you use the TABLE variable instead of temp tables ? The one drawback that I know of is that a TABLE variable cannot be used in INSERT EXEC or SELECT INTO statement. But I have been able to work around this limitation most of the times.

TABLE variables are faster, and require no cleanup, i.e. the variable automatically deallocates when it goes out of scope in your SQL / Stored Procedure code.

Here is also an article on their performance efficiency.
 

---

- Shiva
mycodetrip.com

Table variable are usually no faster (and often a lot slower) than temp tables.

They don't allow indexes (other than a primary key) and, more importantly, they don't maintain column statistics. Without statistics, the optimiser has no way to tell how many rows are in the table variable. It will guess 1. If there are a small number of rows (<100), then this isn't much of a problem. If the row count is large, then the lack of statistics can lead to a very, very bad plan and very slow queries.

Table variables have the same rules as temp tables as to when they're in memory as opposed to disk and they are added to the tempDB system tables just like a temp table. The only performance advantage on table variables is that changes made to them are not logged.

In that article, Tom never compairs temp tables to table vars. Just to a subquery

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!