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.Enroll Now with Discount!
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 Data Science and SQL Server Machine Learning
- 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
- 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
Rate this article:
Reference: SQLNetHub (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.