title: SQL server row-number to tag duplicate rows
body: <p>I am trying to add a row number to duplicate records.</p>
  
  <pre><code>declare @tbl table
  (col1 varchar(10),col2 varchar(10) )
  
  insert into @tbl values ('a1','b1')
  
  insert into @tbl  values('a1','b1')
  
  insert into @tbl values ('a1','b2')
  
  
  insert into @tbl  values('a2','b2')
  
  insert into @tbl  values('a2','b2')
  insert into @tbl  values('a2','b2')
  insert into @tbl  values('a3','b3')
  insert into @tbl  values('a3','b3')
  
  
  select col1,col2,ROW_NUMBER() OVER(ORDER BY col1,col2 ) AS id from @tbl
  </code></pre>
  
  <p>The below is the expected result</p>
  
  <pre><code>declare @tbl1 table
  (col1 varchar(10),col2 varchar(10),id int )
  
  insert into @tbl1 values ('a1','b1',1)
  
  insert into @tbl1  values('a1','b1',2)
  
  insert into @tbl1 values ('a1','b2',1)
  
  
  insert into @tbl1  values('a2','b2',1)
  
  insert into @tbl1  values('a2','b2',2)
  insert into @tbl1  values('a2','b2',3)
  insert into @tbl1  values('a3','b3',1)
  insert into @tbl1 values('a3','b3',2)
  
  select * from @tbl1
  </code></pre>