>>>>Thanks, Sergey. Don't know how I missed that. If I am running in debug for testing, as I do with my big SPs, and and have created the # table this way, do I still need to explicitly drop it when it has been created by a previous run and the code is running in SSMS rather than as an SP? That is does it behave like a @ table variable or is it actually aliased on the server?
>>>>
>>>Contrary to what Sergey said, you don't have to drop the table, it will be dropped automatically when the session ends. I read in T-SQL forum that it's not necessary to drop temp tables.
>>
>>I'm not sure Sergey said it was required, just that it was good practice and there I would have to agree.
>>
>>It will be dropped when the sp finishes executing, but when running the code in SSMS stepping through I have found that often the create #table will fail the second time as the table alias still exisits on the server if it hasn't been dropped explicitly. IOW, when I am debugging a big SP I have an ALTER statement with the alter etc commented out and then a section to declare the params as variables etc and a change a the end from END tor RETURN. I step through and if my debug code does not include and explicit drop for the # tables at the beginning the creates will blow up.
>>
>>@ table variables of course don't have that problem.
>>
>>As I was writing this i tried to reproduce locally on my home office setup and so far can't quite reproduce what happens all the time on my setup at Dow Jones. One big difference may be that I am running here against a local instance of SS where there I am always going across the network on the database connection.
>
>I don't have that thread handy right now, so I suggest to search in T-SQL forum or ask the question there. I am fairly certain that SQL Server MVP told that we'd rather not drop temp tables and let SQL Server handle it. I don't have time right now to search for that discussion, unfortunately, may be in a few days.

I think for me the issue was that I have a big SP with 9 # tables. In order to debug I need to step through interactively. I that case and that case only I need to explicitly drop the tables after the first pass before i can run the code a second time for testing.

In production, the same code, being called in an SP, does not explicitly drop the tables and works fine.

My understanding, I think from Denis, is that it is a matter of session. When you run the code in SSMS you are in the in the same session so the copy in tempdb is still there and a create will fail if the copy you created on the first run of the code hasn't been dropped.

Every SP, on the other hand, is in its own session for this purpose when run, so even if the copy in tempdb is still there, it actually has a different alias and the session for the current run of the SP can create a temp table with no conflict.

So you are correct, you don't need to drop it but it is also good to know why it can blow up on a create when working in SSMS.

now I routinely have code at the beginning of the SPs heavy in temp tables with :

IF EXISTS (SELECT * FROM sys.tables WHERE name LIKE '#mytemptable%')
DROP TABLE #mytemptable

for each temp table.

First time I got the error and googled I found this :

http://blog.sqlauthority.com/2009/03/29/sql-server-fix-error-msg-2714-level-16-state-6-there-is-already-an-object-named-temp-in-the-database/


Makes sense when I think about it.