Scope of table variable and temp table
  

---

Before moving to scope, let’s look at a matrix of specific differences  of table variable and temp table:

Item	#Temp Tables	@Table Variables
Can participate in a transaction
Writes to Log File
Writes only to memory (not disk)
Can Qualify for Parallelism
Allows creation of statistics
Does not affect recompilation
Allows nonclustered indexes
Allows clustered indexes
Can perform SELECT INTO
Can access in nested stored procedures
Can define globally
Can use in user defined functions
Can insert from EXEC
Allows TRUNCATE
Allows ALTER TABLE

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.  

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

 

 

 

 

---