Temp table in SQL Server

What is temp table in SQL Server?

In SQL Server, a temporary table is a table that is created and exists only for the duration of a database session or a specific batch of SQL statements. Temporary tables are typically used to store intermediate results, perform complex data manipulations, or break down a complex query into simpler steps.

There are two types of temporary tables in SQL Server:

1. Local Temporary Tables: These tables are prefixed with a single "#" symbol and are visible only within the current session. They are automatically dropped when the session that created them ends or when the batch of SQL statements completes.

Example:

CREATE TABLE #TempTable (
ID INT,
Name VARCHAR(50)
);

1. Global Temporary Tables: These tables are prefixed with a double "##" symbol and are visible across multiple sessions. They persist until all sessions that have referenced them are closed.

Example:

CREATE TABLE ##GlobalTempTable (
ID INT,
Name VARCHAR(50)
);

Temporary tables are useful for scenarios where you need to temporarily store and manipulate data within a specific scope, without affecting the structure of your permanent tables. Once you're done with a temporary table, it's a good practice to explicitly drop it to release the associated resources.

Please note that temporary tables can be a powerful tool, but they should be used judiciously to avoid performance issues and resource contention in a multi-user database environment.