title: SQL add new editable (Not Identity) column with incremented values
body: <p>I have a SQL Server table and I want to add a new column to my table that will be used for custom ordering. Not an identity column, because I need to be able to change the values after creation.</p>
  
  <p>My table is </p>
  
  <pre><code>CREATE TABLE [dbo].[Ponies] (
      [Name] [nvarchar](50) NOT NULL
  )
  </code></pre>
  
  <p>With data like <code>[{Name: Rarity},{Name: Applejack}]</code>. </p>
  
  <p>I want my table would become like</p>
  
  <pre><code>CREATE TABLE [dbo].[Ponies] (
      [Name] [nvarchar](50) NOT NULL,
      [IndexNumber] [int] NOT NULL
  )
  </code></pre>
  
  <p>And I want the data to be like <code>[{Name: Rarity, IndexNumber:1},{Name: Applejack, IndexNumber:2}]</code> after the alter statement. The point is that the order of the rows can be changed after creation, meaning that when new rows are added the order of the old ones can be changed.</p>
  
  <p>How could I do the <code>ALTER</code> statement so that an incremental <code>IndexNumber</code> would be added for each existing data item?</p>