title: SQL Server silently drops a transaction after a query
body: <p>SQL Server version 14.0.1000.169.</p>
  <p>I start a transaction, execute a list of queries, but when I try to execute <code>commit</code>, I got an error <code>20018 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION</code>.
  There are no errors prior to it, and the connection is not breaking.</p>
  <p>So I tried to log every DML query, executing <code>SELECT XACT_STATE(), @@TRANCOUNT</code> before and after each execution, here is what I got:</p>
  <pre><code>    [XACT_STATE()] =&gt; 0
      [@@TRANCOUNT] =&gt; 0
  -----------------------
  BEGIN TRAN
  -----------------------
      [XACT_STATE()] =&gt; 1
      [@@TRANCOUNT] =&gt; 1
  -----------------------
  &lt;&lt;&lt;Skipped several queries that do no cause the issue&gt;&gt;&gt;
  -----------------------
      [XACT_STATE()] =&gt; 1
      [@@TRANCOUNT] =&gt; 1
  -----------------------
  SELECT unpvt.productID AS ProductID,
         t.[Index] AS ImageSequence,
         unpvt.Image AS ImageFileName
  INTO #tempImages
  FROM (
           SELECT *
           FROM tProductNewImport pi
           WHERE pi.ProductNewImportInstanceID = :ProductNewImportInstanceID
             AND pi.deleted IS NULL
             AND pi.productID IS NOT NULL
             AND pi.StoreID = :StoreID
       ) pi
           UNPIVOT (Image FOR [Column] IN (f6)) unpvt
           JOIN (VALUES (1, 'f6')) t ([Index], [Column])
                ON t.[Column] = unpvt.[Column];
  
  CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-c3975d93e8521] ON [dbo].[#tempImages]
  (
      [ProductID] ASC,
      [ImageFileName] ASC
  );
  
  
  
  DELETE tpi FROM dbo.tProductImage tpi
  JOIN #tempImages i on tpi.ProductID = i.ProductID
  WHERE tpi.StoreID = :StoreID
  AND NOT EXISTS (SELECT * FROM #tempImages i2 WHERE tpi.ProductID = i2.ProductID and tpi.ImageFileName = i2.ImageFileName);
  
  
  UPDATE tpi
  SET ImageSequence = i.ImageSequence * 10
  FROM dbo.tProductImage tpi
  JOIN #tempImages i on tpi.ProductID = i.ProductID and tpi.ImageFileName = i.ImageFileName
  WHERE tpi.StoreID = :StoreID;
  
  
  INSERT INTO tProductImage (StoreID, ProductID, ImageSequence, ImageFileName)
  SELECT :StoreID, i.ProductID, i.ImageSequence * 10, i.ImageFileName
  FROM #tempImages i
  WHERE NOT EXISTS (SELECT * FROM dbo.tProductImage tpi WHERE tpi.StoreID=:StoreID AND tpi.ProductID=i.ProductID AND tpi.ImageFileName=i.ImageFileName);
  -----------------------
      [XACT_STATE()] =&gt; 0
      [@@TRANCOUNT] =&gt; 0
  </code></pre>
  <p>The last one is a single query with several statements in it.
  The transaction disappeared after it, without any explicit 'end transaction' statements.
  My only guess is that 'create index' command somehow performs a commit, but I could not found any information about that.</p>