0

I try to find a way to analyze table uniqueness that doesn't have primary key defined. I try to find an approach without db server resource more like query/read only to be able to run uniqueness queries to find out the column or composite column creating a unique record.

Thought of running several distinct count vs. count on each column but it will be very heavy and will not solve combination of keys. might use log2 search but it seems also heavy

Any ideas, SQL code to support that need?

Thanks!

dataminder
  • 31
  • 3

1 Answers1

0

One way as you mentioned GROUP BY and HAVING COUNT > 1. But the was I prefer is to find out if there is duplicate rows in a table and what they are is using the ROW_NUMBER:

WITH CTE AS
( SELECT col1, col2, col3 , RowNumber = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
FROM yourTable )

select * from CTE
WHERE RowNumber > 1

if you have multiple columns as the key you can put all of them in front of the PARTITION BY section of the command to find out the duplicates based on that column combination...

Aram
  • 5,537
  • 2
  • 30
  • 41