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

--  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