---
title: "How do you find duplicate records in a SQL Server table?"  
description: "How do you find duplicate records in a SQL Server table?"  
author: "Anubhav Sharma"  
published: 2026-05-14  
updated: 2026-06-07  
canonical: https://answers.mindstick.com/qa/116620/how-do-you-find-duplicate-records-in-a-sql-server-table  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 2 minutes  

---

# How do you find duplicate records in a SQL Server table?

## Answers

### Answer by Ravi Vishwakarma

To find [duplicate records](https://answers.mindstick.com/qa/116428/how-to-delete-duplicate-records-but-keep-one) in [SQL Server](https://www.mindstick.com/articles/12343/sql-server-2017-ctp-2-0-now-available), you typically use `GROUP BY` with `HAVING COUNT(*) > 1`.

### 1. Find Duplicates Based on a Single Column

For example, to find duplicate emails:

```plaintext
SELECT
    Email,
    COUNT(*) AS DuplicateCount
FROM Employees
GROUP BY Email
HAVING COUNT(*) > 1;
```

## Result:

| Email | DuplicateCount |
| --- | --- |
| john@example.com | 3 |
| jane@example.com | 2 |

### 2. Find Duplicates Based on Multiple Columns

For example, if duplicates are defined by the combination of `FirstName` and `LastName`:

```plaintext
SELECT
    FirstName,
    LastName,
    COUNT(*) AS DuplicateCount
FROM Employees
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1;
```

### 3. View the Actual Duplicate Rows

To see all rows that participate in duplicates:

```plaintext
SELECT *
FROM Employees
WHERE Email IN
(
    SELECT Email
    FROM Employees
    GROUP BY Email
    HAVING COUNT(*) > 1
)
ORDER BY Email;
```

### 4. Find Duplicates Using `ROW_NUMBER()`

This method is useful when you later want to delete duplicates:

```plaintext
WITH DuplicateRows AS
(
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Email
            ORDER BY EmployeeID
        ) AS rn
    FROM Employees
)
SELECT *
FROM DuplicateRows
WHERE rn > 1;
```

Rows with `rn > 1` are duplicate records, while `rn = 1` is the row that would typically be kept.

### 5. Find Completely Identical Rows

If you want to identify rows where all relevant columns match:

```plaintext
SELECT
    FirstName,
    LastName,
    Email,
    COUNT(*) AS DuplicateCount
FROM Employees
GROUP BY
    FirstName,
    LastName,
    Email
HAVING COUNT(*) > 1;
```

This returns combinations of values that occur more than once in the table.


---

Original Source: https://answers.mindstick.com/qa/116620/how-do-you-find-duplicate-records-in-a-sql-server-table

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.