A Foreign Key Join Elimination Limitation In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into a peculiar limitation involving foreign keys and join elimination in SQL Server, which can be quite frustrating when it occurs. I share my thoughts on why foreign keys are generally useful for ensuring referential integrity but caution against using cascading foreign keys due to the underlying serializable isolation level that can slow down operations. The video also touches on how SQL Server behaves differently depending on whether you’re checking if data exists or non-existent data, leading to unnecessary joins even when a foreign key should guarantee otherwise. If you found this topic interesting or have any questions, feel free to like, comment, and subscribe for more content.
Full Transcript
Erik Darling here with Darling Data. You know, it’s cool when you and your logo are both handsome. See, I see a lot of consulting companies out there where neither the logo nor the consultant are handsome. So really getting the total package from me is worth every single penny. In this video, we’re going to be talking about a really annoying, I guess, maybe the plural, maybe that S shouldn’t be there. It’s a sort of an annoying limitation with foreign keys and joint elimination. Now, the first thing I want to say about foreign key joint elimination is that it happens under such limited circumstances anyway that you should stop trying to meme people into using foreign keys by saying it’s a thing. Like, it’s… it happens so rarely and so rarely in useful circumstances. I don’t understand the point of even bringing it up. It’s almost like when people talk about other facilities in SQL Server with this, like, like, like, meme white knighting of what makes… why they’re okay when they’re… they kind of suck.
Things like, you know, like, you know, like, you know, like, you know, CTE being more readable, table variables being only in memory, things like that. Things like that. Things like that. Okay, sure. Yeah, you read a LinkedIn post where someone said that and now you’re just gonna ride and die with that. Okay. All right. Fine. Fine. Foreign key joint elimination is right up there with that. Now, foreign keys and SQL Server do work as long as they’re not, you know, disabled.
And as long as they’ve been re-enabled correctly, they will totally, you know, make sure that you have referential integrity between two tables. They will make sure that rows exist or, you know, are present in one table that have to be present in another table. It’s fine. They do that. They also, you know, you know, if you’re… I mean, God, don’t put them in a data warehouse. One thing I gotta say up front is data warehouses are not the place for these things.
Your OLTP data is the place for these things. And your data warehouse should just follow whatever, you know, ETL processes are necessary to take, to, you know, honor whatever uniqueness or referential integrity or other constraints exist in your relational data, your OLTP data. Your data warehouse should just be a big, beautiful, massive columnstore indexes where you don’t. Mess with things that slow down data loads.
All right. You put those things in your data warehouse, you’re asking for things to be slow. Take care of them somewhere else. Do not, do not put these things in your data warehouse. It’s stupid. Stupid, stupid, stupid, stupid.
Anyway, if you like this channel and you would like to support this channel with money, you can do that. I have very, very low cost member sponsorship options. I have a hair on my neck somewhere. It feels very strange. If you are unable to participate in the money thing, other things that make my heart go pitter patter are likes and comments and subscribes.
You can hear it from here. That might be arrhythmia. I don’t know. It’s hard to tell. It’s a reason I take a lot of blood pressure medication. All right. If you need SQL Server consulting help, these are the things that I usually help my clients with.
If you need something else, we can negotiate. But if you need any of this stuff, I can pretty much do this right out of the box for you. If you need some SQL Server performance tuning training, I have a very low cost option where you can get about 24 hours of it for just about $150 US if you use that discount code over there.
I’m not sure where my finger, ah, there goes the finger. But there’s a link to get that coupon code applied directly for you down in the video description. I will be out in the world. I will be out in the world speaking to real live people on two dates in the near future.
Friday, September 6th, I will be at Data Saturday Dallas doing a full day pre-con. I also have a couple of regular sessions on September 7th. Then November 4th and 5th, I will be at Past Data Summit doing a big old double team high five with Kendra Little on two days of pre-cons there.
So all great things to show up to and all great events to support with your very presence, your mere presence. You can support me and countless other speakers who show up and organizers who put these things together. And it’s a nice thing to do for the data community because you are part of the data community, aren’t you?
You’re one of us. You’ve been bored in, right? So let’s get on and talk about something that really irks me with far and keys in SQL Server. And I don’t know why this thing gets so tiny when I close out.
It doesn’t look that small normally, so it’s a little strange. Alright, so let’s start by setting things up. Alright, we are going to drop some tables if they exist, and they did up until a minute ago.
They were there. They were live and present. And then we are going to create two tables. One called, I forget why I named it this.
Maybe I was just mad at Connecticut that day. Because, you know, that kind of speaks for itself. And then both of these tables have clustered primary keys on this column called ID, obviously.
And they have two date columns that we are not going to really do much with. And then, I think I already did this. I think this was part of the initial highlight. Yep, I already did that. Good for me.
SQL Server is working correctly. It said that foreign key already exists. So we’ve created a foreign key on CT that references the ID column. Or, sorry, on the ID column in the CT table that references the ID column in the CT underscore FK table.
Alright, so we have that there. And if we do a little background check on our foreign key, we will see that it is present in the database. It is not disabled.
And it is not not trusted. It’s the old double negative here. Whatever Microsoft person decided to phrase it this way, I wish that I could spend some time with you where there are no video cameras and no other recording devices. Because why the hell would you do this?
Why would you just not call it is trusted? Why is it is not not trusted? Now we have to say is it not not trusted or is it is trust trusted? You know?
Is you is or is you ain’t? We don’t know. But that’s a zero. So it is trusted or not not trusted. It’s great news for us. Now, this is where things get funny for me.
If we run this query and we say, hey, does stuff in the CT table exist in the FK table? Is it there? Is you is or is you ain’t?
Well, in this query plan, you’ll notice that we only have to touch one table. Right? Even though we say, hey, there’s stuff in here. SQL Server says, we know it’s there.
We got you. It’s fine. Don’t worry. Don’t sweat it. We’re good. We’re fine. We don’t need to go check over there. It’s obviously there.
It’s a foreign key. Foreign key is enabled and not not not on is is untrust untrust. It’s very trustworthy foreign key. Okay?
Let’s leave it at that. It’s very trustworthy. But now when we say, hey, SQL Server, is there anything here that that’s not not in that table? SQL Server, SQL Server loses some of its confidence.
It loses some of its moxie. Moxie, someone someone knocked the cool right out of SQL Server’s walk because now we end up having to check both tables to figure out if stuff’s there or not. SQL Server all of a sudden does not trust.
Lozen does not trust that foreign key so much anymore, does it? We no longer eliminate that join. And that’s a very strange thing to happen because just a second ago when we asked if stuff was there, SQL Server was very sure of itself. Cock sure, you might even say.
And now now we say, is anything not there? Well, who can tell? We have to go check both tables to figure it out. So this is one of those dumb things.
And this actually sort of aligns with a video that I did. Well, at this point it may have been published two or three days as of when you see this. But it almost kind of goes back to the video I did about what annoys me about computed columns and filtered indexes where if you don’t ask the exact right question in the exact right order, it’s all of a sudden SQL Server’s like, I don’t know you.
I can’t use that filtered index. I can’t use that computed column. Can’t do that. Nope. Nope.
You didn’t do things right. Can’t use it. Which is pretty ridiculous, right? And so if you haven’t seen that video, you should go watch that video to see another way in which SQL Server is utterly, patently ridiculous. All right.
So this is a short one because, you know, I don’t want to spend too much time picking on foreign keys. They are useful for ensuring referential integrity. I hate saying those words.
I guess if I had, if I had to, you know, one more word of wisdom slash caution with them is, if you’re, if you’re the type of person who gets these grand ideas in their head about using cascading foreign keys, just be very careful because cascading foreign keys behind the scenes use a serializable isolation level to make sure that everything maintains that referential integrity. So when you update or delete and that cascading foreign, or that foreign key action cascades out, you’re using the, behind the scenes SQL Server is like, nope, serializable.
Which is another great reason to make sure that your foreign keys are well supported by indexes because you don’t want that going on for a very long time. That can be quite a misery. So anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I hope you find this troublesome and annoying the way I do. Uh, if you remember all the stuff that I said about the liking, the subscribing, training, consulting, great.
If not, you can rewind and get, get reacquainted with all that, that, that vast expanse of knowledge. Anyway, um, I think that’s about it for this one. I have, I have some other videos that I’m going to record.
Uh, you can, you might, you might be able to guess some of the topics by looking at some of the tab names up here. Some of the tab names are none of your business. Don’t look at those.
Some of them will be in, be in upcoming videos. So it’ll be, it’ll be a surprise to you, but not to me. It might, some of the ones that don’t show up might be, might be a disappointment to you, but. All in good time, my friends, all in good time.
Anyway, uh, thank you for watching. Goodbye.
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.