Search code examples
sql-serverstored-procedurestemp-tables

SQL Server : what happens if I run the same stored procedure simultaneously that has a select into same temporary table

Any idea, what happen if I run same stored procedure (using jmeter) simultaneously,and in that stored procedure there are query

SELECT INTO #temp

Will the second stored procedure run after first stored procedure is done?

Or will the temp table be created twice (I heard there is local temp table in SQL Server)?

Sorry for the dumb question, I cannot find any answer on Google.

Thanks

Solution

  • A temporary table only exists in the scope it was created in (and "subscopes" of that scope) and only persist for the duration of that scope.

    So, for example. If you were to run the below, you wouldn't get any errors:

    EXEC sys.sp_executesql N'CREATE TABLE #temp (ID int);';
EXEC sys.sp_executesql N'CREATE TABLE #temp (ID int);';

    That's because the table, #temp would only exist within the scope of the "dynamic" statement, and would cease to as soon as it completes.

    On the other hand, something like the below would fail (This is wrong, see my edit at the bottom):

    CREATE TABLE #temp (ID int);
EXEC sys.sp_executesql N'CREATE TABLE #temp (ID int);';
DROP TABLE #temp;

    That's because the "dynamic" statement has access to the "outer" scope, and so would see that #temp already exists and generate an error.

    Running 2 statements at the same time in the same connection isn't possible, so you won't be able to call the same Stored Procedure at the same time. This means that both will have different scopes, and will therefore reference they're own object #temp, that is specific to their scope.

    You could again test this with a similar idea. Run the below, and then open a new connection and run it again (before the other is complete). You'll notice that they both succeed:

    CREATE TABLE #temp (ID int);
WAITFOR DELAY '00:30'; --Waits 30 seconds
--While the WAITFOR happens, open the another connection and run all this SQL at the same time
DROP TABLE #temp;

    Side note, Global Temporary tables do not behave the same way, but I specifically only reference temporary tables here, not global ones.

    EDIT: Appears I am wrong, sort of, on inner scopes. You actually get some very odd behaviour. Take the below:

    CREATE TABLE #temp (ID int);
INSERT INTO #temp VALUES(1);
EXEC sys.sp_executesql N'CREATE TABLE #temp (ID int); SELECT * FROM #temp;';
SELECT *
FROM #temp
DROP TABLE #temp;

    This will return 2 datasets, one with no rows, one with 1 row. If, however, you remove the CREATE in the deferred statement then you get 1 row from both:

    CREATE TABLE #temp (ID int);
INSERT INTO #temp VALUES(1);
EXEC sys.sp_executesql N'SELECT * FROM #temp;';
SELECT *
FROM #temp
DROP TABLE #temp;

    This occurs on SQL Server 2019, but I sure I recall that this behaviour isn't how it was on previous versions. Perhaps I am recalling (very) old behaviour.