Duplicate inserted in synapse table Id column ( Identity 1,1 )

Question

Duplicate inserted in synapse table Id column ( Identity 1,1 )

Srinivas K 11

Hi All ,

I see Duplicate inserted in synapse table Id column even given that column given as Identity(1,1).

Is there any reson for this issue? generally it should not insert duplicates. Is there any root cause and solution for this is gr8 helpfull to us

phemanth 15,765 Reputation points Microsoft External Staff Moderator

2025-03-24T06:11:50.9466667+00:00

@Srinivas K Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Srinivas K 11 Reputation points

2025-03-24T16:59:02.86+00:00

Hi phemanth,

Thanks for the detailed information. we are inserting this table from mutple source and no manual inserts and this issue is coming not regularly and as coming rarely as it impacting its dependent tables. So do we have any solution for this ?

as is it dependent on any DISTRIBUTION = HASH or DISTRIBUTION = Round robbin ?

or any other solutions
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2025-03-27T06:48:59.6433333+00:00

@Srinivas K We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

2 answers

Your answer

phemanth 15,765 Reputation points Microsoft External Staff Moderator

2025-03-24T06:11:50.9466667+00:00

@Srinivas K Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Srinivas K 11 Reputation points

2025-03-24T16:59:02.86+00:00

Hi phemanth,

Thanks for the detailed information. we are inserting this table from mutple source and no manual inserts and this issue is coming not regularly and as coming rarely as it impacting its dependent tables. So do we have any solution for this ?

as is it dependent on any DISTRIBUTION = HASH or DISTRIBUTION = Round robbin ?

or any other solutions
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2025-03-27T06:48:59.6433333+00:00

@Srinivas K We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Erland Sommarskog 122K MVP Volunteer Moderator

First of all, the IDENTITY property on its own is never a guarantee for unique values, not even in regular SQL Server. However, in regular SQL Server you would only get duplicates if you reseed the identity value or similar.

In Azure Synapse Analytics, it's even more intricate since there are parallel independent nodes. Each node has its set of values, but if you do manual updates things can go wrong. See further https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-identity

Srinivas K 11 Reputation points

2025-03-24T16:57:40.99+00:00

Hi Erland Sommarskog,

Thanks for the detailed information. we are inserting this table from mutple source and no manual inserts and this issue is coming not regularly and as coming rarely as it impacting its dependent tables. So do we have any solution for this ?

as is it dependent on any DISTRIBUTION = HASH or DISTRIBUTION = Round robbin ?

or any other solutions

Answer 2

@Srinivas K

You're welcome, Given that the issue is intermittent and not due to manual inserts, it could indeed be related to the distribution method used in your Synapse table. Here are a few points to consider:

HASH Distribution: This method distributes rows based on the hash value of a specified column. While it helps in evenly distributing data, it can sometimes lead to issues if the hash function doesn't distribute values uniformly.
Round Robin Distribution: This method distributes rows evenly across all distributions without considering the values in any particular column. It is simpler but might not be optimal for large tables with frequent joins.

Check Distribution Key: Ensure that the distribution key chosen for HASH distribution is appropriate and results in an even distribution of data.
Use Unique Constraints: Implement unique constraints or primary keys on the ID column to enforce uniqueness.
DBCC CHECKIDENT: Use the DBCC CHECKIDENT command to check and correct the identity value if it gets out of sync.
Review Insert Logic: Double-check the logic used for inserting data from multiple sources to ensure there are no conflicts or race conditions.
Logs and Monitoring: Enable detailed logging and monitoring to capture the exact scenarios when duplicates are inserted. This can help in identifying any patterns or specific conditions leading to the issue.

If the issue persists, do let us know

For more detailed guidance refer to the official documentation

Share via

Duplicate inserted in synapse table Id column ( Identity 1,1 )

2 answers

Your answer