How can we improve Microsoft Azure SQL Data Warehouse ?

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.

160 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Brynn Borton shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

10 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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"

  • Anonymous commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    How tell the customer we don't support Identities or unique key validation? This is a crucial feature.

  • Darren commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    I also believe this is a crucial feature, and a reason some people cold-shoulder Azure DW. +1

  • Peter Gadsby commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    Ajilius just blogged on this same problem. It is a competitive disadvantage for Azure SQL Data Warehouse.

Feedback and Knowledge Base