title: Use a sequence to populate multiple columns with successive values
body: <p><strong>How can I use default constraints, triggers, or some other mechanism to automatically insert multiple successive values from a sequence into multiple columns on the same row of a table?</strong></p>
  
  <p>A standard use of a sequence in SQL Server is to combine it with default constraints on multiple tables to essentially get a cross-table identity. See for example the section "C. Using a Sequence Number in Multiple Tables" in the Microsoft documentation article "<a href="https://msdn.microsoft.com/en-us/library/ff878058.aspx" rel="nofollow">Sequence Numbers</a>".</p>
  
  <p>This works great if you only want to get a single value from the sequence for each row inserted. But <em>sometimes</em> I want to get multiple successive values. So theoretically I would create a sequence and table like this:</p>
  
  <pre><code>CREATE SEQUENCE DocumentationIDs;
  
  CREATE TABLE Product
      (
        ProductID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY
      , ProductName NVARCHAR(100) NOT NULL
      , MarketingDocumentationID BIGINT NOT NULL DEFAULT ( NEXT VALUE FOR DocumentationIDs )
      , TechnicalDocumentationID BIGINT NOT NULL DEFAULT ( NEXT VALUE FOR DocumentationIDs )
      , InternalDocumentationID BIGINT NOT NULL DEFAULT ( NEXT VALUE FOR DocumentationIDs )
      );
  </code></pre>
  
  <p>Unfortunately this will insert the <strong>same value</strong> in all three columns. This is <a href="https://msdn.microsoft.com/en-us/library/ff878058.aspx#Anchor_1" rel="nofollow">by design</a>:</p>
  
  <blockquote>
    <p>If there are multiple instances of the NEXT VALUE FOR function specifying the same sequence generator within a single Transact-SQL statement, all those instances return the same value for a given row processed by that Transact-SQL statement. This behavior is consistent with the ANSI standard.</p>
  </blockquote>
  
  <p><strong>Increment by hack</strong></p>
  
  <p>The only <a href="http://bidn.com/blogs/KathiKellenberger/sql-server/2816/using-the-sequence-object-to-populate-two-columns" rel="nofollow">suggestion</a> I could find online was to use a hack where you have the sequence increment by the number of columns you need to insert (three in my contrived example) and manually add to the <code>NEXT VALUE FOR</code> function in the default constraint:</p>
  
  <pre><code>CREATE SEQUENCE DocumentationIDs START WITH 1 INCREMENT BY 3;
  
  CREATE TABLE Product
      (
        ProductID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY
      , ProductName NVARCHAR(100) NOT NULL
      , MarketingDocumentationID BIGINT NOT NULL DEFAULT ( NEXT VALUE FOR DocumentationIDs )
      , TechnicalDocumentationID BIGINT NOT NULL DEFAULT ( ( NEXT VALUE FOR DocumentationIDs ) + 1 )
      , InternalDocumentationID BIGINT NOT NULL DEFAULT ( ( NEXT VALUE FOR DocumentationIDs ) + 2 )
      )
  </code></pre>
  
  <p>This does <strong>not</strong> work for me because not all tables using my sequence require the same number of values. </p>