Table Creation Hints
The following hints allow you to modify or override table creation properties.
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. If you are temporarily landing data in dedicated SQL pool, you may find that using a heap table makes the overall process faster.
Override the CREATE TABLE Statement You can override the default Hyper-Q table creation behavior using the synapse_table_options_override hint. Use this in situations in which you require precise control over table creation options in Azure Synapse Analytics.
Override Table Distribution Using the CREATE TABLE Statement You can override table distribution using the Hyper-Q hint override_distribution_style. For example, you can specify that the CREATE TABLE statement create hash-distributed or round-robin distributed tables.
Override Column Distribution Using the CREATE TABLE Statement The default behavior of Hyper-Q is to use the columns defined as a Teradata primary index as Azure Synapse Analytics distribution columns. You can override the distribution of columns using the Hyper-Q hint override_distribution_cols.
Override Partitions Using the CREATE TABLE Statement You can override the default partition creation using the Hyper-Q hint synapse_table_partitions_override.
Specify if CREATE TABLE Uses MULTISET or SET for Teradata Emulation The default_table_type hint lets you specify if a table should be a SET or MULTISET if the CREATE TABLE statement does not specify a table type.
Override Teradata SET and UPI Table Emulation You can override Teradata SET and UPI table emulation using the Hyper-Q hint override_table_uniqueness.
Transient Table Distribution for Azure Synapse Analytics Using the transient_table_distribution_style configuration option, you can specify if Hyper-Q should use a round-robin or hash distribution for the temp tables it creates. Round-robin is the default.
Display Table Properties Using SHOW TABLE The physical_props_in_show_table hint lets you display applicable information related to the distribution and physical layout in SHOW TABLE output. Using the information returned by SHOW TABLE, you can recreate a table in Azure Synapse using the exact same table definitions.
Specify How Many Levels to Nest CASE Statements Hyper-Q transforms CASE statements that are nested beyond the defined levels into temporary tables. However, some query optimizers fail to process CASE statements with too deep of a nesting. You can specify how many level of nesting to use with CASE statements using max_nested_case_levels