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
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
Learn more SQL Server Administration tips like this! Enroll to the Online Course!
Check our online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).
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
Featured Online Courses:
- Essential SQL Server Administration Tips (new)
- The Philosophy and Fundamentals of Computer Programming (new)
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- SQL Server Fundamentals (SQL Database for Beginners)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
Check our other related SQL Server Administration articles.
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). 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).