How to Delete Duplicate Records from a Table in SQL Server


Sometimes it is very much required to remove the duplicate records from the table. Following is the simple and best way to remove the duplicate records from a table.

USE [AdventureWorks]
GO

CREATE TABLE [dbo].[tbl_temp](
       [id] [int] NOT NULL,
       [name] [nvarchar](50) NULL,
       [gender] [nvarchar](50) NULL
)

-- Insert records in the table

GO
INSERT [dbo].[tbl_temp] ([id], [name], [gender]) VALUES (1, N'Peter', N'M')
GO
INSERT [dbo].[tbl_temp] ([id], [name], [gender]) VALUES (2, N'Sam', N'M')
GO
INSERT [dbo].[tbl_temp] ([id], [name], [gender]) VALUES (3, N'Peter', N'M')
GO
INSERT [dbo].[tbl_temp] ([id], [name], [gender]) VALUES (4, N'Bill', N'M')
GO
INSERT [dbo].[tbl_temp] ([id], [name], [gender]) VALUES (5, N'Kate', N'F')
GO
INSERT [dbo].[tbl_temp] ([id], [name], [gender]) VALUES (6, N'Kate', N'F')
GO
INSERT [dbo].[tbl_temp] ([id], [name], [gender]) VALUES (7, N'Peter', N'M')

GO

-- See the records in the table

SELECT * FROM dbo.tbl_temp

How to remove duplicate records from from a table in SQL Server


--  Create CTE_Tbl for temporary result set

;WITH CTE_Tbl
AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY name,gender ORDER BY name,gender) AS TotalDuplicate
FROM dbo.tbl_temp
)

-- Delete the duplicate records

DELETE FROM CTE_Tbl WHERE TotalDuplicate > 1

-- See the final records in the table

SELECT * FROM dbo.tbl_temp


find and delete duplicate rows from a table in SQL Server