Suppose we have many records in a table and some of them are duplicate records. So it is necessary to delete all duplicate records from the table as per our business requirement.

Here, I am creating a sample table and inserting some junk data for deleting duplicate rows.

Below is the script for creating a sample table.

  1. CREATE TABLE Student
  2. (
  3. Id int IDENTITY(1,1) NOT NULL,
  4. Name varchar(50) NOT NULL,
  5. Class varchar(50) NULL,
  6. FatherName varchar(50) NULL,
  7. )
Below is the script for inserting some junk data into the above table.
  1. INSERT INTO Student(Name, Class, FatherName)
  2. VALUES
  3. ('student1', 'one', 'father1'),
  4. ('student1', 'one', 'father1'),
  5. ('student2', 'two', 'father2'),
  6. ('student3', 'three', 'father3'),
  7. ('student3', 'three', 'father3'),
  8. ('student3', 'three', 'father3'),
  9. ('student4', 'four', 'father4');
In the below image, we can see inserted data into the above Student table.

Student table

Here, there are some duplicate records present into the Student table.

So now we are deleting duplicate rows by using Common Table Expression (CTE) and ROW_NUMBER().

Below is the script for the same.
  1. WITH CTE as
  2. (
  3. SELECT ROW_NUMBER() Over(PARTITION BY Name,Class ORDER BY Name)
  4. As RowNumber,* FROM Student
  5. )
  6. DELETE FROM CTE where RowNumber >1
Now in below image we can see that three rows are affected into the Student table.

Run

Now using select query to see records into the Student table.
  1. SELECT * From Student
In the below image we can see that there are no duplicate records present into the Student table.

result