Deleting Duplicate Records in Sql Server

Sometimes when we don’t use primary or unique key in the SQL-Server table, then most of the chances to insert duplicate row into the SQL-Server table. It’s quite difficult task to delete the duplicate row from the SQL-Server table.

For the prevention of inserting duplicate rows into the SQL-Server table, we always use primary key or unique key in the SQL-Server table.

First Example:

Using the same ROW_NUMBER() function you can also easily delete the Duplicate Records present inside a table. I have a [dbo].[tm_Employee] Table which is having duplicate records. I need to delete the duplicate records which are having both the First_name and Last_name same. So, with the help of ROW_NUMBER()all the duplicate records present in the below table can be removed easily. Below are the SQL-Server table Structure and Insert Query

CREATE TABLE [dbo].[tm_Employee](       [First_name] [nvarchar](300) NULL,       [Last_name] [nvarchar](300) NULL,       [Salary] [decimal](20, 2) NULL,       [Joining_date] [datetime] NULL,       [Department] [nvarchar](200) NULL,       [City] [nvarchar](250) NULL,       [Age] [int] NULL ) ON [PRIMARY] INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Deepak', 'Kumar', 5000.00, 1905-06-07, 'Banking', 'Delhi',18) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Deepak', 'Kumar', 5000.00, 1905-06-07, 'Banking', 'Delhi',18) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Deepak', 'Singh', 8000.00, 1894-06-28, 'Insurance', 'Delhi',25) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Satish', 'Singh', 7000.00, 1894-06-27, 'Banking','Mumbai',28) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Satish', 'Singh', 7000.00, 1894-06-27, 'Banking','Mumbai',28) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Satish', 'Rai', 12000.00, 1894-06-27, 'Insurance', 'Delhi',32) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Pankaj', 'Kumar',6000.00, 1894-06-28, 'Services', 'kolkata',31) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Pankaj', 'Singh', 6000.00, 1894-06-28, 'Services', 'Delhi',35) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Pankaj', 'Kumar',6000.00, 1894-06-28, 'Services', 'kolkata',31) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Abhishek', 'Kumar', 650000.00, 1894-06-28, 'Services', 'Delhi',24) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Abhishek', 'Kumar', 650000.00, 1894-06-28, 'Services', 'Delhi',24) INSERT [dbo].[tm_Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Abhishek', 'Kumar', 650000.00, 1894-06-28, 'Services', 'Delhi',24) SELECT * FROM [dbo].[tm_Employee] The query for Delete Duplicate Records:- We can check duplicate record on the basis of  First_name and Last_name both are same then it means record has duplicate record. Note: - If First_name and Last_name are not same means record has not duplicate record. You can select below query simultaneously and then press execute query command. WITH TempEmployee (First_name, Last_name, duplicateRecordCount) AS (  SELECT    First_name,    Last_name,    ROW_NUMBER()OVER(PARTITION BY First_name, Last_name ORDER BY First_name)    AS duplicateRecordCount  FROM [dbo].[tm_Employee] ) DELETE FROM TempEmployee WHERE duplicateRecordCount > 1 TempEmployee is used for Temporary purpose; Instead of TempEmployee you can give any name as per convenance upto you. After the execution of the above query, [dbo].[tm_Employee] table will have the following records. SELECT * FROM [dbo].[tm_Employee] Note: Row_Number() function is present in SQL Server 2005 and in later version(s) Second Example: If your table has identity column or auto increament column. Then you can have to delete the duplicate record by the following sub-query. Here I have taken [dbo].[tm_Employee1] table with the [ID] as identity column. Below are the Table structure and Insert query: CREATE TABLE [dbo].[tm_Employee1] (       [ID] INT IDENTITY(1,1) NOT NULL,       [First_name] [nvarchar](300) NULL,       [Last_name] [nvarchar](300) NULL,       [Salary] [decimal](20, 2) NULL,       [Joining_date] [datetime] NULL,       [Department] [nvarchar](200) NULL,       [City] [nvarchar](250) NULL,       [Age] [int] NULL ) ON [PRIMARY] INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Deepak', 'Kumar', 5000.00, 1905-06-07, 'Banking', 'Delhi',18) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Deepak', 'Kumar', 5000.00, 1905-06-07, 'Banking', 'Delhi',18) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Deepak', 'Singh', 8000.00, 1894-06-28, 'Insurance', 'Delhi',25) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Satish', 'Singh', 7000.00, 1894-06-27, 'Banking','Mumbai',28) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Satish', 'Singh', 7000.00, 1894-06-27, 'Banking','Mumbai',28) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Satish', 'Rai', 12000.00, 1894-06-27, 'Insurance', 'Delhi',32) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Pankaj', 'Kumar',6000.00, 1894-06-28, 'Services', 'kolkata',31) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Pankaj', 'Singh', 6000.00, 1894-06-28, 'Services', 'Delhi',35) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Pankaj', 'Kumar',6000.00, 1894-06-28, 'Services', 'kolkata',31) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Abhishek', 'Kumar', 650000.00, 1894-06-28, 'Services', 'Delhi',24) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Abhishek', 'Kumar', 650000.00, 1894-06-28, 'Services', 'Delhi',24) INSERT [dbo].[tm_Employee1] ([First_name], [Last_name], [Salary], [Joining_date], [Department], [City],[Age]) VALUES ('Abhishek', 'Kumar', 650000.00, 1894-06-28, 'Services', 'Delhi',24) SELECT * FROM [dbo].[tm_Employee1] --Selecting distinct records SELECT * FROM [dbo].[tm_Employee1] E1 WHERE E1.ID = (SELECT MAX(ID) FROM [dbo].[tm_Employee1] E2 WHERE E2.First_name = E1.First_name AND E1.Last_name = E2.Last_name) The query for Delete Duplicate Records:- --Deleting duplicates DELETE [dbo].[tm_Employee1] WHERE ID < (SELECT MAX(ID) FROM [dbo].[tm_Employee1] E2 WHERE E2.First_name = tm_Employee1.First_name AND E2.Last_name = tm_Employee1.Last_name) SELECT * FROM [dbo].[tm_Employee1]