Temp tables are a powerful feature in SQL Server that allow you to store and manipulate temporary data within a session. They are similar to normal tables, but with a few key differences.

When creating a temp table, SQL Server generates a unique name for the table to avoid conflicts with other sessions. This is because temp tables are created in the tempdb database, which is shared across all sessions in an instance.

Let’s take a closer look at how temp tables are created and managed in SQL Server.

Creating a Temp Table

To create a temp table, you can use the following syntax:

CREATE TABLE #temptable_test (
  id INT NOT NULL IDENTITY (1, 1),
  Name CHAR(100) NOT NULL,
  DOJ DATETIME NOT NULL
);

After the table is created, you can view the object definition inside the tempdb database by querying the metadata:

SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U';

When examining the metadata, you will notice that the temp table has a negative object id and a unique name with a long number of underscores and a number (e.g., ___02). This is done to ensure that each session’s temp table is unique and does not conflict with other sessions.

Multiple Temp Tables with the Same Name

In some cases, you may have multiple sessions creating temp tables with the same name but different column definitions. SQL Server handles this by appending an auto number extension to each table’s name, making them unique.

For example, if we create a second temp table with the same name but different schema:

CREATE TABLE #temptable_test (
  id INT NOT NULL IDENTITY (1, 1),
  FullName CHAR(100) NOT NULL
);

When examining the tempdb metadata, you will see that each table has a different auto number extension, ensuring uniqueness.

Lifecycle of Temp Tables

Temp tables are automatically dropped as soon as the session that created them is closed or completed. This makes them a convenient option for storing temporary data that is only needed for the duration of a session.

It’s important to note that temp tables are not accessible outside of the session that created them. They are scoped to the specific session and are not visible to other sessions or queries.

Conclusion

Temp tables are a useful feature in SQL Server for storing temporary data within a session. They are similar to normal tables but have unique naming conventions to avoid conflicts between sessions. Understanding how temp tables are created and managed can help you leverage their power effectively in your SQL Server environment.

Have you encountered temp tables in your SQL Server environments? How many temp tables are typically present in your environment at any given time? Share your experiences and insights in the comments below!