I want to provide incremental value (+1) to one of the column in my Synapse table.
I tried to create a Sequence (to get next value from it), but seems it is not supported in Synapse for now. https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/19746709-support-sequence-types-in-sql-dw
I also tried to define that particular column as IDENTITY (1,1). Sample:
create table test.seq(
seq1 int IDENTITY(1,1) not null,
name varchar(10)
);
But it is not working as expected because when I execute my insert query, the values got skip for the column i.e. I am not getting continuous value (1,2,3,4,5,7,9,10...). Seems it is because of MPP execution in Synapse.
Can someone please help, how can I implement sequence in Synapse table or any other workaround where I can get continuous values (with +1).