Azure Synapse dedicated pool, when doing a join on 2 tables with different distribution, Synapse creates temporary tables (shuffle step in execution plan) with the following syntax:
CREATE TABLE [qtabledb].[dbo].[TEMP_ID_79718]
(
[...] DATE NOT NULL,
[...] INT NOT NULL,
...
) WITH(DISTRIBUTED_MOVE_FILE='');
I did not find anything on WITH(DISTRIBUTED_MOVE_FILE=''), what does it do exactly?
The WITH(DISTRIBUTED_MOVE_FILE='') clause is used to specify the name of the file that will be used to store the data that is being moved between the nodes.
This file is used to move data between nodes when doing a join on two tables with different distributions.
The DISTRIBUTED_MOVE_FILE option is used to specify the name of the file that will be used to store the data that is being moved between the nodes.
The '' value is used to specify that no file name is specified.
SQL DW will move each row to the correct Compute node according to the distribution column of the destination table. Rows that are already stored on the correct Compute node are not copied during this operation.
Data movement will happen on the plan: <dsql_operation operation_type="SHUFFLE_MOVE">.
Which means SHUFFLE_MOVE - Redistributes a distributed table.
The redistributed table has a different distribution column than the original distributed table.
This might be used to when running incompatible joins or incompatible aggregations.
You can refer to an example without including my distribution key
Other Reference: SHUFFLE_MOVE
