How to delete duplicate rows in a Table

The new functions NOW_NUMBER and CTE of SQL Server 2005 can be well implemented.
Example:
Create test data:
Copy codeThe Code is as follows:
Create table Dup1
(
Col1 int null,
Col2 varchar (20) null
)
Insert into Dup1 values
(1, 'aaa '),
(2, 'aaa '),
(2, 'aaa '),
(2, 'aaa '),
(3, 'bbb '),
(3, 'bbb '),
(4, 'ccc '),
(4, 'ddd '),
(5, 'eee ')
Select * from Dup1

Duplicate data can be viewed as follows:
Copy codeThe Code is as follows:
SELECT Col1, Col2, COUNT (*) AS DupCountFROM Dup1GROUP BY Col1, Col2HAVING COUNT (*)> 1


Next we will introduce how to delete duplicate data:
1. NOW_NUMBER: A good RANKING function (NOW_NUMBER, RANK, DENSE_RANK, NTILE) is added to SQL Server 2005. NOW_NUMBER () OVER (PARTITION GY) is the most direct and convenient method, you cannot modify tables or generate extra columns.
First, a column number is assigned, Which is sorted by Col1 and Col2.
Copy codeThe Code is as follows:
SELECT Col1, Col2, ROW_NUMBER () OVER (partition by Col1, Col2 order by Col1) AS rnFROM Dup1


The sequence number is as follows:
 
Obviously, duplicate columns are grouped and sorted by group. You only need to delete the sorting sequence number> 1.
Copy codeThe Code is as follows:
-- Use CTE
WITH DupsD
AS (
SELECT Col1, Col2,
ROW_NUMBER () OVER (partition by Col1, Col2 order by Col1) AS rn
FROM Dup1
)
DELETE DupsD
WHERE rn> 1;
-- Or
Delete a from (
SELECT Col1, Col2,
ROW_NUMBER () OVER (partition by Col1, Col2 order by Col1) AS rn
FROM Dup1) a where a. rn> 1

2. Create a table with a unique ID key.
Copy codeThe Code is as follows:
Alter table dbo. Dup1
ADD
PK INT IDENTITY
NOT NULL
CONSTRAINT PK_Dup1 primary key;
SELECT *
FROM Dup1;

Delete the records that are the same as those of Col1, Col2 and larger than Dup1.PK, that is, keep the records with the minimum PK value.
Copy codeThe Code is as follows:
DELETE Dup1
Where exists (SELECT *
FROM Dup1 AS D1
WHERE D1.Col1 = Dup1.Col1
AND D1.Col2 = Dup1.Col2
AND D1.PK> Dup1.PK );

3. select distant into. This method transfers non-duplicated result sets to the new table by using a new table.
Copy codeThe Code is as follows:
SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1; select * from NoDups

The first and third methods are recommended. The first method is more common in programming of T-SQL, and the third method is often used in ETL.