Azure SQL DW – Does It Support Partitioned Views?

azure-sql-data-warehouse

I've got a really expensive join in data warehouse that isn't distribution aligned. Unfortunately the join key is nullable, and only half the data has a value, meaning that it wouldn't be a good candidate for the distribution key.

Would be be possible to created a partitioned view where the null half of the table is round robin distributed and the other half is distributed on the available key values?

Best Answer

Update Jul 2019 - as at July 2019, Azure SQL Data Warehouse now supports materialized views.

The engine is implemented somewhat differently in SQL Data Warehouse so it does not support the feature Partitioned Views as SQL Server does. For example CHECK constraints are required to enable these to work correctly, but CHECK constraints are not supported in Azure SQL Data Warehouse.

You could spoof up something like this, ie basically a view over two tables each distributed as you described but they would have to be copies of the original tables in order to change the distribution. This would add extra complexity to your process. You could create the copies using CTAS which is powerful in SQL DW, but it's impossible to say if it would improve your processing times.

Make sure you have created all relevant statistics. From experience I find int\bigint columns work faster with columnstore rather than varchar.

Perhaps you can give an example of your tables, DDL, distributions, query and sample data and I can have a look at it.

Related Question