Microsoft Fabric Updates Blog

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

ScenarioIs supported by non-distributed #temp tableIs supported by distributed #temp table
Create session scoped local #temp tablesYesYes
Insert into the #temp table VALUES (<value>’),(‘value’)YesYes
Create a global ##temp tableNoNo
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
NoYes
Insert into #temp table SELECT from parquet backed user tableNoYes
Join two #temp tables          
*Joining non distributed temp tables with distributed temp tables is not a supported scenario
YesYes
Join #temp with sys.tables
*for non-distributed #temp table, use collate clause to resolve the collation conflict
YesNo
Insert into #temp table exec spNoNo
Insert into user table select * from #temp tableNoYes
Insert into #temp table from DMVs and Catalog views (sys.tables)YesNo
Create unlimited sized #temp tableNoYes
Drop #temp tableYesYes
Create and use distributed temp tables in nested statement execution such as stored proc, TSQL while loop, sp_executesqlYesYes

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.

Related blog posts

Session-scoped distributed #temp tables in Fabric Data Warehouse (Generally Available)

June 17, 2025 by Akshay Dixit

The Eventstreams artifact in the Microsoft Fabric Real-Time Intelligence experience lets you bring real-time events into Fabric, transform them, and then route them to various destinations such as Eventhouse, without writing any code (no-code). You can ingest data from an Eventstream to Eventhouse seamlessly either from Eventstream artifact or Eventhouse Get Data Wizard. This capability … Continue reading “Fabric Eventhouse now supports Eventstream Derived Streams in Direct Ingestion mode (Preview)”

June 17, 2025 by Dan Liu

Have you ever found yourself frustrated by inconsistent item creation? Maybe you’ve struggled to select the right workspace or folder when creating a new item or ended up with a cluttered workspace due to accidental item creation. We hear you—and we’re excited to introduce the new item creation experience in Fabric! This update is designed … Continue reading “Introducing new item creation experience in Fabric”