In Azure Synapse dedicated sql pool we have table with Identity column. But we are getting duplicate values in the Identity columns.

Question

We have a table with Identity column in Azure Synapse Sql. Recently we found out that there are duplicates values are generated. We are loading this tables using SP's but there is no SET IDENTITY_INSERT ON command applied. We are not able to understand that how can Identity column can generate duplicate values.

[Note]: We have partitions on tables, and we see no duplicate appearing on same partition.

Answer 1

This is happening to me again, starting Dec 1, 2023 through today.

Seeing the duplicate keys in a SELECT statement can be hard because the compiler sees in the table metadata that the column is supposed to be unique, so it will only return the first of each value, taking a shortcut, which works when the constraint is enforced. Except a unique (primary key) is not enforced in Synapse.

To properly see all the duplicate values, you need to run a function on the column so that the SQL Engine will ignore the table metadata and output each value without any shortcuts.

If you have:
CREATE TABLE a (pk int identity (1, 1));

Do:

SELECT COUNT(1) as c
, COUNT (DISTINCT pk) as ThisWillMatch
, COUNT (DISTINCT CAST (pk as varchar(20))) as ThisWillBeSmaller
FROM a

Or:

-- This will return an empty set:

SELECT pk, COUNT(1) as c
FROM a
GROUP BY a
HAVING COUNT(1) > 1

-- This will return the duplicates:

SELECT CAST (pk as varchar(20)) as pk_varchar, COUNT(1) as c
FROM a
GROUP BY a
HAVING COUNT(1) > 1