Azure SQL Data Warehouse has limits on the maximum size of a transaction (as documented here). As CTAS operations cannot be performed inside a transaction, I was wondering if these limits still applied?

  • If you can keep your CTAS operation as minimally logged then it is not bounded with that restriction. See this. Commented Jul 3, 2017 at 17:12
  • @SqlWorldWide that was exactly what I was looking for! would you like to make it an answer for internet points?
    – Neil P
    Commented Jul 4, 2017 at 8:28

1 Answer 1


Ref: https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-best-practices-transactions

The transaction safety limits only apply to fully logged operations. CTAS is capable of being minimally logged. So answer depends, if CTAS is minimally logged safety limit does not apply and vice versa.

Unlike fully logged operations, which use the transaction log to keep track of every row change, minimally logged operations keep track of extent allocations and meta-data changes only. Therefore, minimal logging involves logging only the information that is required to rollback the transaction in the event of a failure or an explicit request (ROLLBACK TRAN). As much less information is tracked in the transaction log, a minimally logged operation performs better than a similarly sized fully logged operation. Furthermore, because fewer writes go the transaction log, a much smaller amount of log data is generated and so is more I/O efficient.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.