T-SQL Query remove duplicates except for original row from the table 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 &gt; 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 &gt; 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.