T-SQL Query remove duplicates except for original row from the table

Question

Question: There is an Exam_Merge table into which to import records from a similarly structured table in another database. In this case, the Exam_Merge table contains the values of the primary key ID from another database, but this field is not unique. For some reason, some of the entries in it were duplicated: an entry with the same ID is contained in the table 2 times, the values of the remaining field of duplicate records also coincide.

It is necessary to remove duplicates, leaving only non-duplicate IDs.

My question - is it correct and efficient approach or something efficient way exists? sqlfiddle: http://www.sqlfiddle.com/#!18/dce35/1

DDL:

CREATE TABLE exam_merge 
 ( 
 id INT NOT NULL, 
 student_code NVARCHAR(10) NOT NULL, 
 exam_code NVARCHAR(10) NOT NULL, 
 mark INT NULL 
 ); 

QUERY:

WITH cte 
 AS (SELECT id, 
 student_code, 
 exam_code, 
 mark, 
 Row_number() 
 OVER( 
 partition BY id, student_code, exam_code, mark 
 ORDER BY student_code) AS rn 
 FROM exam_merge) 
DELETE cte 
WHERE rn > 1 

• dfhwze: I believe it is optimal: stackoverflow.com/questions/18390574/…
• Pavel Kononenko: @dfhwze Thank you twice! the first answer by your link is a great solution!

Answer 1

Review

Your SQL seems the generally accepted way (Discussed Before) of deleting duplicates.

WITH cte 
 AS (SELECT id, 
 student_code, 
 exam_code, 
 mark, 
 Row_number() 
 OVER( 
 partition BY id, student_code, exam_code, mark 
 ORDER BY student_code) AS rn 
 FROM exam_merge) 
DELETE cte 
WHERE rn > 1

Optimization

I found a possible optimization using a non-clustered index. You would like to create a (non-unique) index on your key (id) with the other columns included.

CREATE NONCLUSTERED INDEX idx_exam_merge ON exam_merge
(id) 
INCLUDE (student_code, exam_code, mark);

This example shows how such index could optimize the query plan to avoid clustered index lookup.