title: Incrementing a counter based on a partition
body: <p>Suppose I have a table that looks a little like this in SQL Server 2008:</p>
  
  <pre><code>UID    Std_RecordID
  -------------------
  1      10
  2      10
  3      12
  4      10
  5      10
  6      10
  7      12
  </code></pre>
  
  <p>Basically there will be a sequence of <code>10</code>s followed by a single <code>12</code>. The <code>10</code> could also be in the list <code>14, 50, 21, 24, 31</code> and the <code>12</code> could be in the list <code>16, 52, 23, 26, 33</code> respectively. An increment of 2 pretty much signifies the end of a set.</p>
  
  <p>I need to increment a counter every time there is a new set.</p>
  
  <p>I know I can get a counter to increment and reset like so:</p>
  
  <pre><code>select ROW_NUMBER() over (partition by Std_RecordId order by UID) 'Ind'
  ,UID
  from @inputTable
  </code></pre>
  
  <p>But this isn't quite what I want given that it will produce the following:</p>
  
  <pre><code>UID    Std_RecordId    Ind
  ---------------------------
  1      10              1
  2      10              2
  3      12              1
  4      10              1
  5      10              2
  6      10              3
  7      12              1
  </code></pre>
  
  <p>I need it to do something like this instead:</p>
  
  <pre><code>UID    Std_RecordId    Ind
  ------------------------------
  1      10              1
  2      10              1
  3      12              1
  4      10              2
  5      10              2
  6      10              2
  7      12              2
  </code></pre>
  
  <p>How does one go about accomplishing this without resorting to iteration? I'm trying to get rid of the iteration in the procedure I'm working in since the iteration is the slowest part of the procedure (it does loads of other things other than this).</p>