Queries to Delete Duplicate Records in SQL Server

Queries to Delete Duplicate records

/*Start Create Table with Duplicate records */
CREATE TABLE DeleteDuplicate
(
ID INT,
NAME VARCHAR(88)
)

INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4

/*End Create Table with Duplicate records */


/*Method#01 Start DELETE Duplicate Rows using while loops*/
WHILE ( 1 = 1 )
BEGIN
SET ROWCOUNT 1

DELETE c
FROM DeleteDuplicate c
INNER JOIN (SELECT id,
NAME
FROM DeleteDuplicate
GROUP BY id,
NAME
HAVING Count(*) > 1) CC
ON C.id = CC.id
AND C.NAME = CC.NAME

IF @@ROWCOUNT = 0
BEGIN
SET ROWCOUNT 0
BREAK;
END
END
/*Method#01 End DELETE Duplicate Rows using while loops*/


/*Method#02 Start DELETE Duplicate Rows using Go statement*/
INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4
GO

DECLARE @DupRowCount INT = 0;
SELECT @DupRowCount =@DupRowCount +( Count(*) -1)
FROM DeleteDuplicate
GROUP BY id,
NAME
HAVING Count(*) > 1
select @DupRowCount

SET Rowcount 1
DELETE c
FROM DeleteDuplicate c
INNER JOIN (SELECT id,
NAME
FROM DeleteDuplicate
GROUP BY id,
NAME
HAVING Count(*) > 1) CC
ON C.id = CC.id
AND C.NAME = CC.NAME
GO 20
/* above we need to specify value of duplicate records. which got from variable @DupRowCount*/
SET RowCount 0
GO
/*Method#02 End DELETE Duplicate Rows using Go statement*/



/*Method#03 Start DELETE Duplicate Rows using CTE*/

INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4
GO


;WITH DELETECTE(Id,Name,DeleteCount )
as
(select id,name ,
r = row_number() Over(Partition by id,name order by id,name)
from DeleteDuplicate )
DELETE FROM DELETECTE
WHERE DeleteCount<>1
GO
/*Method#03 End DELETE Duplicate Rows using CTE*/

/*Method#04 Start DELETE Duplicate Rows using Physical Address(physloc)[Undocumented vartual column or pseudo  column]*/


INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4
GO


delete from DeleteDuplicate
where %%physloc%% not in
(select min(%%physloc%%) from DeleteDuplicate
group by ID,Name
)

/*Method#04 End DELETE Duplicate Rows using Physical Address(physloc)[Undocumented vartual column or pseudo  column]*/



DROP TABLE DeleteDuplicate


Note: Please try above SQL Script in Testing environment only.