Fabric warehouse temp table enhanced support

[jaybgholap]

Support for temp tables was introduced recently in Fabric warehouses.  For workloads/applications that are migrated from Azure Synapse SQL dedicated pools, this is a very important feature.  While I understand that Distributed temp tables are ideal going forward considering new architecture of warehouse (decoupled storage/compute). Existing applications (that support Azure synapse SQL pools) still use syntax that for Non-distributed #temp Table in application generated queries and SQL statements. 

So here is what I ask- 

there should be configuration/setting for a warehouse to have default mechanism for temp tables to be distributed temp tables.  Once its enabled, users/apps don't have to add clause below explicitly while creating temp tables 

WITH (DISTRIBUTION=ROUND_ROBIN) 

 This will ensure that Insert into #temp table statements are supported (as they are only supported for distributed temp tables but not for non-distributed temp tables). 

@TwinkleCyril  Tagging you here since you originally posted blog on this feature. 

[TwinkleCyril]

Thanks for the feedback. We’ve noted in the documentation why distributed #temp tables aren’t the default: - " 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." That said, we’re always looking for ways to improve the customer experience—especially in migration scenarios. We understand how valuable it would be to support existing syntax without requiring code changes, and we’re actively exploring options to make that easier.

[jaybgholap]

Thank you @TwinkleCyril for quick response. That helps a lot. My concern mainly comes from a migration scenario from Azure Synapse Dedicated pool to Fabric warehouse. A lot of "select * into #temp" table scenarios are covered now with recent support of distributed temp tables. however, there are cases when temp tables are created first in applications using Azure Synapse using "CREATE Table #tmp" without using distribution clause. Records are inserted later using "insert into #tmp select " .. This is not supported for non-distributed temp tables in Fabric warehouse as of today. Do you know if its something that's on the roadmap or any ideas on that will work for migration scenarios in the future?

[TwinkleCyril]

At this time, support for insert into #tmp select for non-distributed temp tables is not on our roadmap. We recommend using distributed temp tables instead, as they are fully aligned with the architecture of Fabric Warehouse and behave consistently with user tables (parquet backed) in terms of scalability. We understand that updating existing application code to adopt the new syntax can be a significant effort. While it may require some refactoring, using distributed temp tables ensures compatibility in the long run.