Skip to main content

Datometry Documentation

Specify HEAP as the Default Table Distribution Style

Using the DEFAULT_TABLE_LAYOUT hint, you can specify the distribution style for a table to be heap instead of clustered columnstore index, which is the default. If you are temporarily landing data in dedicated SQL pool, you may find that using a heap table makes the overall process faster.

To learn more about indexes and table types, see Indexes on dedicated SQL pool tables in Azure Synapse Analytics in the Microsoft documentation.

Syntax

The syntax for DEFAULT_TABLE_LAYOUT is:

DEFAULT_TABLE_LAYOUT = HEAP

Where HEAP specifies that all newly created tables use the heap distribution. If you do not specify the DEFAULT_TABLE_LAYOUT = HEAP hint, Hyper-Q creates tables as clustered columnstore index table by default.

Hint Scope

The DEFAULT_TABLE_LAYOUT hint can be specified at the session, request, query, and global level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

Examples
Example 1. Specify That Hyper-Q Create Heap Tables as a Global Setting

You can specify DEFAULT_TABLE_LAYOUT as a global setting in the Hyper-Q configuration file (DTM.INI). To create heap tables as the default distribution type, specify DEFAULT_TABLE_LAYOUT=HEAP.

"gateway".DEFAULT_TABLE_LAYOUT=HEAP

To learn more about specifying a hint as a gateway option in the Hyper-Q configuration file, see Gateway Configuration Parameters



Example 2. Override DEFAULT_TABLE_LAYOUT Using Session Level DTM Commands

You can override DEFAULT_TABLE_LAYOUT=HEAP by resetting it during a session using the dtm set and dtm reset commands. If, for example, DEFAULT_TABLE_LAYOUT=HEAP is specified in the DTM.INI configuration file as a global setting, you can disable the specified heap table creation within an interactive session.

In the following example DEFAULT_TABLE_LAYOUT is specified using the dtm reset command with no table type defined, disabling the creation of heap tables for the session. We then create a table and use a SELECT statement to retrieve the index name and type, which is CLUSTERED COLUMNSTORE, the default table type for Azure Synapse.

dtm reset DEFAULT_TABLE_LAYOUT;

CREATE TABLE table_name (id INT);

SELECT i.name AS index_name  
    ,i.type_desc  
FROM sys.indexes AS i  
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id  
WHERE is_hypothetical = 0 AND i.index_id <> 0   
AND i.object_id = OBJECT_ID('table_name'); 

index_name                                     |type_desc            |
-----------------------------------------------|---------------------|
ClusteredIndex_5af1545733214452a82573ee02c199d2|CLUSTERED COLUMNSTORE|


Example 3. Specify That Hyper-Q Create Heap Tables as a Query Hint

You can specify DEFAULT_TABLE_LAYOUT=HEAP as a hint within a query.

CREATE TABLE
@DTM_DEFAULT_TABLE_LAYOUT=HEAP FOR REQUEST
 table_name (id INT);