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“.

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.

Learn More

 

 

Featured Online Courses:

• Boost SQL Server Database Performance with In-Memory OLTP 
• Essential SQL Server Administration Tips
• SQL Server Fundamentals (SQL Database for Beginners)
• Essential SQL Server Development Tips for SQL Developers
• The Philosophy and Fundamentals of Computer Programming
• .NET Programming for Beginners: Windows Forms (C#)
• Introduction to SQL Server Machine Learning Services
• Introduction to Azure SQL Database
• SQL Server 2019: What’s New
• Entity Framework: Getting Started (Complete Beginners Guide)
• How to Import and Export Data in SQL Server
• Get Started with SQL Server in 30 Minutes
• A Guide on How to Start and Monetize a Successful Blog

 

Read also:

• What are Relational Database Management Systems (RDBMSs) and the SQL Server Data Platform?
• Best Way to Learn SQL Server – A Suggestion
• Get Started with SQL Server by Learning the Fundamentals
• All our SQL Server Training-related articles

 

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: (3 votes, average: 5.00 out of 5)

Loading...

 

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

© SQLNetHub.com