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.