Alter Table Distribution (Azure Data warehouse)

Question

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?

Answer 1

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