Choosing Between Triggers And Foreign Keys In SQL Server

In this video, I discuss when it might be beneficial to use triggers over foreign keys in SQL Server, particularly focusing on scenarios where cascading actions and query plan choices can cause issues. I explore the challenges that arise when using foreign keys, especially at higher compatibility levels and with new cardinality estimators, leading to suboptimal execution plans for foreign key validation. To address these problems, I delve into potential solutions, including query hints like 'FORCE INDEX' and 'LOOP JOIN', which can help mitigate performance issues. Additionally, the video covers how triggers can be implemented effectively, touching on best practices such as setting transaction isolation levels, using hint options to control join behavior, and ensuring data consistency in optimistic environments. CHAPTERS 00:00:00 - Introduction 00:01:35 - The Problem with Cascading Foreign Keys 00:04:07 - Writing Triggers to Replace Cascading Foreign Keys 00:06:29 - Trigger Conditions and Best Practices 00:08:14 - Transaction Isolation Levels in Triggers 00:10:36 - Optimizing Query Plans with Hints 00:12:51 - Example of a Delete Trigger 00:13:08 - Conclusion ━━━━━━━━━━━━━━━━━━━━━━━━━━ 📚 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 ━━━━━━━━━━━━━━━━━━━━━━━━━━