title: Finding duplicate record without looping in SP - SQL Server
body: <p>I have table <code>T1</code> with this structure and sample data:</p>
  
  <pre><code>ID  CID Reference   Rdate 
  --------------------------------
  1   123 REF65798    11/11/2011
  2   123 REF65798    11/11/2011
  3   156 REF65798    11/3/2011
  4   156 REF65798    11/11/2011
  5   181 REF65798    11/5/2011
  6   181 REF65798    11/10/2011
  </code></pre>
  
  <p>Now in my procedure for different reference no's whether any duplicate records present with same <code>Rdate</code>:</p>
  
  <pre><code>declare @Duplicate int
  
  select top 1 
       @Duplicate = count(*) 
  from 
      (select Rdate 
       from t1 
       where Reference = 'REF65798' 
         and CID in (123, 156, 181)
       order by Rdate desc) A
  group by 
      a.Rdate 
  </code></pre>
  
  <p>Here 'Reference' no and 'CID'  value change for each record (I have given it for single record) and I have to consider only latest R Date</p>
  
  <p>Above query takes lot of time when I am processing for 10000 records in loop with different validation. How can I improve above query performance.</p>