SqlTutorial-1 : Lesson-8 : Class-1 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Temporary Tables(Local-# ,Global-## )

S

QL Server supports a greate feature called temporary table, that lets you to hold the result-set of a query for future process. It has greate implicit maintenance support. Temporary tables are very similar to normal Db-Tables except they follows some differerent naming notations and behavioral aspects. There are two types of Temporary tables based on visibility called LOCAL and GLOBAL temporary tables. In this session,  you will learn how they are different from each other, and how they are different from normal Db-Tables and Table variable(@Table). Finally, you will determine how to create, populate and use these temporary tables.

I N D E X

1. What are temporary tables.
2. Difference between Temp-Table Vs Persistent Tables (Normal Db-Tables)
3. Types of temporary table : LOCAL Vs GLOBAL
4. When which one applicable
5. Creating temporary tables
6. Can I delete temporary table explicitly?
7. Alternative-way: @Table Variable
8. Differncen between @Table Vs #Table.
9. Conclusions

1. What are temporary tables?

Temporary tables are auto-droppable tables. They exists as long as SQL server is run and once Sql-Server restarts, they implicitly get deleted permanently. The following are some aspects of temporary tables.

• Convensional aspects:

  • As naming convensions, they are prefixed by either #(local) or ## (Global).
  • As location convensions, temporary tables are located at system-database called "tempDb".  They cann't be located at your database.

• Behavioral aspects:

  • There are two types of temporary table: Local (#table_name) or Global(##table_name) 
  • Temporary tables neither be parent table nor child table,  i.e., you cannot use foriegn key with them. They are lack of referential integrity enforcement. The reason behind this is, once server restarts temporary tables truncated automatically, and referring tables cann't discover them.
  • Like normal Db-tables, it allows all CRUD operations such as SELECT, INSERT, DELETE, UPDATE.
  • Like normal Tables, transaction logging, and  indexing are compatible.

Go to Top

---

2. Temporary Table vs Persistent Tables:

As just I told, temporary tables are very similar to persistent Tables.

Similiarities :

• DML operations: Both supports all CRUD operations much similar way.
• DDL operations: Both supports table ALTration and DROPing.
• INDEXing : Both allows you do indexing for speed up searching process.
• TRANSACTION Logging & Locking: Both allows you do transaction logging & locking so that you can do roll-back and commit operations over them. 
• Both are located same location tempdb (see image). 

Differences:

• Auto-droppability:  Temporary tables are auto-dropable tables, they are removed automatically when Server restarts or your connection with Server re-established. Whereas Persistent tables are survivable as long as you delete them explicitly.
• Data-Visiblity or Sharing: Data of persistent table is visible (accessible) to all logged users.e.g, two users can simultaneously access the same database.Whereas temp-table, even global temp table, is unable to access one's user data to another.
• Trigger-compatibility: Triggers cannot be created on temp tables. i..e., triggers cannot be fired in response to modifications on temporary tables. Whereas triggers are compatible with both Persistent tables and Views. However, you can use temporary tables within the Trigger implementations.

Persistent Table means non-temporary tables or simply Db-tables

Go to Top

---

3. LOCAL(#) Vs GLOBAL(##) Temporary Tables

There are two tyeps of Temporary tables: LOCAL and GLOBAL.

Local Temp-table (#): Local temp-table is visible to current scope i.e, it is visible within same stored procedure, trigger, functions where it is defined. It is invisible (in-accessible) across multiple SPs, UDF etc, even defined in same connection(session).

Global Temp-table (##): Global temp-table is visible to all users(connections) but its data not. i.e., Global temp-table structure is shared across all users(connections/sessions) but its data is alive as long as the connection is open. Once connection is closed the data of even same user is inaccessible.
Global temp-table is deleted when either Server restarts or all connections to the server have been closed. The primary difference between them is Visibility.

Similiarity

• Both have same functionality and behavior (same syntax and operations). And both allows ALTER TABLE, SELECT INTO, CREATE INDEX, ROLLBACK operations. 
• Both are used to store intermediate results while processing a set of data. 

Differences:

• Naming notation: Local temporary table name begins with single # whereas global with ##. 
• Visibility: Local temp-tables are visible to only current scope and they discarded when access goe to of of scope. Whereas Global temporary tables are visisble to all connections (users). i.e., local temp-tables are invisible to multiple users whereas Global temporary tables are visible to multiple users (connection / sessions) as long as the connection that created it is still active.

• One of the mis-understanding with Global temp-table is that it is accessible across all session/connection. Meaning of this is that, obviously you can share Global temp-table structure across all users but not data. Still two users can't share their data with each other. Both are isolated by current session only.Once connection is close, the user data is no more. 
• In contrast to this local temp-tables neither structure nor data is accessible across users. Niether Global nor Local Temp tables data is accessible across multiple connection/user.i.e., no two users/connections can share same data stored in temp-table. However, the global temp-table structure can be shared across multiple connections/users.

Go to Top

---

4. When which one applicable

There are a couple of scienarios where we favour temporary tables.

• Holding and manipulating bulk data temporarily in array-like style:   Where we are working with large number of row manipulations in stored Procedures, we keep intermediate processed data in temp-table for futher accessing. E.g., DataSource paging is a good example;
• Joining Bulk data of complex result sets: You can store the result-sets of complex business query and then joine with each other. This approach, gives two advantages: tracing db-query, and readability.

For samll-sized of data use table variable @Table instead of temp-tables.

Go to Top

---

5. Creating temporary tables

Like persistent tables, you can create temporary table with CREATE TABLE clause.

Examples

1. Creating a new simple local Temporary Table.

   Sqlcmd> Create Table #myLocalTemp1(c1 int )
   Go
   Sqlcmd> Insert into #myLocalTemp1 values (100)
   Sqlcmd> Insert into #myLocalTemp1 values (200)
   Sqlcmd> Insert into #myLocalTemp1 values (300)
   Go
   Sqlcmd> select * from #myLocalTemp1
   Go
   Sqlcmd> DROP TABLE #myLocalTemp1   //Explicitly dropping temp table within current session.
   
   

2. Creating temporary table using existing table. (usage of SELECT INTO)

   SELECT e_id, e_name,salary INTO #MyLocalTemp2
   FROM Billings

1. Don’t use Visual Studio IDE for inserting values into the temporary tables, because VS IDE recreates session on every query execution and temporary tables get dropped on every query processing. Instead use sqlcmd.
2. Temporary tables are mainly used in stored procedures to hold temporary table data.

Where local & global temp-tables are located: 
Both are located at tempdb system databases. And each local temp-table is identified by a unique identifier. following figure shows this;

Go to Top

---

6. Can I delete temporary table explicitly?

• Yes, you can drop temp-table but drop operation must within same scope where it was created (e.g, Within same Stored Procedure)
  consider the following example:
  
  CREATE TABLE #localTable ... 
  ..................................
  DROP TABLE #localTable;
• As per my view, explict deletion of temp-table does not make a sense, since this done automatically by SQL server for you. Even doing so, it does not give you any benefit.

Go to Top

---

7. Alternative-way: @Table Variable

Table variable is the best choice for temporay holding small-sized tabular data. One of the main advantage of table variable, in contrast to temp-table,  is performance.

Syntax:

DECLARE @table_name TABLE (
              c_name_1 data_type [column_attributes]
              [, c_name_2 data_type [column_attributes]]...
              [, table_attributes]
);

Syntax is similar to CREATE TABLE tableName(….) except instead of CREATE=DECLARE and table variable prefixed with @.

Examples

1. Creating Table type variable and inserting values.

   Declare @MyTableVar table(Id int primary key,Lookup varchar(15))
   Insert @MyTableVar values (1, '1Q2000');
   Insert @MyTableVar values (2, '2Q2000');
   Insert @MyTableVar values (3, '3Q2000');
   
   Select * from @MyTableVar
   Go

2. Creating Table type variable and inserting values from other table.

   DECLARE @BigBankers table(BankerID int, BankerName varchar(50));
   INSERT @BigBankers SELECT BankerID, BankerName
      FROM Bankers
      WHERE BankerID = 1;
   SELECT * FROM @BigBankers;
   GO;

Go to Top

---

8. Difference between @Table Vs #Table

1. No Indexing: A table variable cannot allow user-defined indexes using CREATE INDEX command whereas Temp table allows. However, a table variable contains system-defined indexes if it has primary and unique keys. 
2. No Altration to structure: A table variable cannot be altered once defined whereas Temp table can be altered. 
3. No SELECT INTO: A table variable cannot be defined using SELECT INTO command instead Declare a table variable then populate data into it using INSERT INTO. Whereas Temp table is able to defined using SELECT INTO.
4. No TRANSACTION Compatibility: A table variable does not support transaction, locking & logging whereas Temp table supports. Therefore, a table variable data cannot be rolled-back whereas Temp table data can be. 
5. No Dynamic SQL support: A table variable does not support Dynamic SQL execution whereas Temp table supports. However, declaring table variable inside EXEC statement can execute.
6. No Recompiles-SP while modifcaitons: A table variable never recompiles the stored procedure whereas Temp table recompiles the SP on each modification such as insert, update, and delete on it.

Temporary tables are physical tables stored in temp-db system databases whereas table variables are logical tables stored partially in RAM as well as in temp-db.

Go to Top

---

9. Conclusions

• Temporary Tables and Table Variables are greate features that lets you store and process intermediate results. Temporary tables are recommended when large no. of records you want to hold in memory for processing and table variables is ultimate for small-sized data holding and processing.
  
• The selection of local and global temp-tables is based on visibility. If you want to access the temp-table within same stored procedure(or UDF, cusors etc), then use LOCAL temp-tables. If you want to access temp-table across multiple sessions use GLOBAL temp-tables.
  
• Regarding to performance, Table variables (@Table) are always fast compare to temporary tables. Because these are directly accessible without participating in Logging and locking. Whereas Temporary table’s performance is optimized only explicitly through user-defined indexes.
• It is a common misconception that table variables are stored only in RAM, since they are stored both in RAM and tempdb.
• Like temporary tables, table variables are able to use in SP, UDF and batches. Typically, a table variable can be used as parameter and return type of UDF.
• Since table variables are non-sharable resources across multiple users, therefore locking is not required on table variables.
• Table variables get destroyed when they go out-of scope. i.e, End of SP, UDF or Batch execution.
  
• Unlike temp tables, Table variables define din SP are compiled once and reuse multiple times.

Go to Top