All About SQL Server Stored Procedures: Nested and Autonomous Transactions

In this video, I delve into the intricacies of nested transactions in SQL Server, addressing a request from viewers who wanted to understand how these work and if they can be achieved. I explain that while SQL Server behaves differently compared to other database engines like Oracle when it comes to nested transactions, you can still achieve some aspects of an autonomous transaction using a loopback linked server. However, I caution against the complexity and potential issues involved in this approach. The video demonstrates creating a simple table and stored procedure to illustrate how transactions work in SQL Server, showing that while you can commit changes within a transaction, rolling back an outer transaction will undo any inner transaction commits. If you're interested in learning more about this topic or supporting my channel, I invite you to become a paying member for as little as $4 a month, allowing me to continue creating content and answering your SQL Server questions during my Office Hours episodes. CHAPTERS 00:00:00 - Introduction 00:00:29 - About the Video Content 00:01:13 - Nested Transactions Overview 00:03:54 - Autonomous Transactions Concept 00:05:38 - SQL Server Transaction Behavior 00:07:13 - Store Procedure Example 00:09:15 - Least Painful Method ━━━━━━━━━━━━━━━━━━━━━━━━━━ 📚 TRAINING & COURSES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Get AI-Ready With Erik https://training.erikdarling.com/get-ai-ready-with-erik?coupon=AIREADY SQL Server Performance Engineering Course https://training.erikdarling.com/sql-server-performance-engineering?coupon=ENGINEERING Learn T-SQL with Erik https://training.erikdarling.com/learn-t-sql-with-erik?coupon=ADVANCEDTSQL Everything Bundle: https://training.erikdarling.com/?coupon=SPRINGCLEANING ━━━━━━━━━━━━━━━━━━━━━━━━━━ 🛠️ CONSULTING & SERVICES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Need SQL Server performance help? https://training.erikdarling.com/sqlconsulting ━━━━━━━━━━━━━━━━━━━━━━━━━━ 💬 CONNECT ━━━━━━━━━━━━━━━━━━━━━━━━━━ Ask questions at Office Hours https://erikdarling.com/officehours/ Become a channel member https://www.youtube.com/@ErikDarlingData/join ━━━━━━━━━━━━━━━━━━━━━━━━━━