Session-scoped distributed #temp tables in Fabric Data Warehouse (Generally Available)
Introducing distributed session-scoped temporary (#temp) tables in Fabric Data Warehouse and Fabric Lakehouse SQL Endpoints.
#temp tables have been a feature of Microsoft SQL Server (and other database systems) for many years. In the current implementation of Fabric data warehouse, #temp tables are session scoped or local temp tables. Global temp tables are not included in this release.
Session-scoped #temp tables exist only within the session in which they are created and last only for the duration of that session. They are not visible to other users or sessions and are automatically dropped from the system once the session ends or the user decides to drop the temp table. These tables are accessible to all users without requiring specific artifact-level permission.
Benefits of Session-Scoped #temp Tables
They can be used as a staging area for data before inserting it into user tables, helping to streamline the data insertion process. They are also ideal for storing intermediate results during complex data transformations, which can significantly optimize query performance. Developers find them useful for testing and debugging purposes, making them a versatile tool in various scenarios. The ease of use and flexibility of #temp tables allow for dynamic creation and dropping within a session, providing a convenient solution for temporary data storage and calculations.
Types of Session-Scoped #temp Tables
Two types of #temp tables can be created based on specific use cases:
Non-distributed #temp Table (mdf-backed) is the default type. The syntax for creating and using non-distributed #temp tables in Fabric Data Warehouse is similar to user tables, but you need to prefix the temp table name with #.
CREATE TABLE #table_name (
Col1 data_type1,
Col2 data_type2
);
Distributed #temp Tables (Parquet-Backed) can be created with the distribution equals round-robin keyword.
CREATE TABLE #table_name (
Col1 data_type1,
Col2 data_type2
) WITH (DISTRIBUTION=ROUND_ROBIN);
*data_type1 and data_type2 are placeholders for the supported data types in Fabric Data Warehouse Data types – Microsoft Fabric | Microsoft Learn.
Due to session context limitations in the Fabric query editor, it is recommended to use SSMS or execute #temp table queries within the same query execution context.
Using distributed #temp table is recommended as they fully align with warehouse user tables in terms of unlimited storage, data types supported, operations we can perform on them. The syntax for the rest of the operations is like user tables in Fabric Data warehouse, with prefix ‘#’ added to the table name to indicate that the table is a session-scoped #temp table.
For distributed #temp tables, if a collation is not specified for a string-type column, it defaults to the “DATABASE_DEFAULT” collation type. This means it inherits the collation of the current database context.
If a transaction involving a #temp table fails or when a #temp table is dropped using the DROP TABLE command, the table becomes immediately eligible for Garbage Collection. In the event of a front-end failover, all #temp tables from the previous session are also eligible for GC, ensuring that temp tables do not persist across failovers.
Why two types of #temp Tables?
SSMS uses #temp tables to populate the object explorer pane, displaying databases and tables. Non-distributed #temp tables adhere to SQL DB behavior and are the default type to ensure compatibility with existing tools like SSMS and other Azure tools. SSMS uses #temp tables for operations such as inserting from DMVs and catalog views. Non-distributed #temp tables support all column types, whereas distributed #temp tables have limitations due to unsupported column types (such as using column types sysname and nvarchar) in some DMVs, which is why they cannot be the default type of temp tables in Fabric Data Warehouse.
Distributed #temp tables address the limitations of non-distributed #temp tables, such as the ability to integrate with distributed user tables and support for creating temp tables of unlimited size.
Common scenarios supported by each type of #temp tables
| Scenario | Is supported by non-distributed #temp table | Is supported by distributed #temp table |
|---|---|---|
| Create session scoped local #temp tables | Yes | Yes |
| Insert into the #temp table VALUES (<value>’),(‘value’) | Yes | Yes |
| Create a global ##temp table | No | No |
| SELECT INTO from one temp table to another *In Fabric DW, if a target of a SELECT INTO/CTAS is a temp table, a distributed temp table is created always (regardless of source table’s type). So, operations like Select-Into with source as non-distributed temp tables will not be supported | No | Yes |
| Insert into #temp table SELECT from parquet backed user table | No | Yes |
| Join two #temp tables *Joining non distributed temp tables with distributed temp tables is not a supported scenario | Yes | Yes |
| Join #temp with sys.tables *for non-distributed #temp table, use collate clause to resolve the collation conflict | Yes | No |
| Insert into #temp table exec sp | No | No |
| Insert into user table select * from #temp table | No | Yes |
| Insert into #temp table from DMVs and Catalog views (sys.tables) | Yes | No |
| Create unlimited sized #temp table | No | Yes |
| Drop #temp table | Yes | Yes |
| Create and use distributed temp tables in nested statement execution such as stored proc, TSQL while loop, sp_executesql | Yes | Yes |
Conclusion
We are actively working on enabling additional scenarios for distributed #temp table. Stay tuned for more updates. Submit your feedback on Fabric Ideas and join the conversation on the Fabric Community.
