title: How to deploy temporal tables with dacpac and SqlPackage.exe
body: <p>We are trying to work with temporal tables in SQL Server 2016.  We are developing the SQL scripts in SSDT 15.1.6 in Visual Studio 2017, but we are experiencing issues when trying to deploy the dacpac that is generated during the build.</p>
  
  <p>Our dacpac is deployed using <code>SqlPackage.exe</code>, and we encounter this error when attempting to deploy the dacpac:</p>
  
  <blockquote>
    <p>Creating [dbo].[TestHISTORY].[ix_TestHISTORY]...<br>
    An error occurred while the batch was being executed.<br>
    Updating database (Failed)<br>
    Could not deploy package.<br>
    Error SQL72014: .Net SqlClient Data Provider:    </p>
    
    <p>Msg 1913, Level 16, State 1, Line 1<br>
    The operation failed because an index or statistics with name 'ix_TestHISTORY' already exists on table 'dbo.TestHistory'.  </p>
    
    <p>Error SQL72045: Script execution error.  The executed script:<br>
            CREATE CLUSTERED INDEX [ix_TestHISTORY]
                ON [dbo].[TestHistory]([SysStart] ASC, [SysEnd] ASC);</p>
  </blockquote>
  
  <p>When we create the temporal table in SSDT we have the following:</p>
  
  <pre><code>CREATE TABLE [dbo].[Test]
  (
      [Id] INT NOT NULL PRIMARY KEY,
      [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
      [SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
      PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
  )
  WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[dbo].[TestHISTORY], DATA_CONSISTENCY_CHECK=ON))
  </code></pre>
  
  <p>As far as I can tell the issue is with the dacpac creation. After the project is built, the dacpac created looks like this: </p>
  
  <pre><code>CREATE TABLE [dbo].[test]  
  (
      [Id]       INT NOT NULL PRIMARY KEY CLUSTERED ([Id] ASC),
      [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
      [SysEnd]   DATETIME2 (7) GENERATED ALWAYS AS ROW END   NOT NULL,
      PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
  )
  WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[testHISTORY], DATA_CONSISTENCY_CHECK=ON));
  GO
  
  CREATE TABLE [dbo].[testHISTORY] 
  (
      [Id]       INT           NOT NULL,
      [SysStart] DATETIME2 (7) NOT NULL,
      [SysEnd]   DATETIME2 (7) NOT NULL
  );
  GO
  
  CREATE CLUSTERED INDEX [ix_testHISTORY]
      ON [dbo].[testHISTORY]([SysEnd] ASC, [SysStart] ASC);
  GO
  </code></pre>
  
  <p>I suspect because we are using a <a href="https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table#creating-a-temporal-table-with-a-default-history-table" rel="noreferrer">temporal table with a default history table</a> we can't have the dacpac create those extra creation statements.  Since this is effectively causing SQL Server to try to create those items twice, leading to the above error.</p>
  
  <p>Does anyone know what we might be missing? Or if you are deploying temporal tables using a dacpac, is your only option to use <a href="https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table#creating-a-temporal-table-with-a-user-defined-history-table" rel="noreferrer">user-defined history tables</a>?</p>