Such reasons may include:
- Breaking the logic of a large and complex SQL Statement in smaller portions of code.
- Increase the performance of a SQL query, etc.
Types of Temporary Tables in SQL Server
But what types of temporary tables does SQL Server provide and what is the meaning of each type? Last but not least, where are temporary tables stored and how can we get schema information about them?
There are two types of temporary tables:
- Local temporary tables:
- Only available to the current connection to the database for the current login
- They are dropped when the connection is closed
- Global temporary tables:
- Available to any connection upon their creation
- They are dropped when the last connection using them is closed
Code Example – Local Temporary Table
CREATE TABLE #table_name ( column_name [DATATYPE]); GO
Code Example – Global Temporary Table
CREATE TABLE ##table_name ( column_name [DATATYPE]); GO
Strengthen your SQL Server Administration Skills – Enroll to our Online Course!
Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
So, consider as an example that you have created the following temporary table:
CREATE TABLE #temp_table ( id INT, name VARCHAR(50) ); GO
--Query 1(a): Get the exact name of the temporary table you are looking for DECLARE @table_name AS VARCHAR(300) SET @table_name = (SELECT TOP 1 [name] FROM tempdb..sysobjects WHERE name LIKE '#temp_table%'); GO
Explanation: When you declare a temporary table, SQL Sever adds some additional characters on its name in order to provide a unique system name for it and then it stores it in tempDB in the sysobjects table.
Even though you can query the temporary table with its logical name, internally, SQL Server knows it with the exact name. To this end, you need to execute the above query for finding the exact name of the temporary table.
--Query 1(b): Get column information for the temporary table -- by using the sp_columns stored procedure EXEC tempdb..sp_columns @table_name; GO
Explanation: The sp_columnsstored procedure returns column information for the specified tables or views that can be queried in the current environment.
Watch the Video with Live Demonstration
Learn essential SQL Server development tips! Enroll to our Online Course!
Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
Featured Online Courses:
- AI Essentials: A Beginner’s Guide to Artificial Intelligence
- Human-AI Synergy: Teams and Collaborative Intelligence
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
Read Also:
- Advanced SQL Server Features and Techniques for Experienced DBAs
- Applying Scrum for Efficient Database Administration
- SQL Server Database Design Best Practices
- Useful Python Programming Tips
- SQL Server 2022: What’s New – New and Enhanced Features (Course Preview)
- System.IO.FileLoadException: could not load file or assembly…
- How to Connect to SQL Server Databases from a Python Program
- Essential SQL Server Development Tips for SQL Developers
- The TempDB System Database in SQL Server
- SQL Server Installation and Setup Best Practices
- The feature you are trying to use is on a network resource that is unavailable
- SQL Server 2016: TempDB Enhancements
- tempdb growth
- Introduction to SQL Server Machine Learning Services
- Essential SQL Server Administration Tips
- What are SQL Server Statistics and Where are they Stored?
- How to Resolve: The feature “Scale-out deployment” is not supported in this edition of Reporting Services
- The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered – How to Resolve it
- How to Fix SQL Server Agent Not Showing in a Failover Cluster
- Introduction to Azure Database for MySQL (Course Preview)
- Data Management for Beginners – Main Principles (Course Preview)
- Working with XML and JSON Data in SQL Server
- …more
Check our other related SQL Server Administration articles.
Subscribe to our newsletter and stay up to date!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© SQLNetHub
Artemakis Artemiou, a distinguished Senior Database and Software Architect, brings over 20 years of expertise to the IT industry. A Certified Database, Cloud, and AI professional, he earned the Microsoft Data Platform MVP title for nine consecutive years (2009-2018). As the founder of SQLNetHub and GnoelixiAI Hub, Artemakis is dedicated to sharing his knowledge and democratizing education on various fields such as: Databases, Cloud, AI, and Software Development. His commitment to simplicity and knowledge sharing defines his impactful presence in the tech community.