Using a Temp Table in a Multi-User Environment

Question

here is the situation:

I have an SSRS report that uses SP as a dataset. SP creates a Temp table, inserts a bunch of data into it, and selects it for the SSRS report. Pretty straight forward.

Question:

If several users run a report with various parameters selected, will the temporary table created by SP create in tempdb and potentially fail to return the expected data set?

Answer 1

Most likely no. If the temporary table is defined as #temp or @temp , then you will be safe, since these temporary tables can only be accessed when creating a connection and will only last for the duration of the stored procedure. If, however, you use tables ##temp (two pound signs), while these tables are also preserved as long as the created stored procedure is executed, they are available and available for all connections to this SQL instance.

It’s good that you are not using tables ##, so you are probably safe.

Answer 2

A temporary table with a single # is a local temporary table, and its scope is limited to the session that created it, so collisions should not be a problem.