Total Page Preview:   000000008974

SQL SERVER- Delete-Remove duplicate record or rows from table in sql server

In this article I am going to explain How to delete - Remove duplicate record or rows from table in sql server with example. Sometimes you may want to delete all duplicate recoeds - rows in table. So I am explaining details with live example:
EmppId EmpName Salary  
1 ithubcity 1000  
1 ithubcity 1000 Duplicate row
1 ithubcity 1000 Duplicate row
2 blog 500  
2 blog 500 Duplicate row
3 blog 522  
Delete/Romove Duplicate record/rows Using Common Table Expression (CTE):
-----------------Delete/Romove duplicate row/records Using CTE ------------------------
;WITH CTEEmployee AS
       SELECT EmppId, EmpName, Salary,
       ROW_NUMBER() OVER(PARTITION BY  EmppId, EmpName, Salary ORDER BY EmppId) AS 'RowNumber'
       FROM tblEmployee
DELETE FROM CTEEmployee WHERE RowNumber > 1
Delete/Romove Duplicate record/rows Using Sub Query:
-----------------Delete - Remove duplicate row/records Using Sub Query ------------------------
DELETE  Employee FROM (
                                             SELECT  EmppId, EmpName, Salary,
                                             ROW_NUMBER() OVER (PARTITION BY  EmppId, EmpName, Salary ORDER BY EmppId) AS 'RowNumber'
                                             FROM     [dbo].[tblEmployee]
                                                  ) AS Employee
WHERE   RowNumber > 1
Get SQL Scripts for Create Tables and Insert Data for demo:
---------------Create table ------------
CREATE TABLE [dbo].[tblEmployee]
      [EmppId] [int] NULL,
      [EmpName] [varchar](50) NULL,
      [Salary] [float] NULL
--------------------------------- Insert record into table------------------------
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (2, N'blog', 500)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (2, N'blog', 500)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (3, N'blog', 522)
Thanks !!!
Brijesh Kumar

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn :


Good Explanation!!!Keep it up:)
Great article. I will be going through many of these issues as well..
It's perfect time to make some plans for the longer term and it's time to be happy. I have read this publish and if I may just I wish to recommend you some attention-grabbing things or advice. Maybe you can write subsequent articles relating to this article. I wish to learn even more issues about it!
Everything is very open with a very clear clarification of the issues. It was truly informative. Your website is very useful. Thank you for sharing!
Hello to every body, it's my first visit of this website; this webpage consists of amazing and in fact excellent material designed for visitors.
Hi mates, fastidious article and good urging commented here, I am genuinely enjoying by these.
Inspiring quest there. What occurred after? Good luck!
Attractive section of content. I just stumbled upon your weblog and in accession capital to assert that I acquire actually enjoyed account your blog posts. Anyway I will be subscribing to your augment and even I achievement you access consistently fast.
Hmm is anyone else encountering problems with the pictures on this blog loading? I'm trying to figure out if its a problem on my end or if it's the blog. Any feed-back would be greatly appreciated.
First of all I would like to say wonderful blog! I had a quick question that I'd like to ask if you don't mind. I was interested to find out how you center yourself and clear your head prior to writing. I've had difficulty clearing my mind in getting my ideas out there. I do enjoy writing however it just seems like the first 10 to 15 minutes are usually lost simply just trying to figure out how to begin. Any ideas or hints? Thank you!
Hey there! This is my first comment here so I just wanted to give a quick shout out and say I really enjoy reading your posts. Can you recommend any other blogs/websites/forums that deal with the same subjects? Thanks for your time!
I've been surfing online more than three hours today, yet I never found any interesting article like yours. It's pretty worth enough for me. In my view, if all site owners and bloggers made good content as you did, the internet will be much more useful than ever before.
You have made some decent points there. I looked on the web to learn more about the issue and found most people will go along with your views on this site.




Facebook User: