Add IDENTITY Types
The fact IDENTITIES and SEQUENCE Types are missing makes it very difficult to generate surrogate keys. It seems the only pattern available is to combine ROW_NUMBER() with a select max from table, which is horribly clunky and won't work if parallel treads are loading into the table.
IDENTITY property is now supported in SQL DW. Because this request includes information on SEQUENCE types, a new separate suggestion will be created for SEQUENCE types here: https://feedback.azure.com/forums/307516/suggestions/19746709.
See https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-identity for more information on IDENTITY
The key is make is easy to generate surrogate keys on up to 60 nodes running in parallel. The old paradigm of identity() may have to be modified.
Any update on this? I learn that the Identity Type is working under Free Subscription with the version:
"Microsoft Azure SQL Data Warehouse - 10.0.9009.9"
Dave Mulanaphy commented
Both Newid() and uniqueidentifier are not in the scope of Parallel Data Warehouse.
I second this suggestion to add IDENTITY and SEQUENCE to generate surrogate keys.
How tell the customer we don't support Identities or unique key validation? This is a crucial feature.
Ralph Kemperdick commented
This is a feature request that goes back a long time to PDW V1.0 and at some point it should make it into the product.
Given the continued relevance of dimensional modeling in modern DW architecture, and especially related BI tooling, this is a crucial gap to close. I appreciate that this is not a trivial engineering problem to solve in an MPP environment, but if Teradata can do it, so can Microsoft. Please make this happen well and soon.
I also believe this is a crucial feature, and a reason some people cold-shoulder Azure DW. +1
Peter Gadsby commented
I think this would be very useful as I believe using ROW_NUMBER causes a broadcast move which for obvious reasons is sub-optimal
Ron Dunn commented
Ajilius just blogged on this same problem. It is a competitive disadvantage for Azure SQL Data Warehouse.