In many cases we often need to store data in some temporary table. One of this case often used within Stored Procedures, when it is not necessarily need to persist the data beyond the scope of the store procedure.
If in cases we need to store data in a table structure we can use temporary tables and for that SQL server provides two types of temporary table. These tables are stored in the ‘tempdb’ system database and their persistence availability and accessibility depend on the type of temporary table. These types are:
1. Local Temporary table (#table: Single # table)
2. Global temporary table (##table: Double # table)
There is another type of temporary table called ‘@table variable’ which is stored in memory not in ‘tempdb’. So this is out of scope of the context.
Local temporary table- Characteristics and when should be used:
· The table local temp table and data stored in it only accessible from the current scope/operation i.e. usually the store procedure using it or nested store procedure.
· These tables get cleared automatically when the current procedure goes out of scope. So, this is better on resources as it release resources early.
· Created with the given name with a session-specific identifier so that it could be distinguishable from similar named local temp table.
· Most common use of this type of table is examining data coming from another stored procedure.
· It reduces the amount of locking required opposed to the permanent table as only the current user accessing the table.
· Also performance can be gained by placing tempdb on a separate disk.
To create local temp table:
CREATE TABLE #tempTable
Global temporary table- Characteristics and when should be used:
· The Global temporary table is accessible throughout the session and visible by all other user session when a session is referencing to it.
· Global temporary table remains in the database permanently, but rows exist only within a given session. As the session ended the data in the table cleared but the table structure remains.
To create Global temp table:
CREATE TABLE ##globalTable