3
\$\begingroup\$

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 
\$\endgroup\$
1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.