SQL Server Performance Forum – Threads Archive
Temporary TableI am using stored procedure to create a temporary table.
I am wonder is the table unique because the temporary table will be accessed by multiple user concurrently….the table will contain different info for different user…
is there any way i can make sure the temporary table is unique or not?
The table is unique for the session.
Try this create table #temp (i int)
select * from tempdb.dbo.sysobjects where type = ‘U’ and name like ‘#temp%’
drop table #temp And you will see the different names created in different sessions Bambola.
Temp table is one method and also other work around is use @@spid and store
in a table and based on the @@spid u can get the data.
True Bamabola, SQL Server attaches some postfixes to the emporary table names while creating. This ensures that multiple users can create the temp table with the same name simultaneously. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Agree with both. Be carefully with temp tables. If temp tables are small there is no problem, but if very big you may be will have performance problems. Luis
Just would like to add that there are local (#table_name) and global (##table_name) temporary tables. A local exists only for the duration of a user session or the procedure that created it. When the user logs off or the procedure that created it is done, the local temporary table is lost. Multiple users can’t share a local temporary table because it is local to one user session. A global temporary table also acts just like a local temporary table however, multiple users can access a global temporary table. Jon M