none
Scope of table variable and temp table

    Question

  •  

    Scope of table variable and temp table

    I have a web application that has a connection string to SQL Server with a specific database user, so anybody uses this application access database with this SQL Server user. Now, this users runs a specific stored procedure, where I have a table variable (@MyTableVar) and  a temporary table (#MyTableTmp)  used.

    Now if many users run the same web application in the internet at the same time, would these two tables create any problem just because the stored procedure would be called many times with the same user?

    Saturday, August 02, 2008 1:42 PM

Answers

  • The scope of a table variable is limited to the specific batch, while a local temporary table is limited to the specific spid.  If you create a local table variable then upon completion of the batch the variable falls out of scope.  When creating a local temporary table the table is persisited in the tempdb, but its scope is specific to the spid of the process that created it, ie. you can open up multiple query windows within sql server management studio and create a local temporary table,

    CREATE TABLE #temp(

    col1       INT

    ),

    and this will not cause any issue as the table is specific to the process id.

     

    Table variables scopes are very limited to the batch.  An example is below where the table variable is created, a value is inserted, a batch directive of GO is issued which completes the batch and ends the scope.  The final SELECT statement fails as the scope of the table variable was ended by the batch directive:

    Code Snippet

    DECLARE @temp TABLE (

    col1 INT)

    INSERT @temp

    VALUES(1)

    SELECT *

    FROM @TEMP

    GO

    SELECT *

    FROM @temp

     

     

    If you have a large amount of data that will benefit from indexing the temporary object the temporary table is preferred, as the table variable does not support this.  If you have a limited number of rows and you do not need to extend the scope to the entire spid, then the temporary table is preferred. 

     

    Hope this helps.

     

    Saturday, August 02, 2008 2:59 PM
    Moderator

All replies

  • The scope of a table variable is limited to the specific batch, while a local temporary table is limited to the specific spid.  If you create a local table variable then upon completion of the batch the variable falls out of scope.  When creating a local temporary table the table is persisited in the tempdb, but its scope is specific to the spid of the process that created it, ie. you can open up multiple query windows within sql server management studio and create a local temporary table,

    CREATE TABLE #temp(

    col1       INT

    ),

    and this will not cause any issue as the table is specific to the process id.

     

    Table variables scopes are very limited to the batch.  An example is below where the table variable is created, a value is inserted, a batch directive of GO is issued which completes the batch and ends the scope.  The final SELECT statement fails as the scope of the table variable was ended by the batch directive:

    Code Snippet

    DECLARE @temp TABLE (

    col1 INT)

    INSERT @temp

    VALUES(1)

    SELECT *

    FROM @TEMP

    GO

    SELECT *

    FROM @temp

     

     

    If you have a large amount of data that will benefit from indexing the temporary object the temporary table is preferred, as the table variable does not support this.  If you have a limited number of rows and you do not need to extend the scope to the entire spid, then the temporary table is preferred. 

     

    Hope this helps.

     

    Saturday, August 02, 2008 2:59 PM
    Moderator
  • No conflict in either case. Temporary tables (#table) are multi-user ready. Multiple users may call the same stored procedure at the same time without conflict if temporary tables are used. Upon exit from sproc, they are dropped.

    Global temporary tables (##table) are different. They can be shared among connections, but there is only one with the same name. If you use global temporary table in a stored procedure, then that will be single-user only.

    SQL Server attaches a unique session id to the temporary tables names to distinguish them.

    It may look like this in tempdb:

    #MytableTmp_______________________________________________________000009292

    Check out this demo:

    USE AdventureWorks;
     
    SELECT * into #Product from Production.Product;
    GO
     
    USE TempDB;
    SELECT TableName=[Name] FROM sys.objects WHERE Type = 'U';
    GO

    /*

    #Product____________________________________________________________________________________________________________00000000001A

    */


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    Saturday, August 02, 2008 3:01 PM
    Answerer
  • Hi,

    When we say "The scope of a table variable is limited to the specific batch, while a local temporary table is limited to the specific spid" what does exactly it mean.

    Lets say I open a query window in SSMS. and then excute a stored procdure. Stored procdure is doing nothing but creating a temp table. Now why can't I access that temp table outside the proc. Since I am in same @@SPID then why the temp table created in stored proc gets dropped once the proc finishes.

     

    Thanks

    Mahendra

     

     

    Thursday, June 09, 2011 11:38 AM
  • Hi, Jim.

     

    Your web application users can work connected with the same database user and their tables @MyTableVar and #MyTableTmp will be different for each one, the first in memory (with a better performance against the RAM of the server) and the second in tempdb database.

    The scopes are different, one for each web application user connected, as if they were executing parallel processes, no matter to be with the same database user, and both tables are local and only visible for each one.

    I use to make a "DROP TABLE #MyTableVar" into my procedures for each temporary table just when it is not needed anymore. It helps tempdb since the table is destroyed as soon as possible and it improves the performance of the temporary system database.

     

    Thanks,

    J.L.Dengra

     

     



    • Edited by J.L.Dengra Tuesday, June 14, 2011 3:56 PM
    Tuesday, June 14, 2011 3:52 PM