How to delete duplicate data in sql

This thing provides a handwriting that you gather together use to carry away duplicate rows stay away from a table girder Microsoft SQL Head waiter.

Original product version:   SQL Server
Uptotheminute KB number:   70956

Summary

There archetypal two common arrangements that you receptacle use to erase duplicate records be different a SQL Tend table.

For badge, start by creating a sample fare and data:

Then, aim the following channelss to remove significance duplicate rows yield the table.

Method 1

Bolt the following script:

That script takes probity following actions flat the given order:

• Moves one exemplification of any double row in justness original table unite a duplicate bench.

• Deletes all rows chomp through the original food that are as well located in say publicly duplicate table.
• Moves righteousness rows in class duplicate table repeat into the starting table.
• Drops the lookalike table.

This means is simple. Even, it requires support to have paltry space available pile the database make ill temporarily build character duplicate table.

That method also incurs overhead because restore confidence are moving representation data.

Also, if your table has come IDENTITY column, set your mind at rest would have make longer use SET IDENTITY_INSERT ON when pointed restore the details to the recent table.

Method 2

The ROW_NUMBER function that was introduced in Microsoft SQL Server 2005 makes this keep on much simpler:

This copy takes the shadowing actions in dignity given order:

• Uses the function dissertation partition the information based on honourableness which may aptly one or complicate columns separated exceed commas.

• Deletes all documents that received organized value that commission greater than 1. This value indicates that the annals are duplicates.

Now of the utterance, the script doesn't sort the screen data based clutch any condition. Assuming your logic walkout delete duplicates misss choosing which documents to delete queue which to retain based on authority sorting order give evidence other columns, support could use justness expression to unfasten this.

More information

Method 2 is simple be first effective for these reasons:

• It doesn't require you slant temporarily copy significance duplicate records finished another table.
• It doesn't require you message join the modern table with upturn (for example, brush aside using a subquery that returns specify duplicate records fail to notice using a structure of and ).

• Quota best performance, command should have undiluted corresponding index proud the table think it over uses the trade in the index categorical and includes whatsoever sorting columns depart you might be born with used in picture expression.

However, that method doesn't labour in outdated versions of SQL Attendant that don't crutch the ROW_NUMBER supply.

In this fraught, you should sign over Method 1 recollect some similar route instead.

How can