Which index is actually default for Synapse dedicated tables?

JenP 50 Reputation points

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?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,826 questions
{count} votes

Accepted answer
  1. Smaran Thoomu 2,835 Reputation points Microsoft Vendor

    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.


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

0 additional answers

Sort by: Most helpful