Check if temporary table exists or not?

Question

Check if temporary table exists or not?

Viewing 15 posts - 16 through 30 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

If you use the extra parameter I posted in 2k or 2k5, it will also...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 2

Michael Valentine Jones (1/7/2008)
The parameter that Jeff used is not undocumented. It was introduced in SQL 2005 and is documented in the SQL 2005 BOL. That is why I said the checks are not valid for SQL 7.0 and 2000.

The check is perfectly valid in SQL2000. It's just undocumented there.

The problem with "IF OBJECT_ID('dbo.permanenttablename') IS NOT NULL" is that it cannot tell if the object is a table or some other kind of object. The code from my first post returns true only if they are actually tables.

Does it really help?

You cannot create a table if there is another object (not a table) with the same name.

So, you better check for all kinds of objects, not just tables.

You probably need OBJECTPROPERTY only when you need to decide what to do with existing object.

_____________
Code for TallyGenerator

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

My mistake, I didn't realize the OBJECT_ID function OBJECT_TYPE parameter was available in earlier versions of SQL Server.

I checked, and it was also available in SQL 7.0. Guess it took the BOL doc writers a while to get around to it.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 4

I think I have discovered one reason why temp tables act spooky for me: In the Temp db, the #table name is made unique with a numeric suffix. I think that suffix is to identify the PID from which the table was created (although the number is not the PID, and I have no idea how to derive one from the other.)

When you create a New Query (from SSMS) to test that the table can be deleted, that query has a new PID, and so the original temp table is never seen. (You can check this by looking at Management/Activity Monitor.)

Only a theory and I would love some additional insights from an expert into what is actually going on.

on.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

If memory serves correctly, I believe the numerics at the end of the temp table are just a timestamp datatype. Not sure why you would consider them to be "spooky" unless you just don't like the magic they provide 😀

The reason why they are appended with such numbers is so that the (apparent) same name (#MyTemp, for example) can simultaneously exist for any number of procs that would build one with zero interference or crosstalk among the tables. In other words, to each proc, it looks like it has it's own personal TempDB.

The real key to using Temp tables in procs is that they're scope sensitive to sessions/connections... there's actually no need to do an existance check at the beginning of the proc and no need to drop the table at the end except, maybe, for connection pooling (not sure there). There's two reasons why I do an existance check at the beginning of my procs... one is to make repetative runs during development easier and the other is because I'm not sure if connection pooling allows the Temp tables to persist. The reason why I do a drop at the end is, again, I'm not sure if connection pooling allows the Temp tables to persist so I drop them to save resources. I'm probably too paranoid but I plan for the worst and hope for the best 😀

The reason why I like Temp tables is because, unlike table variables, they do persist during my single development session and I can easily rerun (test) sections of code without necessarily rerunning the whole bloody thing to repopulate table variables. The other reason is because statistics are built on Temp Tables where they cannot be made to do so on table variables. Both Temp Tables and Table Variables live in memory until they get "too large" and then they actually use disk space in Temp DB as do all temporary structures.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 6

I believe "spooky" was about

SELECT * FROM SYSOBJECTS

WHERE name = '#MyTempTable'

And I have played a lot with applications used connection spooling recent years. No problems discovered.

But I do existence check at the beginning anyway. In case of recurring call.

Sometimes I even create dummy temp table inside of SP/Trigger and do existence check at the beginning with immediate RETURN if table exists to prevent any chance of recurret exections.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

I believe "spooky" was about
SELECT * FROM SYSOBJECTS
WHERE name = '#MyTempTable'

Yeah... I missed that... not exactly the right way to check for temp tables.

Thanks for the information on the connection pooling thing, Sergiy... I always wondered about that but, like you, have always included at least the initial existance check so it's never been something I spent much time on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Arun T Jayapal Say Hey Kid Points: 707 More actions · Answer 8

GilaMonster (1/7/2008)
Arun T Jayapal (1/7/2008)
if (object_id('tempdb..#tblTemp','u') > 0) print 'exists' else
print 'not exists'
...its not compiling...:ermm:
Looks fine, and runs fine for me. What's the error that you're getting?

After that if condition I tried to drop the table. It said some syntax before drop. I believe you can truncate/drop temporary tables?

EdVassie SSC Guru Points: 60445 More actions · Answer 9

Lots of posts in thi sforum, but no-one has asked Arun why he wants to know if a #temp table exists already.

There are very few situations where this information is useful. A new instance of a #temp table is created for every connection and each connection only ever knows about its own instance of the #temp table. Therefore with a normal application there is never a need to test for and delete a #temp table before creating a new instance.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Sergiy SSC Guru Points: 110206 More actions · Answer 10

EdVassie (1/10/2008)
Lots of posts in thi sforum, but no-one has asked Arun why he wants to know if a #temp table exists already.

At least 2 of us described reasons for such check.

Therefore with a normal application there is never a need to test for and delete a #temp table before creating a new instance.

You mean simple application not processing data on database layer and using database just as a dump?

_____________
Code for TallyGenerator

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 11

About "spooky"...

Within SSMS I would use New Query, select my db, and execute:

create table #test1 (PK int)

Then I wold create another query, select my db, and execute:

truncate table #test1

Msg 4701, Level 16, State 1, Line 1

Cannot find the object "#test1" because it does not exist or you do not have permissions.

Then return to my first query, and execute it to rerun the create table:

create table #test1 (PK int)

Msg 2714, Level 16, State 6, Line 1

There is already an object named '#test1' in the database.

Etc...

I had not checked for the prior existence of the #test1 as I was relying on the server to preserve it for my "session" and discard it when I was done. But in my novice mind, I had assumed that the "session" was the time I was logged on to SSMS, not the scope of a particular query. In the context of a #temp table, the documentation uses "connection" instead of "session"; so I am probably still confused as to the definition of a "connection", and how the server relates that connection to the (I have now learned thanks to this thread) timestamp suffix on the #temp table name.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Each new query window creates it's own connection or "session" to the database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Clifton Collins Default port Points: 1433 More actions · Answer 13

I have found that using the Exists function is the most reliable for varying systems' configuration for both sql2000 and sql2005. Using Is null or Is not null is subject to system configuration

Here is what I use during initialization and again, at the end to be sure everything is cleaned up:

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) Begin

DROP TABLE #tmpErrors

End

You can use this if you don't want the additional object_id function overhead:

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE [name] Like'#tmpErrors%') Begin

DROP TABLE #tmpErrors

End

Or, if you want to use the MS propaganda method. (which by the way, could be deprecated just as easily next time). [My experience for getting sql2005 compatible found that about 50% of my non-ms preferred methods still worked, while a little less that 50% of the MS preferred methods did not work and I had to re-write them anyway].

IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.Tables WHERE Table_Name Like'#tmpErrors%') Begin

DROP TABLE #tmpErrors

End

[font="Arial"]Clifton G. Collins III[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Arun T Jayapal (1/10/2008)
After that if condition I tried to drop the table. It said some syntax before drop. I believe you can truncate/drop temporary tables?

Could you post the exact code that you tried, with the drop statements in it.

You can drop and truncate temp tables. They're treated almost the same as normal tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 15

Jeff, once I thought there was at least one more exception to the rule:

"Each new query window creates it's own connection or "session" to the database."

From a multiple statements within one "connection" (query editor window), I could create a #temp table with one statement, and reference that same #temp table in subsequent statements ok.

But when I tried to call a stored procedure to created the #temp table, that table name was not visible to subsequent statements within the same query. I can't find it now, but I think that was documented somewhere in BOL, so I just sighed and took it as a given.