Choosing Between Triggers And Foreign Keys In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into when you might want to consider using triggers over foreign keys in SQL Server. Erik Darling from Darling Data shares insights on how cascading actions can lead to unexpected issues, especially with the new cardinality estimator. I highlight a scenario where foreign keys cause performance problems during foreign key validation and discuss potential solutions like query hints. The video also covers best practices for writing efficient triggers, including setting transaction isolation levels and using hint options to ensure optimal performance. If you’re interested in more SQL Server tips or want to support my channel, consider becoming a member by clicking the link in the description.
Full Transcript
Erik Darling here with Darling Data. And in today’s video, we’re going to talk about when you might want to consider using triggers over foreign keys in SQL Server. There are times when you may want to do this because leaving foreign keys up to their own devices can cause all sorts of weird stuff, especially when you have cascading actions involved, especially when you might care about SQL Server’s query plan choice when when cascading those actions out or even when validating foreign keys on insert, update, and delete. Of course, foreign keys and triggers are great ways to maintain referential integrity in OLTP databases. If you have foreign keys or triggers in your data warehouse, you should be dragged out into an alley and beaten with a rigid implement that I will not apologize for the length of the way that I have been doing. I have been apologizing for the length of some of my videos. If you would like to join the 20 or so other people who have been so kind as to become members, and I will not apologize for the length of my members list of this channel, you can do that by clicking the link in the video description. If for some reason you think you have something better to spend $4 a month on, you can like, you can comment, you can subscribe. You can do all sorts of nice things that make me feel less lonely when I wake up at five in the morning and look at my phone. If you need help with SQL Server, I am great at all of these things. And you know what else is great? My reasonable rates. Bam, sold. Pretty good there. Gotcha. Gotcha. You’re gonna be knocking on my door any second now. If you would like low cost, high quality SQL Server performance tuning training, that’s far cheaper than anything you will find on Black Friday. For the rest of your life, not just for a year, you can get over 24 hours of performance tuning training from me at that link with that discount code. There is also a fully formed link in the video description that you can click on without having to do any work or copying and pasting. If you would like to catch me live and in person, Seattle, you can go to Seattle. That increases your chances of your chances of seeing me by a bit. If you actually attend past data summit, you increase those chances exponentially of seeing me. And if you come to me and Kendra’s pre cons on November 4th and 5th, you are nearing a 100% certainty that you will see me. Right? Like, can’t rule anything out. Maybe I’ll like get struck by some kind of weird plasma bolt and turn invisible between now and then. But you will at least still hear my booming voice and see a floating lavalier mic going around the stage. If I turn invisible for past data summit. Well, I mean, just watch out on kilt day. We’ll have some hijinks going on. Anyway, let’s go talk about triggers versus far and keys. And the post that, of course, inspired this, because unlike some other SQL Server websites out there, I like to give credit where credit is due.
And my friend, my good friend, Forrest McDaniel, who I got to catch up with at Data Saturday Dallas, wrote this post in 2018. God, I was still in my 30s. Is this really six years old? Yeah, yeah. In like a month, this thing is like exactly six years old. Anyway, here’s a forest demo in a canute shell. You have a table called P that’s sort of like parent. You have a table called C that’s sort of like child. And you put some data in the parents and you put some data in the children’s and you add a constraint to that table. And let’s just make sure this thing is actually on there. So nothing weird happens. This is the problem that Forrest ran into. And this is a problem that I see a lot more people running into as they start flipping to higher compatibility levels and start succumbing to like Oregon Trail style succumbing to the new cardinality estimator.
So what I’m going to do is force the default with endless air quotes cardinality estimator. That’s the new one. And what you’ll see is a plan that looks like this. And this is not the kind of plan that you want to see when you are validating your foreign keys. This is a very bad plan for foreign key validation. This type of plan will generally be a lot slower than the nested loops variety that you would normally want to see here.
And this type of plan in a foreign key greatly, like you going to Seattle and going to my pre-con, how that exponentially increases your chances of seeing me. Seeing this type of plan greatly exponentially increases your odds of seeing a whole lot of deadlocks, especially if two things try to delete from these tables at the same time. One really sort of interesting, I’m not even going to call it downside, just one very interesting effect of cascading foreign keys is that under the covers they will switch to the serializable isolation level.
And you can, you know, like that’s a pretty strict one. If you come to my session at Past Data Summit about isolation levels, you will learn more about that. Really tying things in today, this is a big sales pitch for Darling Data.
This is the kind of thing you really want to avoid. Now, there are ways to fix this. But if you are like, you know, in any framework or some other ORM only shop, you might have a hard time injecting query hints into your queries.
You know, you might not be able to force a plan because of, you know, differences and stuff. It might not, just might not go well. You can fix this problem with a cardinality estimation hint like this.
Where SQL Server now, because you use the legacy cardinality estimator, SQL Server estimates joins differently, right? There’s a difference in how SQL Server looks at join cardinality between the two. That’s one of the biggest differences between them.
And now we get a nested loops join and things will be much happier as far as when you actually have to cascade deletes out. Because, you know, when you cascade a delete, you’re not just deleting from one table anymore. You are deleting from two tables.
You are deleting from up here. And then you spool a bunch of data into this thing. And then you spool a bunch of data out of this thing. And then you join that spooled data to the other table where the cascading thing lives. And then you delete from another thing.
So all in all, you are deleting from two tables. And the more indexes might be involved here, like say you have a whole bunch of indexes on both of those tables for all the different queries that you’re on, the more stuff you’ve got to delete from, the more stuff you’ve got to lock, the more problems are on.
Goes without saying. All of that stuff. So another way of fixing that is to stick a loop join hint on here. Now, what you’re going to notice if we look at these two plans together is that these are nice, thin, friendly looking lines.
And these are not so thin, friendly looking lines. The reason why is not because of anything other than the cardinality estimator still. You would still want to see this plan even in this state.
The one up there, you get a nested loops join because SQL Server does a better job with cardinality estimation in this case for that join. The bottom one, we’re forcing a loop join, but SQL Server still uses the same cardinality estimation for the default cardinality estimator. That’s why those plans look different.
Both of these use apply nested loops. There’s no prefetching in one and not in the other. There’s no optimized nested loops in one and not in the other. Everything is the same except the cardinality estimation model for that. So if we were to add option loop join and force the legacy cardinality estimator, we would see just basically a plan that looked like the force the legacy cardinality estimator one.
That one only looks different there because it’s only the loop join hint, not the cardinality estimation hint. Now, if you wanted to write triggers to replace this stuff, you would have some stuff to think about. Right.
Because, well, I know thinking isn’t your specialty. I’ve seen your queries, seen your servers, seen your schema, seen your indexes, seen a lot. I’ve seen a lot.
I know I’m like Santa Claus when it comes to SQL Server. I see everything. I see everything. And so there’s some stuff that you have to do inside of your triggers to make them work right or to make them work well or work better. A lot of that stuff comes at the very top.
For example, it’s very good to have this condition at the very beginning of your trigger so you can just bonk out if there are no rows. Right. So if someone does an insert that doesn’t actually insert anything, you would want to do this to avoid having to run anything else in the trigger.
You generally, even though exact abort is the default for triggers, I find it’s a lot safer to set no count and exact abort on here just in case any client options or client settings changes may have tinkered with this. And then you want to set row count to zero, I guess because Paul White says so. So we’re going to listen to Paul on that one.
Now, since behind the scenes, cascading foreign keys use the serializable isolation level. If you are in an environment where that sort of thing might matter, you would probably want to set the transaction isolation level inside of your trigger to serializable as well. So that you get commensurate blocking results when these things execute.
Like I said before, that can tip the scales towards things having a lot more locky and deadlocky. But, you know, this is the sort of this is the price you have to pay if you need this level of consistency in your queries. Another thing that you might have to consider is and this and this comes down to sort of the control thing that I was talking about before, where, you know, if you know, you always want to loop join for this stuff, you can stick the option loop join hint in the trigger.
And you can always get that option loop join. You can always get that loop join plan that you’re after. You can also always put a force seek hint into this part of the query so that you never have to worry about a scan happening on the inner side of anything.
Right. Not on a merge join, hash join, but because we’re only getting loops here. As long as you have a good supporting index, that loop join will always you want to force the seek in there.
Where you have to think about stuff a little bit, though, is if you are using an optimistic isolation level, you might need to add this in because otherwise you might get strange results from reading stale data. Now, Paul White, and I’ll put a link to this post in the video description as well. Paul White goes over this in one of his isolation level series blog posts, sort of explaining something similar.
But, you know, so if you’re using RCSI, you would probably want to use this so you don’t mess anything up. You could, of course, use a serializable thing, too. But most people would probably just benefit from just a regular old recommitted lock hint in there.
That would probably be good enough for most scenarios. But, you know, as always, make sure that you’re testing for your actual use case, not for what I say. That might be OK here.
I don’t always know. So, you know, I have limited insights sometimes into exactly what is going to be the best thing for you locally. But you can do that for all sorts of triggers.
This is an example with an insert trigger. This is, of course, an update trigger that does nearly the same thing. And this is a delete trigger, which would probably come pretty close to doing exactly what we just did with that foreign key with the cascading delete, where we would delete from whatever got inserted into there.
Actually, I might want to do that with the deleted table. You know. Maybe I forgot to change that one when I copied and pasted it.
We’re going to leave that one on the editing room floor, though. That’s going to survive there. And I’m going to give myself 10 demerits. I’m going to go do some push-ups.
But anyway. Let’s just change that to deleted. The deleted. There we go. And we’re going to keep that.
We’re going to keep the as I in there. And now everything looks good. So anyway. Copy-paste errors aside, I’m pretty happy with this one. Though I might have to apologize for the length since we did go a little bit over the 10-minute mark. But anyway.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you’ll forgive my copy and paste error. And I hope that you’ll continue watching despite the fact that there was an obvious oversight at the end of this video. But anyway.
Goodbye. Cruel World. Cruel World.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.