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:
Reference: SQLNetHub (https://www.sqlnethub.com)
© SQLNetHub.com
Artemakis Artemiou is a seasoned Senior Database and AI/Automation Architect with over 20 years of expertise in the IT industry. As a Certified Database, Cloud, and AI professional, he has been recognized as a thought leader, earning the prestigious Microsoft Data Platform MVP title for nine consecutive years (2009-2018). Driven by a passion for simplifying complex topics, Artemakis shares his expertise through articles, online courses, and speaking engagements. He empowers professionals around the globe to excel in Databases, Cloud, AI, Automation, and Software Development. Committed to innovation and education, Artemakis strives to make technology accessible and impactful for everyone.