Find Duplicate Rows in SQL Server

When working with database tables, you may have duplicate data entered in it and we usually don't want to keep duplicate records, so in this article, I have mentioned how we can find duplicate rows in SQL Server using RowNumber or using GroupBy.

We can use 'Unique' keyword to find duplicate rows, but sometimes, it cannot be effective when database is poorly designed, so it is better to use other options to find duplicate rows in SQL Server.

Suppose, we have below SQL Server database table, which have duplicate rows:

Using GroupBy

We can use GroupBy to get total number of duplicates rows using Multiple Columns check, so considering above table, we can check duplicate values using Name, Salary columns table

So SQL Query for duplicate would be as below:

select Name,Salary,COUNT(Name) 
       as Duplicate 
	   from [OrderDetails].[dbo].[EmployeeSalary] 
	   group by Name,Salary

Ouput:

Using RowNumber

You can also simply use RowNumber as CTE(Common Table Expression) in SQL Server to get duplicate rows in SQL Server.

WITH CTE AS(
   SELECT Name,Salary,
       RN = ROW_NUMBER()OVER(PARTITION BY Name,Salary ORDER BY Id)
   FROM [OrderDetails].[dbo].[EmployeeSalary] 
)
select * from CTE WHERE RN > 1

Here is the output of above SQL Query

As you can see from output, we were able to get Duplicate Rows with it's Count.

Using Inner Join and GroupBy

One of the things to see in the above SQL Query is that you don't get the Primary key (ID) of the duplicate rows, so in case, you want to get duplicate rows from the database table and delete it, you may want the Primary Key also.

So here is an example using SQL join.

SELECT
    y.Id,y.Name,y.Salary
    FROM [OrderDetails].[dbo].[EmployeeSalary]  y
        INNER JOIN (SELECT
                        Name,Salary, COUNT(*) AS CountOf
                        FROM [OrderDetails].[dbo].[EmployeeSalary]
                        GROUP BY Name,Salary
                        HAVING COUNT(*)>1
                    ) dt ON y.Name =dt.Name AND y.Salary=dt.Salary

The above Query uses Inner Join and GroupBy.

Output:

That's it, hope it helps.

You may also like to read:

Common Table Expressions (CTE) in Sql server

SQL Comments (Comment in SQL Query)

Change theme in SQL Server Management Studio (SSMS, Dark Mode)

Union vs Union All in sql server

Best Open Source Database Software (DBMS)

Get Date From Datetime in SQL Server

Get month and year in sql server from date