How to find and delete duplicate values in sql server

Introduction

Today, I am writing about how to Find and delete duplicate values in SQL server and delete in our SQL Server database, sometimes data is inconsistent due to not proper relational integrity it results to duplicate rows in corresponding tables. Duplicate records of any database will lead to unstable application and it will not generate information according to end user expectation. I have clarified extremely known SQL Server error SQL Server Error 25 And 87.

Step 1: Create SQL Server Database Table

Here creating SQL Server database table and saving records in to table.

CREATE TABLE [dbo].[Test_App](
[TableId] [int] IDENTITY(1,1) NOT NULL,
[ItemID] [int] NULL,
[Firstname] [varchar](50) NULL,
[lastname] [varchar](50) NULL
PRIMARY KEY CLUSTERED 
(
[TableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Test_App] ON 

INSERT [dbo].[Test_App] ( [ItemID], [Firstname], [lastname]) VALUES ( 5, 'John', 'Dave')
INSERT [dbo].[Test_App] ([ItemID], [Firstname], [lastname]) VALUES (5, 'Rajesh', 'Sharma')
INSERT [dbo].[Test_App] ([ItemID], [Firstname], [lastname]) VALUES (4, 'David', 'Hook')
INSERT [dbo].[Test_App] ([ItemID], [Firstname], [lastname]) VALUES (4, 'Lovely', 'Singh')
INSERT [dbo].[Test_App] ([ItemID], [Firstname], [lastname]) VALUES (2, 'Ajay', 'Singh')
INSERT [dbo].[Test_App] ([ItemID], [Firstname], [lastname]) VALUES (2, 'Dave', 'One')
SET IDENTITY_INSERT [dbo].[Test_App] OFF

Step 2: Creating CTE Query to locate duplicate records from SQL server database.

Here next showing how to find duplicate rows for ItemID using CTE query in SQL Server database.

WITH CTE AS(
   SELECT ItemID,
       RN = ROW_NUMBER()OVER(PARTITION BY ItemID ORDER BY ItemID)
   FROM [dbo].[Test_App]
)
select * from CTE WHERE RN > 1

Step 3: Delete Duplicate Records From SQL Server Table

This step guides to delete located duplicate rows for column ItemID in database table.

WITH CTE AS(
   SELECT ItemID,
       RN = ROW_NUMBER()OVER(PARTITION BY ItemID ORDER BY ItemID)
   FROM [dbo].[Test_App]
)
Delete from CTE WHERE RN > 1

Step 4: Consistent SQL Server Database Table Records

Now finally you can see consistent rows without redundancies in related database SQL table.

select * from Test_App

Conclusion

In this article, I have lined up query to find duplicate records and delete it later using CTE query to tune up our application database. CTE query is quite simple to run and remove duplicate records from our database. Duplicate records lead to slow performance of the database, It shows that our database is modelled very poor. It is very right time to remove duplicate records from our database to make our application highly perform able.

Relevant Reading

• Saving Changes not permitted in SQL Server
• The underlying provider failed on open
• How to Schedule Backup in SQL Server