Temporary Tables in SQL Server

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.
TemporaryTable
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.

TemporaryTables_SQLServer

  • 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!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s