Share via

Identity function on column

venkatesh padmanabhan 181 Reputation points
2020-10-21T04:27:26.253+00:00

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

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,566 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vaibhav Chaudhari 38,671 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

    33894-image.png

    ----------

    Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav