Temp tables in SQL Server can improve the efficiency and performance of complex queries by breaking them down into smaller pieces, storing intermediate results in memory, and eliminating unnecessary data.

In SQL Server, temporary tables are tables that are created for a specific purpose and are only available for the duration of the connection that created them. They can be created using either the # symbol or the ## symbol, with each symbol having slightly different behaviours.

Local Temp Tables
Temp tables created using the “#” symbol are known as local temp tables. These tables are only available to the connection that created them, and they are automatically dropped when the connection is closed. Local temp tables are commonly used and useful when you need to store and manipulate intermediate results within a query or stored procedure.

Global Temp Tables
These tables are available to any connection, and they are not automatically dropped when the connection that created them is closed. Global temp tables are useful when you need to share data between different connections or when you want to store data that needs to persist beyond the lifetime of a single connection or session.

Below I’ll demo how to perform the following on Temp Tables in SQL Server:
– Create a Local Temp Table
– Insert Rows into Temp Table
– Create an Index on Temp Table
– Select From Temp Table
– Drop Temp Table

Create a Local Temp Table

This statement creates a temporary table called #Chicken in a local scope:

-- Create a local temp table called #Chicken
CREATE TABLE #Chicken (
   ID INT,
   Name VARCHAR(50),
   Type VARCHAR(50),
   Price DECIMAL(18,2)
);

This temp table will be short-lived as it exists only for the duration of my current SSMS session. It is only accessible from the session in which it was created and is automatically dropped when the session ends (when I close the query window or it disconnects).

Insert Rows into Temp Table

This statement inserts four rows of data into the temporary table #Chicken:

-- Insert four rows of data into the #Chicken table
INSERT INTO #Chicken (ID, Name, Type, Price)
VALUES (1, 'Roasted Chicken', 'Whole', 14.99),
       (2, 'Fried Chicken', 'Wings', 9.99),
       (3, 'Grilled Chicken', 'Breast', 12.99),
       (4, 'Teriyaki Chicken', 'Stir Fry', 11.99);

Create an Index on Temp Table

In this next statement, I’m creating an index on the #Chicken temporary table to improve query performance:

-- Create an index on the Type column to improve query performance
CREATE INDEX idx_chicken_type ON #Chicken (Type);

An index in MSSQL is a data structure that allows faster search and retrieval of data from a table. By creating an index on the Type column of the #Chicken table, the database engine can more efficiently locate and retrieve rows based on the values in that column.

Select From Temp Table

This statement retrieves all rows from the temporary table #Chicken:

-- Retrieve all rows from the #Chicken table
SELECT * FROM #Chicken;

Drop Temp Table

This statement drops a temporary table called #Chicken.

-- Drop local temp table (also happens on session disconnect)
DROP TABLE #Chicken;

When a temporary table is no longer needed, it can be removed using the DROP TABLE statement. Dropping a temporary table removes it from the database and frees up the space it occupied.

We should give global temp tables more attention for this clean-up part. Global temp tables are visible to all connections which means they are not automatically dropped. It is important to drop global temp tables when they are no longer needed to prevent clutter and ensure efficient use of resources.