Find Duplicates in MS SQL Server

We could use the following methods to find duplicate values in a table.

• GROUP BY clause.
• ROW_NUMBER() function.

1. Using the GROUP BY clause to find the duplicate values :

Syntax :

SELECT col1, col2, ...COUNT(*)
FROM table_name
GROUP BY col1, col2, ...
HAVING COUNT(*) > 1;

Example –
Let us create a table named Geek that contains three columns ID, A, and B.

CREATE TABLE Geek (
ID INT IDENTITY(1, 1),
A INT,
B INT,
PRIMARY KEY(id));

Let us add some values to the table Geek –

INSERT INTO Geek (A, B)
VALUES (1, 1),(1, 2), (1, 3), (2, 1),
(1, 2), (1, 3), (2, 1), (2, 2);

We know that Geek table contains the following duplicate rows –

(1, 2) (2, 1) (1, 3) 

MS SQL Server query to find the duplicate rows using GROUP BY clause in the Geek table :

SELECT A, B, COUNT(*) AS num
FROM Geek
GROUP BY A, B
HAVING COUNT(*) > 1;

Output –

Table – Geek

A	B	num
2	1	2
1	2	2
1	3	2

To find the full row details for each duplicate row, JOIN the output of the above query with the Geek table using CTE :

WITH CTE AS ( 
SELECT A, B, COUNT(*) AS num
FROM Geek
GROUP BY A, B
HAVING COUNT(*) > 1
)
SELECT Geek.ID, Geek.A, Geek.B
FROM Geek
INNER JOIN CTE ON
CTE.A = Geek.A AND CTE.B = Geek.B
ORDER BY Geek.A, Geek.B;

Output –

Table – Geek

ID	A	B
2	1	2
5	1	2
6	1	3
3	1	3
4	2	1
7	2	1

2. Using ROW_NUMBER() function to find duplicate values :

Syntax :

WITH cte AS (
SELECT col,ROW_NUMBER() OVER (
PARTITION BY col
ORDER BY col) AS row_num
FROM table_name
)  
SELECT * 
FROM cte  
WHERE row_num > 1;

MS SQL Server query to find the duplicate rows using ROW_NUMBER() function in the Geek table :

WITH CTE AS (
SELECT A, B,
ROW_NUMBER() OVER (
PARTITION BY A, B
ORDER BY A, B
) AS rownum
FROM Geek
)
SELECT *
FROM CTE
WHERE rownum > 1;

Output –

Table – Geek

A	B	rownum
1	2	2
1	3	2
2	1	2