CODEWITHSUNDEEP
sql-serverazuredata-warehouseazure-synapse
MetallicPriest
MetallicPriest

Reputation: 30735

In Azure Synpase, how can I check how a table is distributed

In Azure Synapse, how can I check how a table is distributed. For example whether it is distributed in a round robin manner or with hash keys.

Upvotes: 3

Views: 4295

Answers (2)

Kashyap
Kashyap

Reputation: 17401

Don't confuse distribution and partitioning. I've updated the question.

pdw_table_distribution_properties is certainly a possibility as mentioned.

Or just generate the create DDL for that table using any Client (Data Studio, SSMS, VS Code with plugin, ...).

E.g. in Azure Data Studio, right click on the table and click "Script as Create".

enter image description here

Look for DISTRIBUTION in WITH clause.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sa_logs]
(
    [version_number] [float] NULL,
    [request_start_time] [datetimeoffset](7) NULL,
    ...
    [referrer_header] [varchar](256) NULL,
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
)
GO

or for a table with HASH:

...
    DISTRIBUTION = HASH ( [hash_column] ),
...

Upvotes: 2

wBob
wBob

Reputation: 14379

You can use the Dynamic Management View (DMV) sys.pdw_table_distribution_properties in a dedicated SQL pool to determine if a table is distributed via round robin, hash or replicated, eg

SELECT 
       OBJECT_SCHEMA_NAME( object_id ) schemaName, 
       OBJECT_NAME( object_id ) tableName,
       *
FROM sys.pdw_table_distribution_properties;

It's the distribution_policy_desc column. Some sample results:

Results

Upvotes: 4

Related Questions