Let's now look at how to delete these records in SQL SERVER 2008, first of all, to simulate making some simple duplicate records:
Copy Code code 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 ', 23,0)
Insert into Employee ([Id], [Name], [age], [Sex]) Values (8, ' Mirsa ', 23,0)
Insert into Employee ([Id], [Name], [age], [Sex]) Values (9, ' Mirsa ', 23,0)
Insert into Employee ([Id], [Name], [age], [Sex]) Values ("John", 26,default)
Insert into Employee ([Id], [Name], [age], [Sex]) Values (one, ' Abraham ', 28,default)
Insert into Employee ([Id], [Name], [age], [Sex]) Values (' Lincoln ', 30,default)
OK, first we use the most common method:
Delete from Employee Where name in (select Namefrom employee Group by name has Count (Name) >1);
Then use RowNumber ():
Delete T from (select Row_number ()-Partition by [Name] ORDER by (select 0) as rownumber,* from Employee) Twhere t.ro Wnumber > 1;
You can also use a CTE (Common Table Expressions):
Copy Code code as follows:
With Dups as
(
Select Row_number () over (Partition by [Name] ORDER by (SELECT 0)) as RN
From Employee
)
Delete from Dups
Where rn>1;
Plus the CTE of rank ():
Copy Code code as follows:
With Dups as
(
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 following are the execution plans for these four T-SQL queries:
You can see that the method without a CTE is most expensive, mainly in table Spool, where the cost is 44%, and table Spool is a physical operator.
The table Spool operator scans the input and places a copy of each row into a hidden spool table that is stored in the tempdb database and exists only during the lifetime of the query. If you redo the operator (for example, by Nested the Loops operator), but you do not need any rebind, you will use the spool data without having to rescan the input.
Note that the above method is only if there are fewer duplicate records, if more records are duplicated. Delete will be very slow, the best way is to copy the target data to another new table, delete the original table, rename the new table to the original table. or a temporary table, which can also reduce the database transaction log. Look at the following T-sql:
Copy Code code as follows:
With Dups as
(
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 ', ' Employee '
I hope this post will help you develop. Author: Petter Liu
