Delete Duplicate Rows in Sql Server

Please find Original post from here

In this article, i would like to share a tip for deleting duplicate rows from sql server table.

For that we will be using a student table created from following query

CREATE TABLE tbl_Student
(
rollNumber INT,
studentName VARCHAR(MAX), 
address VARCHAR(MAX) 
)

after inserting for 4 rows into the table with one duplicate row.

tbl_Student

following query will be helpful to know is there any duplicate rows based on rollNumber,

SELECT DISTINCT rollnumber,COUNT(*) AS [Number of Duplcates]
FROM tbl_Student
GROUP BY rollNumber

Query result will look like this

and shows that rollNumber with '1' is repeated twice

Now i am going to delete this duplicate rows and this can be done in two method using CTE

Please find Original post from here

Method 1 - by keeping original

In this method all duplicate rows will be deleted by preserving original copy and my query looks like this

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY rollNumber ORDER BY rollNumber) AS RN
FROM tbl_Student
)

DELETE FROM CTE WHERE RN<>1

after deletion table will be as shown below

after deletion

Please find Original post from here

Method 2 - without keeping original

Here we will delete all repeated rows including original copy and here is the query

WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY rollNumber)
FROM tbl_Student)

DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)

resulting table will be like this

after deletion

-- Done Folks--

»Keep Sharing and Learning  ☺ «

Video Tutorial