title: Deduplication of imported records in SQL server
body: <p>I have the following T_SQL Stored Procedure that is currently taking up 50% of the total time needed to run all processes on newly imported records into our backend analysis suite. Unfortunately, this data needs to be imported every time and is causing a bottleneck as our DB  size grows. </p>
  
  <p>Basically, we are trying to identify all duplicate in the records and keep only one of them.</p>
  
  <pre><code>DECLARE @status INT
  SET @status = 3
  
  
  DECLARE @contactid INT
  DECLARE @email VARCHAR (100)
  
  
  --Contacts
  DECLARE email_cursor CURSOR FOR 
  SELECT email FROM contacts WHERE  (reference  = @reference AND status = 1 ) GROUP BY email HAVING (COUNT(email) &gt; 1)
  OPEN email_cursor
  
  FETCH NEXT FROM email_cursor INTO @email
  
  
  WHILE @@FETCH_STATUS = 0
      BEGIN
          PRINT @email
          UPDATE contacts SET duplicate  = 1, status = @status  WHERE email = @email and reference = @reference  AND status = 1
          SELECT TOP 1 @contactid = id FROM contacts where  reference = @reference and email = @email AND duplicate = 1
          UPDATE contacts SET duplicate  =0, status = 1 WHERE id = @contactid
          FETCH NEXT FROM email_cursor INTO @email
      END
  
  
  CLOSE email_cursor
  DEALLOCATE email_cursor
  </code></pre>
  
  <p>I have added all the indexes I can see from query execution plans, but it may be possible to update the entire SP to run differently, as I have managed to do with others.</p>