In SQL, a temporary table, often denoted with a "#" symbol in its name, is a special type of table that is created and exists only for the duration of a database session or within a specific scope, such as a stored procedure or a batch of SQL statements. Temporary tables are incredibly useful when you need to store and manipulate data temporarily within your SQL queries or procedures. They provide a way to organize, manipulate, and work with intermediate results or data sets.

Syntax

The syntax of a temporary table is like a physical table in Microsoft SQL Server with the exception of the use of sign (#). There are two types of temporary tables: local and global.

Local temporary tables are visible only to user who created the temp table during the same connection to an instance of SQL Server. Local temporary tables and are deleted after the user disconnects from the instance of SQL Server.

Global temporary tables are visible to all users and to all connections. Global temporary tables are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Syntax for local temp tables

CREATE TABLE #local_table
(
column_name_1 data_type,
column_name_2 data_type,
....
column_name_n data_type
);

Syntax for global temp tables

CREATE TABLE ##global_table
(
column_name_1 data_type,
column_name_2 data_type,
....
column_name_n data_type
);

Scope: Temporary tables are session-specific or scope-specific. They are only accessible and visible within the session or batch of SQL statements where they were created. They are automatically dropped when the session ends or when the scope exits.

Usage: Temporary tables can be used to store intermediate results, complex query outputs, or to break down a complex task into smaller, more manageable steps. You can insert, update, delete, or query data within temporary tables, just like regular tables.

Limitations: Temporary tables are not suitable for long-term data storage. They are designed for temporary data manipulation. They may introduce concurrency issues if multiple sessions try to use the same temporary table name simultaneously.

Examples

Temporary tables are typically created using the CREATE TABLE statement with the "#" symbol in front of the table name. For example:

CREATE TABLE #TEST_LOCAL (
ID INT ,
NAME VARCHAR (250) ,
HIREDATE DATE ,
);

CREATE TABLE ##TEST_GLOBAL (
ID INT ,
NAME VARCHAR (250) ,
HIREDATE DATE ,
);

Benefits

Temporary tables can help improve query performance by allowing you to precompute and store intermediate results.
They provide a way to break down complex queries into simpler steps, making your SQL code more readable and maintainable.
Temporary tables are often used in situations where you need to perform multiple operations on the same set of data within a single session.

Dropping Temporary Tables

Temporary tables are automatically dropped when the session ends or when the scope (such as a stored procedure) exits. However, you can also explicitly drop them using the DROP TABLE statement. For example:

DROP TABLE #TempTable

Summary

In summary, SQL temporary tables are a powerful tool for managing and manipulating data temporarily within your database sessions or SQL code. They provide a way to store intermediate results, simplify complex queries, and improve query performance while maintaining data isolation and scope-specificity.