0

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. – SqlWorldWide Jul 3 '17 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 Jul 4 '17 at 8:28
1

Ref: https://docs.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, privacy policy and cookie policy

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