Hi @JenP ,
Welcome to Microsoft Q&A forum and thanks for reaching out here.
By default, Synapse Analytics creates a clustered columnstore index (CCI), when no index options are specified. This is different from the default data structure for tables created in other SQL Server instances, which is a heap. This difference is because clustered columnstore indexes are the recommended data structure for large tables in Azure Synapse Analytics, as they offer both high compression and good query performance.
However, there are some cases where a heap may be a better choice for a table in Azure Synapse Analytics. For example, if a table is very small or if it is not frequently queried, then a heap may be more efficient.
When a query plan creates a table in tempdb, the default data structure will depend on the specific query. If the query does not specify any index options, then the table will be created as a heap. However, if the query specifies an index, then the table will be created with the specified index.
- Indexes on dedicated SQL pool tables in Azure Synapse Analytics
- Clustered columnstore indexes: Overview
- Data Modelling in Azure Synapse Analytics (dedicated SQL pool)
- Synapse Fundamentals – Table Creation
I hope this helps! Let me know if you have any further questions.