CODEWITHSUNDEEP
sql-server-2005
Zahid
Zahid

Reputation: 1830

what will happen to my temp table in mssql if multiple users use my application?

I am running a sql query which involves creating a temp table to do some processing on it. My question is what will happen to my temporary sql table if lets say 10K users a using my app.I am creating and inserting data into the table as follows

BEGIN TRANSACTION;
BEGIN TRY
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN   DROP TABLE #Temp; END
Select * Into   #Temp From     [Booking]  where purchaseorder='123'
-- Do some processing on temp table and 
-- save data into some other table.
 IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN DROP TABLE #Temp; END 

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Can it be that my temporary will get dropped by some other user , other than the user who created it? my application is in asp.net

Thanks

Upvotes: 0

Views: 893

Answers (2)

HLGEM
HLGEM

Reputation: 96570

Tables that start with the # are only active for the particular scope of the query, multiple users running the same query are in a different scope and thus could not affect each other. ##temp tables are global and other sessions can affect them.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

No, local temp tables (those that start with single #) are unique to your connection. There is no way for another connection to drop your table.

The fact that you're writing defensive code to try to drop such a table beforehand is rather odd.

For most simple queries, a table variable would probably be better (since those have more easily understood scope)

Upvotes: 1

Related Questions