Support SEQUENCE types in SQL DW
Thanks for your suggestion. We are looking into this scenario for a future release. 10703657
Miika Varis commented
Very important. We have a chain of integrations (ETL loads) that need an unique persisted numeric ID for the whole chain. Since IDENTITY value cannot be trusted in Synapse, and it is impossible to use in CTAS in many cases (in our case UNION; the MS Docs suggested UPSERT pattern for ADF), we need a replacement for IDENTITY. SEQUENCE would be the most logical replacement. It would make sense to handle ID generation on server level instead of creating own clunky/slow logic for it.
Nava Jeevan commented
This is a must have feature in the Azure SQL Data Warehouse as it doesn't maintain the order/sequence of the IDENTITY field.
Ralph Kemperdick commented
Having SEQUENCES makes porting DW solutions from SQL onprem to QL DW much easier.
Would make the life of DW architects much easier, specifically with regards to Data Vault models.
Did this is achieved by any means in SQL DW ?
I am unable to identify correct replacement for SEQUENCE in SQL dw.
This seems like it would be required for partition swap loading mechanisms, or? I am new to SQL DW and coming from Oracle, so perhaps the identify columns can handle that scenario.
Created due to the completion of IDENTITY. See here: https://feedback.azure.com/forums/307516/suggestions/12303591