Index types:

• Clustered columnstore indexes
• Clustered indexes
• Nonclustered indexes
• Non-index (Known as heap)

Clustered columnstore indexes:

By default, Synapse SQL pool creates a clustered columnstore index when no index options are specified on a table. Clustered columnstore tables offer both the highest level of data compression as well as the best overall query performance.

Heap tables:

When you are temporarily landing data in Synapse SQL pool, you may find that using a heap table makes the overall process faster. This is because loads to heaps are faster than to index tables and, in some cases, the subsequent read can be done from cache.

Clustered and nonclustered indexes:

Clustered indexes may outperform clustered columnstore tables when a single row needs to be quickly retrieved. For queries where a single or very few row lookup is required to perform with extreme speed, consider a cluster index or nonclustered secondary index.