#Temp tables defined under a different collation to the underlying warehouse

[WLY168]

So Microsoft Fabric goes through a lot of trouble to abstract and provide a programming model that is similar to normal SQL stored procedures. However, session scoped temporary tables (SQL_Latin1_General_CP1_CI_AS) are created in a collation different to that of an underlying warehouse (Latin1_General_100_BIN2_UTF8). Now I accept the explanation that the underlying engine used for session scoped temporary tables is different. What I don't accept is how an actraction has leaked into the programming model forcing developers to either manually specify collation at create or join time or create the table with distribution. The default should be to accept parallelism and create #temp tables with distribution instead of opting in (unless someone can enlighten me with an opposing argument.)

[Wikkleyn_81]

hi @WLY168 there are two types of temp tables in fabric, Non-distributed (default type) and Distributed. 

Distributed temp tables defaults to the default db collation type.

here is a link to a blog post with detail on the differences and more detail:

https://blog.fabric.microsoft.com/en-US/blog/announcing-the-general-availability-of-session-scoped-d...

[v-lgarikapat · Accepted solution]

Hi @WLY168,

Thank you for reaching out to the Fabric Forum.

To help address your question, please refer to the attached documentation below. These resources provide detailed guidance and should clarify the behavior you are experiencing.

If you have any further questions after reviewing the documents, feel free to follow up here and we’ll be glad to assist.

Data Warehouse collation - Microsoft Fabric | Microsoft Learn

Set or Change the Column Collation - SQL Server | Microsoft Learn

Session-scoped distributed #temp tables in Fabric Data Warehouse (Generally Available) | Microsoft F...

Thanks,

Lakshmi.

[v-lgarikapat]

Hi @Wikkleyn_81 “Thanks for your prompt responses.

@WLY168 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further

Thanks,

Lakshmi

[v-lgarikapat]

@WLY168 Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further

Thanks,

Lakshmi