Data Platform Tips 82 - Heap tables in Azure Synapse Analytics

Heap table is a special type of database table that enables faster loading of data. Enables faster loading of data in staging tables before any transformation for Data Warehouses. Heap tables are better than tables that has clustered or nonclustered indexes as loading data is slower than heap tables.

A heap table doesn't have any specific ordering and it is simply set of rows. If a table has less than 100 million rows it is recommended to create a heap table and load the data in Data Warehouse. Once data is loaded to the heap table then add the indexes required.

CREATE TABLE heapTable

(

id int NOT NULL,

firstName varchar (50),

lastName varchar (50),

zipCode varchar (10)

)

WITH (HEAP);