Why does Group by Grouping Sets work on SQL Server and not on the Azure SQL Data Warehouse?

Question

The Group By Grouping Sets, Cube and Rollup work on SQL Server, but they don't appear to be supported yet on the Azure SQL Data Warehouse. The same goes with some Ordered Analytics that use the keywords "Rows Unbounded Preceding". I also noticed there was no Unique Clustered Indexes on the SQL Azure Data Warehouse along with Primary and Foreign Key constraints. Is this just going to be in the first release or is there another reason?

Answer 1

Azure SQL Data Warehouse is a scale out MPP version of SQL Server. Azure SQL Data Warehouse just entered preview yesterday (learn more here http://aka.ms/sqldw) so features like Primary and Foreign Key constraints and ROW_NUMBER() OVER (UNBOUNDED PRECEDING... will be added as the service moves to general availability.

Azure SQLDW does support ROW_NUMBER() OVER (PARTITION BY...) today. This applies to other window aggregate functions like SUM() OVER().

You can view the list of supported syntax on the Azure SQL Data Warehouse documentation site: https://azure.microsoft.com/documentation/articles/sql-data-warehouse-overview-reference/

Answer 2

Azure SQL Data Warehouse didn't support CUBE, GROUPING SETS, CTE Recusrsion etc.