Can we use composite key while creating table structure using distribution as hash. If yes, kindly provide the syntax.

Hello Dheeraj Singh,

Thank you for posting your question in the Microsoft Q&A forum. 

Yes, you can use composite keys (multiple columns) for hash distribution in Azure Synapse Analytics. When creating a table with hash distribution, you can specify multiple columns to form a composite distribution key. 

Syntax for Hash Distribution with Composite Keys 

CREATE TABLE [schema_name].[table_name]  

(  

column1 datatype [NULL|NOT NULL], column2 datatype [NULL|NOT NULL], -- additional columns -- constraints if needed 

 )  

WITH  

(  

DISTRIBUTION = HASH (column1, column2), -- Composite hash distribution key -- Optional table options: CLUSTERED COLUMNSTORE INDEX, PARTITION (partition_column_name RANGE [LEFT|RIGHT] FOR VALUES (value1, value2, ...))  

); 

Some considerations you may review: 

• Performance Impact: The hash function will combine values from all specified columns to determine distribution 
• Data Skew: Choose columns that together provide good distribution (avoid combinations that might create hotspots) 
• Query Patterns: Composite keys work best when queries frequently filter on all key columns 
• Joins: Composite distribution helps when joining on all key columns 

Composite hash distribution is particularly useful when: 

• You frequently join tables on multiple columns 
• No single column provides good distribution characteristics 
• Your queries commonly filter on multiple columns together  

If the above answer helped, please do not forget to "Accept Answer" as this may help other community members to refer the info if facing a similar issue. Your contribution to the Microsoft Q&A community is highly appreciated.