How to Delete Duplicate Rows in SQL Server

8 Flares Filament.io 8 Flares ×

In earlier post, I published article about Find duplicate rows in SQL. Now It’s easy when you have small amount of duplicate data. But in million records,it’s very difficult to delete duplicate rows in SQL.

Continue with older post, I have added one more duplicate Email.My table will look like below.!
User-Table-with-sample-data

Now what I want is, my script will delete duplicate records with one of the record remains in table.!

So I try to use below simple script which will find duplicate rows of SQL Server easily. !

duplicate-rows-in-SQL

 

Here, I have use MIN function, because I think record with first inserted will be valid, if you want you can use MAX function also.!

Now, I will delete duplicate rows in SQL table using below query.!

 

User-Table-without-duplicate-records

In this SQL Tutorial, I have already one Identity Column. If you don’t have you can add easily.!

I see many people use different approach like creating Temp table or inserting data into another table which is quite time-consuming & may degrade performance.!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">