title: How to implement auto-incrementing version number column?
body: <p>What is a good way to implement a field in SQL Server that is auto-incrementing based on another field in the same table? Imagine a <code>VersionNumber</code> field that is auto-incrementing <code>1</code> to <code>n</code>, <em>for each value</em> of column <code>DocumentID</code>.  </p>
  
  <p>Requirements:</p>
  
  <ul>
  <li>VersionNumber must start at 1, and not skip any numbers</li>
  <li>The VersionNumber column must be persisted. It cannot be dynamically created in a view, such as via <code>ROW_NUMBER</code>. The point of this is creating an audit trail. </li>
  <li>Ideally, direct inserts and updates of VersionNumber would be prevented</li>
  </ul>
  
  <p><strong>In Detail</strong></p>
  
  <p>I'm designing a database that requires versioned data. The basic structure for this sort of thing is to have a header table with invariant fields and a version detail table with:</p>
  
  <ul>
  <li>A foreign key to the header table</li>
  <li>A version number</li>
  <li>Fields than can change from version to version</li>
  </ul>
  
  <p>Articles such as <a href="https://www.simple-talk.com/sql/database-administration/sql-strategies-for-versioned-data/" rel="nofollow">SQL Strategies for 'Versioned' Data</a> and <a href="https://www.simple-talk.com/sql/sql-training/yet-another-sql-strategy-for-versioned-data/" rel="nofollow">Yet Another SQL Strategy for Versioned Data</a> discuss strategies for efficiently querying this type of structure, but not the actual implementation of the <code>VersionNumber</code> field (the second article provides a partial example, but leaves a fuller implementation for 'an advanced exercise').</p>
  
  <p>Example DDL:</p>
  
  <pre><code>CREATE TABLE Invoice (
      InvoiceID   bigint identity(1,1) not null primary key,
      CreateDate  date not null
  );
  
  CREATE TABLE InvoiceVersion ( 
      InvoiceVersionID  bigint identity(1,1) not null primary key,
      InvoiceID         bigint not null,
      VersionNumber     int not null
      Other Fields...
  
      CONSTRAINT FK_InvoiceVersion_Invoice
      FOREIGN KEY ( InvoiceID ) 
      REFERENCES Invoice ( InvoiceID )
  );
  
  CREATE UNIQUE INDEX UQ_InvoiceVersion
      ON InvoiceVersion ( InvoiceID, VersionNumber )
  ;        
  </code></pre>
  
  <p>My partial solution addresses incrementing the version number on insert, but does not prevent selective deletes of specific versions, or prevent direct updates of <code>VersionNumber</code>. I'd love it, though, if all this didn't require three 50-line triggers.</p>
  
  <pre><code>CREATE TRIGGER TRG_InvoiceVersion_Insert
      ON InvoiceVersion
  INSTEAD OF INSERT
  AS
  BEGIN
      -- Prevent explicit VersionNumber insert
      DECLARE @ExplicitVersionCount int;
  
      SELECT @ExplicitVersionCount = COUNT(*)
      FROM inserted
      WHERE VersionNumber IS NOT NULL;
  
      IF (@ExplicitVersionCount &gt; 0) BEGIN
          RAISERROR ('Explicit version number insert not allowed', 15, 1);
      END
  
      -- Get the current version number (implicit in row count)
      DECLARE @InsertingInvoiceID TABLE (
          InvoiceID bigint not null primary key 
      );
  
      DECLARE @CurrentVersions TABLE (
          InvoiceID bigint not null primary key,
          VersionCount int
      );
  
      INSERT INTO @InsertingInvoiceID ( InvoiceID )
      SELECT DISTINCT i.InvoiceID 
      FROM inserted i;
  
      INSERT INTO @CurrentVersions ( InvoiceID, VersionCount )
      SELECT I.InvoiceID, COUNT(V.InvoiceID)
      FROM @InsertingInvoiceID I
          LEFT JOIN migrate.InvoiceVersion V 
              ON V.InvoiceID = I.InvoiceID
      GROUP BY I.InvoiceID
      ;
  
      INSERT INTO InvoiceVersion (
          InvoiceID
        , VersionNumber
        , [Other fields...]
      )
      SELECT
          i.InvoiceID
        , v.VersionCount + ROW_NUMBER() OVER (PARTITION BY i.InvoiceID ORDER BY i.InvoiceID)
        , [Other fields...]
      FROM inserted i
          INNER JOIN @CurrentVersions v
              ON i.InvoiceID = v.InvoiceID
      ;
  END;
  </code></pre>