Multiple Temp Tables With Same Name Initials

Question

Hi,

I've following script to create a temporary table:

CREATE TABLE #MyTable
(
[Col1] INT,
[Col2] INT,
[Col3] INT
)
GO

The above script created the temp table successfully. Later on I added a script on top of it that first looks for the temp table, drop the table if exists, and then re-create the temp table. The drop table script is as follows:

IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#MyTable'))
DROP TABLE dbo.#MyTable
GO

The problem is when I run this script again, it prompts me that commands executed successfully. But when I go see the tempdb, I was shocked to see that there were actually 2 temp tables created with the same name, i.e., #MyTable_____XXXXXXXX1 and #MyTable_____XXXXXXXX2. It, further, is not allowing me to drop those 2 temp tables.

Due to this when I use #MyTable in other queries, i get unexpected results.

Can someone help me how to drop these 2 temp tables?

Answer 1

What you are seeing is expected behaviour.

Multiple temp tables with same name can co-exists in SQL Server.  So you can create a temp table with the same name in two different session. SQL Server internally adds a suffix to the table name that makes it unique in tempdb.  However, keep in mind that only creating session has visible to the temp table as well as any calling session,  what I mean by this if you create a temp table is proc P1 and proc P1 calls another procedure P2 -- temp table is visible in P2 also.

You may not want to drop those tables explictly, as soon as session or connection that created that temp table terminates; SQL server will destroy the temp table for you.

Answer 2

Fine, but at the moment, the visibility of temp table is not my concern. What I want to know is how I can delete those multiple temp tables, so that I can fix the issue that I am facing right now.

Answer 3

You can drop those table by issuing a drop table #tempTableName

If you are still seeing those in SSMS (management studio) make sure you refresh the view.

Answer 4

I've ran the drop table script mentioned in the initial post number of times and refreshed as well, but not success. 2 temp tables are still in the tempdb in SSMS.

Answer 5

Hello,

I would suggest you to have a look at http://msdn.microsoft.com/en-us/library/ms174979(SQL.100).aspx

In the part Temporary Tables ( around the middle of the page ), you will find some explanations about the visibility scope of the temporary tables.Usually, the temporary tables are dropped when the session is ending ( but even if you are closing your SSMS, your connection/session is not immediately closed ( problem related to the pooling ). In fact, it is put in the pool and destroyed later after a time whch may reach 15 or more minutes ( to open a connection is an heavy operation which needs many resources, it is why it is not closed immediately , just in case another connection is needing with the same characteristics )

Have a nice day.

PS : if you want more precisions, a moderator may move your thread towards the Database Engine Forum, where it will interest more people ( this forum is mainly for Data Access as connectivity problems ). For myself, it is rare that i am doing a move without having the agreement of the original poster ( simply as i am trying to respect him/her ) 

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 6

Hello,

We have no news from you since more a week.

Have you found a solution to your problem ? If yes, please, could you share it ( it would be useful for people having the same problem )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.