If we can create permanent (persistent) tables in database, why to go for temporary tables. Many people use temporary tables in database without knowing what the purpose and how it will add to performance tuning of queries.
This article will brief you about various types of temporary tables and will show you the examples for each type of temporary table.
"Divide'n'Conquer" a large "problem".
You can get absolutely mind boggling blinding speed out of some queries. This is the rule based on which temporary table concept came into picture. Many people think that "good set based code" has to be a single query to accomplish a task and nothing could be further from the truth. I've used Temp Tables to divide up queries that have couple of joins and that previous took anywhere from 30 minutes to 1 hour to run and have gotten them to run in seconds.
Temporary can be created at runtime and can do the all kinds of operations that one normal table can do. Scope of the temporary tables is limited to the session or instance. These tables are created in tempdb.
Temporary tables are used in the following scenarios:
- When we are doing large number of row manipulation in stored procedures. In complex joins, instead of joining the main table directly we can use temporary tables. This will reduce the data load on the table which in turn reduces the execution time of the query.
- This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
Some important information about temp tables:
- Temporary table can't have foreign key constraints on it.
- The best way to use a temporary table is to create it and then fill it with data. i.e., instead of using Select into temp table, create temp table 1st and populate with data later
Different Types of Temporary Tables
In SQL SERVER we can create the following types of temporary tables.
- Local Temp Table
- Global Temp Table
- Table Variable
Local Temp Table:
Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash
("#") signHere is an example for local temp table.
Global Temp Table
Global Temporary tables name starts with a double hash (
"##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed. Here is an example for global temp table.
scope of Local Temporary table is only bounded with the current connection of current user. Where as, the scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables
We can create a table a global table in one instance of database and we can use it in another instance of the same database until we disconnect from database.
Table Variable
We have a data type called TABLE in sql server. We can make use of this data type to create temporary tables in database. Here is the syntax for temporary table variable.
If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
Temporary tables are stored in tempdb as shown below:
