What are Local and Global Temporary Tables in SQL Server?

In this article, we will talk about temporary tables in SQL Server and more specifically, we will answer the question: What are Local and Global Temporary Tables in SQL Server?

 

What are Temporary Tables in SQL Server?

Temporary tables in SQL Server, is a special  type of database tables which once created, exist only temporarily.

That means that, temporary tables in SQL Server, are automatically dropped by the Database Engine under certain conditions.

Temporary tables, are particularly useful when developing large T-SQL scripts and you need to break the logic into smaller chunks.

 

Local Temporary Tables in SQL Server (#)

Local temporary tables in SQL Server, are defined by using the # (single hash) sign.

This type of temporary tables, are only available for the session in which they were created in.

After the session ends, local temporary tables are automatically dropped by the SQL Server Database Engine.

 

T-SQL Code Syntax Example – Creating a Local Temporary Table:

CREATE TABLE #LocalTempTable(
id INT PRIMARY KEY,
code VARCHAR(50)
);
GO

 

Global Temporary Tables in SQL Server (##)

Global temporary tables in SQL Server, are defined by using the ## (double hash) sign.

These temporary tables, are available to all sessions (visible to all users), but  after the session in which the temporary table was created ends and all references to the table are closed, then the temporary table is automatically dropped by the SQL Server Database Engine.

 

T-SQL Code Syntax Example – Creating a Global Temporary Table:

CREATE TABLE ##GlobalTempTable(
id INT PRIMARY KEY,
code VARCHAR(50)
);
GO

 

Watch our YouTube Video: What are Temporary Tables in SQL Server and what’s the Difference Between # and ##?

 

 

Enroll to our Online SQL Server Course for Beginners!

Feel free to check our online course “SQL Server Fundamentals – SQL Database for Beginners“.

SQL Server Fundamentals - SQL Database for Beginners (Online Course)
Lifetime Access | Live Demos | Q&A | Certificate of Completion

In this beginner-level course which has been updated for 2020, you will learn how to install SQL Server Database on both Windows and Linux, and how you can start doing basic tasks in SQL Server, using its free client tools SQL Server Management Studio, and Azure Data Studio.

Enroll Now with Discount!

 

 

Featured Online Courses:

 

Read also:

 

Check also: Our eBooks on SQL Server

Subscribe to our newsletter and stay up to date with our latest posts, eBook releases and other news!

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)

Loading...

 

Reference: SQLNetHub (https://www.sqlnethub.com)

© SQLNetHub.com