Compare Snowflake Temporary and Transient Tables with SQL Temp Tables

By: Sergey Gigoyan | Updated: 2023-03-16 | Comments | Related: More > Snowflake

Problem

In a previous article, we discussed Snowflake temporary and transient tables. This article will compare SQL Server and Snowflake temporary tables and understand some of their similarities and differences. This article can be useful for SQL Server developers switching to Snowflake development. It is assumed that the readers have SQL Server experience and are familiar with SQL temporary tables. It is recommended to read this previous article, Temporary Tables vs. Transient Tables in Snowflake, before starting this one. Reading SQL Server Global Temporary Table Visibility is also recommended to better understand global temporary table visibility in SQL Server.

Solution

Below is an overview of temporary tables in SQL Server and Snowflake, along with similarities and differences, followed by example creation of these temporary objects in both SQL Server and Snowflake.

SQL Server Temporary Tables

In SQL Server, temporary tables are used to temporarily store data that is only needed for a specific period of time. These tables are stored in the tempdb database and come in two varieties: local and global.

Local temporary tables are denoted by a single "#" symbol at the start of their name, and they can only be accessed by the current connection for the user and are deleted when the user connection is closed.

Global temporary tables are denoted by two "##" symbols at the start of their name and can be accessed by any user after they are created. They are removed from the system once the connection to the server where the table was created is closed, and all connections referencing the table are terminated from the SQL Server instance.

Also, local and global temporary tables can be explicitly deleted using the DROP TABLE command.

Lastly, all temporary tables are cleared when a restart of SQL Server occurs since tempdb is recreated each time SQL Server starts.

Snowflake Temporary and Transient Tables

In Snowflake, temporary and transient tables are used for storing temporary data that is only needed for a short period. The main difference between the two is that transient tables can persist beyond the current session, while temporary tables are automatically dropped when the session ends.

Additionally, temporary and transient tables do not have a fail-safe period, meaning they are not protected in the event of a system failure or other events. Both types of tables contribute to overall storage charges, so it's important to drop them when they are no longer needed to avoid unexpected charges.

Similarities of SQL Server Temporary Tables and Snowflake Temporary and Transient Tables

In terms of usage, both Snowflake temporary tables and SQL Server temporary tables are used to store temporary data that is only needed for a short period of time. These tables are typically used for storing intermediate results for complex queries, holding data for reporting or analysis, etc. Both Snowflake and SQL Server provide automatic cleanup once the session is closed. Additionally, both systems offer similar functionality for querying and manipulating data with the temporary tables.

In terms of scope, temporary tables in Snowflake are very similar to local temporary tables in SQL Server. They both exist in the current session and are automatically dropped when the session ends.

Transient tables are somewhat similar to SQL Server global temporary tables in terms of visibility. They are both available not only for the current session but also for other sessions.

In Snowflake, as well as in SQL Server, all these tables consume storage. In Snowflake, they are stored in the data storage; in SQL Server, they are stored in tempdb (not in memory).

Differences between SQL Server Temporary Tables and Snowflake Temporary and Transient Tables

Even though temporary tables in Snowflake and SQL Server share some similarities, there are also distinct differences.

For example, transient and global temporary tables in SQL Server both have a level of availability beyond the creating session. However, unlike global temporary tables in SQL Server, which are automatically deleted when the creating session and all referencing sessions end, transient tables in Snowflake persist in storage even after those sessions have been terminated. This makes them more similar to permanent tables. On the other hand, global temporary tables in SQL Server are automatically deleted when the creating session terminates, and there are no longer any active references to the table.

Another key difference is that SQL Server does not have an equivalent to Snowflake's time travel feature. This means that in SQL Server, you cannot query temporary data as it existed at a specific point in time. This feature can be useful for auditing and troubleshooting. The Time Travel feature can be set up to one day for temporary and transient tables.

The syntax of creating and referring to these tables is also different in these two systems. In SQL Server, local temporary table names start with # and global temporary table names with ##. Unlike in SQL Server, Snowflake has no special characters needed for the table names.

Example Creation of SQL Server Temporary Tables

Below is a code sample that creates local and global temporary tables in SQL Server:

CREATE TABLE #TestTable(
   [ID] [int] NOT NULL,
   [Val] [int] NOT NULL,
 
)
GO
 
CREATE TABLE ##TestTable(
   [ID] [int] NOT NULL,
   [Val] [int] NOT NULL,
 
)
GO
 
SELECT * FROM #TestTable
SELECT * FROM  ##TestTable

Example Creation of Snowflake Temporary and Transient Tables

The following code creates temporary and transient tables in Snowflake:

CREATE TEMPORARY TABLE tmpTestTable (ID INT, Val VARCHAR(50));
 
SELECT * FROM TestDB.TestSchema.tmpTestTable; 
 
CREATE TRANSIENT TABLE transientTestTable (ID INT, Val VARCHAR(50));
 
SELECT * FROM TestDB.TestSchema.transientTestTable;

Conclusion

Here is some additional information that can be helpful:

We do not need to specify the database name in SQL Server when creating temporary tables, as they are created only in the tempdb database.
Snowflake has no concept of a temporary database, so transient and temporary tables are created in the specified databases.
In Snowflake, you can create temporary and non-temporary tables with the same name within the same schema. In that case, it is important to consider that the temporary table hides the non-temporary table with the same name.
In SQL Server, it is not possible to have regular and temporary tables with the same names because the temporary table name begins with # or ## for a global temporary table). However, the part of the name after the # signs can be the same for temporary and non-temporary tables, as we saw in the example above.

This article has provided a comparison of Snowflake temporary and transient tables with SQL Server temporary tables. It highlighted the similarities, such as their temporary nature and storage charges, as well as their key differences, such as their visibility and persistence. This information can be useful for SQL Server developers transitioning to Snowflake development, as it allows them to understand the similarities and differences between the two systems. It is important to note that the best choice for temporary tables will depend on the specific use case, as both Snowflake and SQL Server offer unique advantages and disadvantages.

Next Steps

For additional information, please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-03-16

Snowflake Temporary Tables and Transient Tables vs. SQL Temporary Tables