Summary of "Temporary tables in SQL Server Part 34"

Summary of “Temporary tables in SQL Server Part 34”

This video tutorial by Presume Technologies, presented by the speaker Wanker, explains the concept of temporary tables in SQL Server, detailing their types, creation, usage, and differences from permanent tables.

Main Ideas and Concepts

What are Temporary Tables?

Temporary tables are similar to permanent tables but are created in the tempdb database.
They exist temporarily and are automatically deleted when no longer in use.
Two types exist:
- Local Temporary Tables
- Global Temporary Tables

Permanent Tables vs Temporary Tables

Permanent tables are created in the user database and remain until explicitly dropped.
Temporary tables are created in tempdb and have a limited lifespan based on connection or procedure context.

Creating Temporary Tables

Use the same CREATE TABLE syntax as permanent tables.
Local Temporary Tables: Prefix the table name with a single hash symbol (#).
Global Temporary Tables: Prefix the table name with two hash symbols (##).

Local Temporary Tables

Created with a single # prefix (e.g., #PersonDetails).
Exist only within the session (connection) that created them.
Automatically dropped when the session that created them is closed.
Can be explicitly dropped using DROP TABLE #TableName.
When created inside a stored procedure, they are dropped automatically when the procedure completes.
SQL Server appends random numbers to their names internally to differentiate tables with the same name created by different sessions.
Different sessions can create local temporary tables with the same name without conflict due to the appended random suffix.
To query local temporary tables in tempdb.sysobjects, use the LIKE keyword because of the random suffix.

Global Temporary Tables

Created with two ## prefixes (e.g., ##EmployeeDetails).
Visible to all sessions and connections.
Exist until the last session referencing the table is closed.
Cannot have duplicate names across sessions; attempting to create a global temporary table with an existing name results in an error.
Unlike local temporary tables, global temporary tables do not have random numbers appended to their names.

Checking for Temporary Tables

Graphically: Expand the tempdb system database, then the “Temporary Tables” folder in SQL Server Management Studio (SSMS).
Programmatically: Query the tempdb..sysobjects system table using LIKE for local temporary tables.

Common Interview Question: Differences Between Local and Global Temporary Tables

Feature Local Temporary Table (#) Global Temporary Table (##) Name Prefix Single hash (#) Double hash (##) Visibility Only visible to the creating session Visible to all sessions Name Suffix Random suffix appended by SQL Server No random suffix Lifespan Dropped when creating session ends Dropped when last referencing session ends

Detailed Methodology / Instructions

Creating a Local Temporary Table

Use the syntax: sql CREATE TABLE #TableName ( -- column definitions );
Insert data: sql INSERT INTO #TableName VALUES (...);
Query data: sql SELECT * FROM #TableName;
The table exists only in the session that created it.
To drop explicitly: sql DROP TABLE #TableName;
When the session closes, the table is dropped automatically.

Creating a Global Temporary Table

Use the syntax: sql CREATE TABLE ##TableName ( -- column definitions );
Insert and query data similarly to local temporary tables.
The table is accessible from all sessions.
The table is dropped only when the last session referencing it closes.
Attempting to create a global temporary table with an existing name causes an error.

Checking Temporary Tables

In SSMS Object Explorer:
- Navigate to tempdb > Temporary Tables.
Using Query: sql SELECT name FROM tempdb..sysobjects WHERE name LIKE '#TableName%'

Use % wildcard because of the random suffix on local temporary tables.

Stored Procedure and Temporary Tables

Temporary tables created inside stored procedures are dropped automatically after procedure execution.

Handling Multiple Sessions

Each session can create a local temporary table with the same name without conflict.
SQL Server appends random numbers to differentiate them internally.
Global temporary tables must have unique names across sessions.

Speakers / Sources Featured

Primary Speaker: Wanker (Presenter from Presume Technologies)

This summary captures the core lessons, concepts, and practical instructions for working with temporary tables in SQL Server as presented in the video.