Delete duplicate records in SQL-mysql tutorial

This is an SQL statement used in mssqlserver2008 to delete duplicate records. if you need it, please refer to it.

This is an SQL statement used in mssql server 2008 to delete duplicate records. if you need it, please refer to it.

Duplicate records may occur in the Database due to some reasons, such as user input or data import failure. if you do not use a primary key, constraint, or other mechanisms to implement data integrity, record the data repeatedly. now let's look at how to delete these records in SQL SERVER 2008. First, we can simulate some simple duplicate records:

The code is as follows:

Create Table dbo. Employee ([Id] int Primary KEY, [Name] varchar (50 ), [Age] int, [Sex] bit default 1) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (1, 'James ', 25, default) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (2, 'James ', 25, default) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (3, 'James ', 25, default) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (4, 'Lisa ', 24, 0) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (5, 'Lisa ', 24, 0) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (6, 'Lisa ', 24, 0) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (7, 'mirsa) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (8, 'mirsa) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (9, 'mirsa) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (10, 'John', 26, default) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (11, 'Abraham', 28, default) Insert Into Employee ([Id], [Name], [Age], [Sex]) Values (12, 'Lincoln ', 30, default) Select * From dbo. Employee

First, we use the most common method:

The code is as follows:
Delete From Employee Where Name in ( Name From Employee Group By Name Having Count (Name)> 1 );

Then use RowNumber ():

The code is as follows:

Delete T From ( Select Row_Number () Over (Partition By [Name] Order By (SELECT 0) As RowNumber, * From Employee) T Where T. RowNumber> 1; With Du ( Select ROW_NUMBER () Over (Partition by [Name] Order by (SELECT 0) as rn FROM Employee ) Delete From Dups Where rn> 1; WITH Dups ( Select [ID], [Name], [Age], [Sex] , ROW_NUMBER () OVER (Partition By [Name] Order By (SELECT 0) AS rn , RANK () OVER (Partition By [Name] Order By (SELECT 0) AS rnk FROM Employee ) Delete from Dups WHERE rn <> rnk;

The execution plans for these four T-SQL queries are as follows:

You can see that the CTE method does not have the largest overhead, mainly in Table Spool, where the overhead is 44%, and Table Spool is a physical operator.

The Table Spool operator scans input and places a copy of each row in a hidden offline Table. This Table is stored in the tempdb database and only exists during the query period. If you repeat this operator (for example, repeat through the Nested Loops operator) without any re-binding, the offline data is used instead of scanning the input again.

Note that the above method only applies when there are few duplicate records, if there are many duplicate records. the DELETE operation will be very slow. The best way is to copy the target data to another new table, DELETE the original table, and rename the new table as the original table. or use a temporary table to reduce database transaction logs. see the following T-SQL:

The code is as follows:
WITH Dups ( Select [ID], [Name], [Age], [Sex] , ROW_NUMBER () OVER (Partition By [ID] Order By (SELECT 0) AS rn FROM Employee ) Select [ID], [Name], [Age], [Sex] INTO dbo. EmployeeDupsTmp FROM Dups WHERE rn = 1 Drop table dbo. Employee; EXEC sp_rename 'dbo. employeedupstmp', 'employe'