Temporary tables, as the name implies, are used to temporarily store data. These tables can be used for CRUD (Create, Read, Update, and Delete), join, and other actions similar to regular database tables. This means that temporary tables can function similarly to physical tables in a variety of ways, giving us greater flexibility. In these tables, for example, we can define constraints, indexes, and statistics. Temporary tables are useful for storing instantaneous result sets that are accessed repeatedly.
Temporary tables are deleted when the session that created them ends, or they can be explicitly deleted by users. The temp tables are not stored in memory, but rather in TempDB.
The temporary tables will be defined with the hash (#) symbol as shown below.
SELECT
——BusinessEntityID,
——PersonType,
——NameStyle,
——Title,
——FirstName,
——MiddleName,
——LastName,
——Suffix,
——EmailPromotion
INTO #Person
FROM Person.Person;
Through the following ways, we can identify if the temp table exists in the temp database.
- Expand “Databases” in SSMS, then “System Databases,” then “TempDB,” and finally “TempDB” to see the temporary tables.
- You can query the tempdb.sys.tables – SELECT * FROM tempdb.sys.tables
- The OBJECT ID function is used to acquire the database object’s identification number. This will aid in determining whether a given object already exists in the specified database, in this case TempDB.
IF OBJECT_ID(N’tempdb..#tempEmp’) IS NOT NULL
BEGIN
DROP TABLE #tempEmp
END
GO
Types of Temporary Tables:
1) Local Temporary Tables: These begin with a single “#” hashtag symbol and are only viewable on the session that is created. If the session that created the local temporary table is closed, SQL Server will immediately delete the temporary table.
2) Global Temporary Tables: These begin with a double “#” hashtag symbol and are accessible to other sessions, where they can be accessed or dropped.
Hope you find this article helpful.
Happy learning!!!
Big Data and SQL 
One comment