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
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    10 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      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.

      • DeweyDewey 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.

      • HeejinHeejin commented  ·   ·  Flag as inappropriate

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

      • Ralph KemperdickRalph Kemperdick commented  ·   ·  Flag as inappropriate

        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.

      • DarrenDarren 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.

      • PieterPieter commented  ·   ·  Flag as inappropriate

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

      • Peter GadsbyPeter 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 DunnRon 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