Menu
SQL temp tables are created in the tempdb database. A local SQL Server temp table is only visible to the current session. It cannot be seen or used by processes or queries outside of the session it is declared in.
In SQL Server, local temporary tables are visible only in the current session. So if you create a local temporary table in one session, you cannot access it in other sessions. If a local temporary table created in a stored procedure, it is dropped automatically when the stored procedure is finished.
Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session.
Temporary Tables are a great feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. The temporary tables could be very useful in some cases to keep temporary data.
From CREATE TABLE:
Local temporary tables are visible only in the current session
and (more importantly):
If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, the Database Engine must be able to distinguish the tables created by the different users [sic - almost certainly this should say sessions not users]. The Database Engine does this by internally appending a numeric suffix to each local temporary table name.
Which exactly rebuts the point of whoever said that they would be shared.
Also, there's no need to DROP TABLE at the end of your procedure (from same link again):
A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished
## is used for global temporary tables - will be available to the different imports.
# is used for local temporary tables and only available in the current/inner scope.
One session cannot see another session's temporary tables. So different imports will not affect each other, regardless of whether you use temporary tables or table variables.
The exception is global temporary tables, which start with ##. Those are visible to all connections.
I just spent a few hours struggling to find out why a temporary table used in a trigger behaved strangely. Then I realised that the temporary table had the same name as a temporary table in the stored procedure used to insert the data that fired the trigger. I am now aware that this should have been obvious to me straight away, but it was a typical case of overlooking the most obvious cause when trying to find out why something did not make sense.
So it is important to remember that when a stored proc calls another stored proc or fires a trigger, then temp table names have to be unique across these in order to prevent undesired side-effects.
Furthermore - even when executing the following code in the inner stored proc, it will not function as expected. Since the outer stored proc seems to lock the temp table name.
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With