Dedicated SQL Pool - Strange Duplicate Check Error

Question

Dedicated SQL Pool - Strange Duplicate Check Error

VS29 246

I need some expert advice here...

I have a table with primary key of INT Data type. Logically, it should not allow duplicates and when checked for duplicates using count(*) I see none.

However, when I check for a particular value, it returns multiple records for the same field.

Environment - Dedicated SQL Pool.

Some one please provide any insight that you might have.

Thank you!

Ronen Ariely 15,206 Reputation points

2022-08-19T21:26:49.03+00:00

Hi,

when checked for duplicates using count(*) I see none... when I check for a particular value...

Please provide the exact queries which you use to do these checks

VS29 246

Hi @Ronen Ariely , here are my queries...

Table - [dbo].[TableA]  
Primary Key Column - Column A   
  
Query # 1  
SELECT [ColumnA], COUNT(*)   
FROM [dbo].[TableA]  
GROUP BY [ColumnA]  
HAVING COUNT(*)>1  
  
--I get no records when Q # 1 is ran.   
  
Query # 2  
SELECT *   
FROM [dbo].[TableA]   
WHERE [ColumnA] = 9293205   
  
--I get 50+ records when Q # 2 is ran.  
  
Query # 3  
SELECT * FROM (  
SELECT [ColumnA], RN = ROW_NUMBER() OVER (PARTITION BY [ColumnA] ORDER BY Date)  
FROM [dbo].[TableA]  
)x WHERE RN > 1  
  
--I get records with RN > 1 when Q # 3 is ran.

Thank you!

Accepted answer

1 additional answer

Your answer

Ronen Ariely 15,206 Reputation points

2022-08-19T21:26:49.03+00:00

Hi,

when checked for duplicates using count(*) I see none... when I check for a particular value...

Please provide the exact queries which you use to do these checks
VS29 246 Reputation points

2022-08-19T22:12:55.907+00:00

Hi @Ronen Ariely , here are my queries...

Table - [dbo].[TableA] Primary Key Column - Column A Query # 1 SELECT [ColumnA], COUNT(*) FROM [dbo].[TableA] GROUP BY [ColumnA] HAVING COUNT(*)>1 --I get no records when Q # 1 is ran. Query # 2 SELECT * FROM [dbo].[TableA] WHERE [ColumnA] = 9293205 --I get 50+ records when Q # 2 is ran. Query # 3 SELECT * FROM ( SELECT [ColumnA], RN = ROW_NUMBER() OVER (PARTITION BY [ColumnA] ORDER BY Date) FROM [dbo].[TableA] )x WHERE RN > 1 --I get records with RN > 1 when Q # 3 is ran.

Thank you!

Answer 1

Hi,

OK... now that you showed us what you did, it is simple to explain the issue :-)

The confusing is related to the way GROUP BY behaves in Dedicated SQL Pool.

GROUP BY Is not fully supported and when you use simple GROUP BY on a unique constraint then the server do not aggregate the data. The number of rows in the result SET will be the same as the number of rows you inserted and the value of the function COUNT(*) will be 1.

Therefore, when you added the filter HAVING COUNT(*)>1 then no row retuned.

The following document presents a simple example. Notice that before adding the unique constrain the GROPUP BY works and returns only 4 rows but after adding the constraint it simply do nothing.

It is well documented that GROUP BY in Dedicated SQL Pool is only fully supported when using one of the three types of grouping: GROUP BY with ROLLUP, GROUPING SETS or GROUP BY with CUBE

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-group-by-options?WT.mc_id=DP-MVP-5001699

The solution in your case is to use a query like bellow (for example using ROLLUP)

SELECT a1, count(*) AS total FROM t2 GROUP BY ROLLUP (a1) HAVING not a1 is null

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin

VS29 246 Reputation points

2022-08-20T02:18:56.457+00:00

Very well explained, thank you so much @Ronen Ariely .

Learnt something new, thank you @Erland Sommarskog & @Ronen Ariely !!
Ronen Ariely 15,206 Reputation points

2022-08-20T09:25:38.587+00:00

You are most welcome :-)

And thanks for thanks. It's the best reward we can get

+1

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

From the topic Primary key, foreign key, and unique key using dedicated SQL pool in Azure Synapse Analytics:

Table constraints

Dedicated SQL pool supports these table constraints:

-- PRIMARY KEY is only supported when NONCLUSTERED and NOT ENFORCED are both used.
-- UNIQUE constraint is only supported when NOT ENFORCED is used.

For syntax, check ALTER TABLE and CREATE TABLE.

FOREIGN KEY constraint is not supported in dedicated SQL pool.

Remarks

Having primary key and/or unique key allows dedicated SQL pool engine to generate an optimal execution plan for a query. All values in a primary key column or a unique constraint column should be unique.

Important

After creating a table with primary key or unique constraint in dedicated SQL pool, users need to make sure all values in those columns are unique. A violation of that may cause the query to return inaccurate result.

That is, Synapse does not enforce the uniqueness, that onus is on you. (As for why it does not enforce it - keep in mind that it is parallel architecture with many nodes.)

VS29 246 Reputation points

2022-08-19T22:14:05.52+00:00

Thank you, @Erland Sommarskog !

have few follow up q's...sorry if I sound dumb.

Does that mean - when I checked for duplicates it just checks in a single node and when running the row_number or checking for a particular value it checks all nodes?

Why is it so hard to enforce uniqueness on all nodes in the parallel architecture?

Thank you!
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-08-20T21:50:01.407+00:00

Why is it so hard to enforce uniqueness on all nodes in the parallel architecture?

The nodes are different computers. I don't know exactly how these Synapse clusters are built. I guess they are all in the same data centre, but there is still a network connection between them, and co-ordinating a thing like a uniqueness check when you're loading data would massively slow down the load speed.

Share via

Dedicated SQL Pool - Strange Duplicate Check Error

1 additional answer

Your answer