Total Page Preview:   000000019439

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 :


Great article. I will be going through many of these issues as well..
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!
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!
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.
Good Explanation!!!Keep it up:)
Inspiring quest there. What occurred after? Good luck!
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.
I like looking through a post that can make people think. Also, thank you for allowing me to comment!
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!
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.
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!
Hi mates, fastidious article and good urging commented here, I am genuinely enjoying by these.
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.
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.
Its lіke y᧐u read my mind! Υoᥙ appear to know a lot about this, like yоu wrote the book іn it oг sοmething. I tһink that you can do with some pics to drive the message һome ɑ lіttle bit, Ƅut other than that, thіs is magnificent blog. Ꭺ fantastic read. I'll certaіnly be baсk.
Now I am ready to do my breakfast, afterward having my breakfast coming again to read further news.
Hurrah! Finally I got a website from where I know how to in fact obtain helpful data concerning my study and knowledge.
Amazing! This blog looks exactly like my old one! It's on a totally different subject but it has pretty much the same layout and design. Great choice of colors!
I am truly thankful to the owner of this web page who has shared this fantastic post at at this place. adreamoftrains webhosting
Hello, of course this article is actually good and I have learned lot of things from it on the topic of blogging. thanks.
If some one desires to be updated with latest technologies then he must be pay a quick visit this website and be up to date every day.
Howdy just wanted to give you a quick heads up. The words in your content seem to be running off the screen in Ie. I'm not sure if this is a formatting issue or something to do with browser compatibility but I thought I'd post to let you know. The design look great though! Hope you get the problem solved soon. Cheers
Hello, I wish for to subscribe for this weblog to obtain hottest updates, so where can i do it please help.
Piece of writing writing is also a fun, if you be familiar with after that you can write otherwise it is complex to write.
Great post however I was wanting to know if you could write a litte more on this subject? I'd be very grateful if you could elaborate a little bit more. Cheers!
Everyone loves it when folks come together and share opinions. Great blog, keep it up!
Hey, I think your blog might be having browser compatibility issues. When I look at your blog in Safari, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, amazing blog!
This is my first time pay a visit at here and i am truly happy to read all at single place.
I don't even know how I finished up here, however I assumed this submit was once good. I do not know who you are however definitely you're going to a well-known blogger in the event you are not already. Cheers!
I am regular reader, how are you everybody? This piece of writing posted at this web page is really pleasant.
With havin so much content and articles do you ever run into any problems of plagorism or copyright violation? My site has a lot of exclusive content I've either authored myself or outsourced but it appears a lot of it is popping it up all over the web without my authorization. Do you know any techniques to help reduce content from being ripped off? I'd certainly appreciate it.




Facebook User: