The official documentation ( example here ) says things like "When a table is created, by default the data structure has no indexes and is called a heap." but then go on to say "Dedicated SQL Pools create a clustered columnstore index when no index options are specified on a table". Which is it? And specifically, when the query plan creates a table in tempdb, which is it then?

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. Reference: 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.

Which index is actually default for Synapse dedicated tables?

Accepted answer

Smaran Thoomu 15,040 Reputation points Microsoft Vendor

2023-11-09T14:21:53.0033333+00:00
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.

Reference:

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.
Please sign in to rate this answer.
JenP 50 Reputation points

2023-11-11T19:26:38.47+00:00

@Smaran Thoomu Actually, one more question just occured to me. When a query plan decides to move data into tempdb (and for the sake of this example, let's assume it's a heap), it wouldn't be Round Robin, correct? Because tempdb is a shared resource and isn't distributed at all, it'd just be a "regular" table? I wonder then if it loses the parallel processing capability because it's not spread out amongst the nodes...?

Smaran Thoomu 15,040 Reputation points Microsoft Vendor

2023-11-13T12:22:55.9733333+00:00

Hi @JenP,

You are correct that tempdb is a shared resource and is not distributed across nodes in Azure Synapse Analytics. When a query plan moves data into tempdb, it will be stored in a regular table, not a round-robin table.

Regarding parallel processing capability, it depends on the specific query and the amount of data being processed. In general, parallel processing is still possible for queries that involve tempdb tables, but the performance may be impacted if the data is not distributed evenly across nodes.

I hope this helps! Let me know if you have any further questions.

JenP 50 Reputation points

2023-11-13T16:12:00.6733333+00:00

Thanks @Smaran Thoomu that clarified things. I appreciate your help!

Smaran Thoomu 15,040 Reputation points Microsoft Vendor

2023-11-13T18:16:21.0566667+00:00

JenP Glad to know it helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Which index is actually default for Synapse dedicated tables?

0 additional answers

Your answer