CODEWITHSUNDEEP
t-sqlazureazure-sql-databasedata-warehouse
Scout1520
Scout1520

Reputation: 33

Alter Table Distribution (Azure Data warehouse)

In rushing to get off work I ran a large insert on a new table that has a Round Robin distribution. I know it is unlikely, but is there a way to alter the table's distribution from Round Robin to a Hash Distribution?

Upvotes: 3

Views: 11733

Answers (1)

wBob
wBob

Reputation: 14389

Unfortunately ALTER TABLE does not support changing the distribution of a table in Azure SQL Data Warehouse. The next best thing you could do is create a copy of the table using CTAS, drop the original table, then rename the new one, something like this:

CREATE TABLE dbo.yourTable2
WITH (
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH ( yourColumn )
    )
AS
SELECT *
FROM dbo.yourTable
OPTION ( LABEL = 'CTAS: Change distribution on dbo.yourTable' );
GO

DROP TABLE dbo.yourTable
GO
RENAME OBJECT dbo.yourTable2 TO yourTable;
GO

Upvotes: 5

Related Questions