How to find duplicate record in sql server

We'll learn here how to find the duplicate record in a sql server database table.

Step1: Let's create the table.

CREATE TABLE [dbo].[Employee](

[Id] [int],

[Name] [nvarchar](50) NULL,

[Gender] [nvarchar](10) NULL,

[Salary] [int] NULL,

)

GO

Step 2: Insert the data into table

INSERT INTO Employee(Id,Name,Gender,Salary)VALUES(1,'Amit','Male',5000),

(2,'Rahul','Male',10000),

(1,'Amit','Male',5000),

(3,'Fernando','Male',20000),

(3,'Fernando','Male',20000),

(4,'Kimboon','Male',7000),

(5,'Kristen','Female',5000)

Step 3: Check the data in table with the help of below query.

SELECT* FROM Employee

Step 4: So with the help of below query we can  get the duplicate data from Employee table.

SELECT *,COUNT(*)Duplicate FROM Employee

group by ID,name,gender,salary

Having COUNT(*)>1

or

WITH duplicatedata AS (

    SELECT 

        *,

        ROW_NUMBER() OVER (

            PARTITION BY id

            ORDER BY id) duplicate

    FROM 

        Employee

) 

SELECT * FROM duplicatedata 

WHERE duplicate > 1;

Output:

So now we have the duplicate data.