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.
Brynn Borton
shared this idea
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
10 comments
-
Anonymous
commented
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.
-
Dewey
commented
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
Yeah!
-
Anonymous
commented
Hi Admin,
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. -
Heejin
commented
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.
-
Darren
commented
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.
-
Pieter
commented
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.