Identity function on column

venkatesh padmanabhan 181

Hi.

I have an Identity column set for one of my columns in the Azure SQL database. However the column values are not shown as expected. I want to have a sequential numbering to be set for this column, however the values are getting set randomly. Expected values are 1 , 2, 3 .. but the values are random.

I have tried changing the distribution mode from RoundRobin to Hash. But this has not helped. How to fix this? Thanks

MartinJaffer-MSFT 26,081 Reputation points

2020-10-26T19:17:11.6+00:00

@venkatesh padmanabhan thank you for your question. If you do not mind my asking, why is it important that the identity column be sequential for you? Does some business logic rely upon the order of rows? I ask because there may be alternative options, but only if we can understand the ultimate goal.
Navtej Singh Saini 4,226 Reputation points Microsoft Employee

2020-10-28T19:35:38.467+00:00

@venkatesh padmanabhan

Please help with the ask from @MartinJaffer-MSFT so that we can further work on this.

Thanks
Navtej S

1 answer

Vaibhav Chaudhari 38,721 Reputation points

2020-10-21T05:16:45.167+00:00

I think, you are using Azure SQL DW (not SQL DB).

Azure SQL DW has its own distribution architecture and incremental order of value is not possible here

You can try using row_number() over(..) logic to generate the values in senquence

Reference - https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-identity#allocation-of-values

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav
Please sign in to rate this answer.
venkatesh padmanabhan 181 Reputation points

2020-10-21T05:35:04.68+00:00

Thanks. Yes, it is the Azure SQL DW.

I tried the above approach, but still not getting a sequential flow.

Navtej Singh Saini 4,226 Reputation points Microsoft Employee

2020-10-23T01:42:23.837+00:00

@venkatesh padmanabhan

Please check this post regarding the identity column and let us know if this will work or you need further info regarding this.

Hope this helps.

Regards
Navtej S

venkatesh padmanabhan 181 Reputation points

2020-10-23T04:48:52.443+00:00

HI. I have verified the site, but it does not have a solution to create the sequntial flow.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Identity function on column

1 answer

Your answer