A Bug With Inline Index Creation Syntax In SQL Server
Thanks for watching!
Video Summary
In this video, I share a lighthearted look at inline index creation bugs in SQL Server, which have persisted even through updates to SQL Server 2022. This quirky issue, which I reported long ago and still exists today, adds an amusing twist to my usual technical content. As always, if you enjoy the channel and are looking for more in-depth SQL Server performance tuning, health checks, or training, there’s a special offer available: 24 hours of expert-level content for just $150 when purchased at 75% off. Plus, I’ll be teaching a full-day pre-con on Friday, September 6th, and hosting two days of SQL Server performance tuning content at Data Saturday Dallas and the Past Data Summit in Seattle. So, whether you’re looking to improve your skills or just want to see me in person, there are plenty of opportunities coming up!
Full Transcript
Erik Darling here with Darling Data. And, um, well, this video is gonna be, this is an easier one, cuz I’ve scheduled this for a Friday. I’m not recording this on a Friday. But, uh, in the order that I’m recording videos today, this is also my Friday of video recording for the day. So, I would also like an easy one, where I don’t know if I’m recording videos, I don’t have to talk a lot about anything particularly in-depth. Um, much like on a real Friday, even though I believe, even though, even though today is Monday, and this is going out on a Friday, I am exhausted. I’m not gonna say anything too goofy about Mondays, or coffee, or alarm clocks, or any of that stuff, because, uh, I don’t know.
Don’t don’t wear shorts, as they say. So, let’s talk about inline index creation bugs in SQL Server, cuz this one’s kind of funny, because I reported this a long time ago, and no one has done anything about it. It exists to this day in SQL Server 2022. Uh, I don’t know. Apparently, the summer interns were busy with other things, so. Whew. Let’s have ourselves a time. Uh, if you like my channel, and you have gotten sick of just the mere act of liking and commenting, and you’ve, you’ve, you’ve, like, subscribed all of your, you know, primary and alt YouTube accounts, uh, one, one way that you can support the channel is to sign up for a membership. There is a link to do that in the description of the video, along with links to other things, uh, that you might find useful. I don’t know. Maybe you will, maybe you won’t. Uh, it all depends on how you click them, I guess.
Uh, if you need help with your SQL Server, if it is in bad shape, uh, if performance is not good, or things are going awful for some other reason, I’m pretty good at fixing it. Uh, health checks, uh, health checks, performance analysis, hands-on tuning, dealing with emergencies, uh, training your developers so I don’t have to deal with emergencies anymore. Uh, uh, these are all things that I excel at. Um, I already made an Excel joke in the video, so I’m not gonna redo it here.
I mean, you, you deserve better than that. I’m not gonna go there. Don don’t wear shorts. Uh, so, um, yeah, my rates are reasonable. As always. Uh, if you want some very reasonably rated, uh, performance tuning content at the beginner, intermediate, or advanced, slash expert, slash crazy cuckoo brains, uh, I can’t believe you’re gonna do that with SQL Server level of training.
You can get all mine. It’s 24 hours of content for life for 75% off, which brings it to about $150 US dollars. If you’d like to see me in person, this is where I’ll be, uh, on a Friday just like this one. September the 6th, I will be at Data Saturday Dallas doing all sorts of Data Saturday things.
Uh, but on a Friday too, because, uh, Friday I have to teach a full-day pre-con. And then Saturday will be the 7th, and that’ll be Data Saturday Dallas, but it’s all, it’s, it’s all combined in there. And of course, November 4th and 5th, my, my birthdays, I will be at Past Data Summit in Seattle co-hosting two.
Bang em up, smash em up, hit em up. Days of SQL Server performance tuning content with the lovely and brilliant Kendra Little. So, um, you should come see me at both of those.
Now, without further ado, let’s repartee. Alright. Now I gotta get my head under the party hat. Just in case this is the shot that YouTube picks for like the, the splash image for my video.
This, I gotta get my head under the party hat. Make sure that’s lined up. I really should find a party hat that maybe looks like it’s on a head already.
I don’t know. I’m probably too lazy for that. We’re just gonna have fun now. Alright. So, here’s the bug as it exists. And has existed ever since inline index creation syntax was created.
Uh, now I’ve, this is sort of fitting because I did recently write a video about complaining that you can’t create a filtered index on a, or can’t create a filtered index on a computed column, something like that. One of those videos wild time, obviously very memorable experience for all.
Uh, where if you create a video, uh, create a video, if you create a table. Oh, dear me. If you create a table that has a computed column in it like this one, and it doesn’t matter if you persist it or not.
Uh, if you try to do this, uh, SQL Server will say, you cannot do that. You cannot do that with that column. You are wrong to even try.
This red text signifies your idiocy in the matter. Your, your, your idiocy in the matter and your illiteracy in reading the documentation. Uh, we can, we can prove that doubly by adding the persisted keyword and starting this whole tragic experiment over again. And trying to do this and being met with the same level of resistance from SQL Server where we cannot create that thing.
But this is where things get real wild. If we create our table with this definition, right? We’re gonna, we’re gonna drop the table if it exists.
We’re going to create the same columns right here. Uh, and it doesn’t matter if this one’s persisted or not yet again. But we’re gonna inline the index creation syntax right here like this.
Right? See what we’re doing? Very sneaky, very tricky. And if we create this table, this, this, this completes successfully.
Qua? How dare you? What, what’s gonna happen next? You know what’s gonna happen next? Errors.
Errors happen next. If I run this query, and I don’t even call, I don’t call the crap column. Uh, I don’t do anything where the definition of the, the crap column would be expanded. Uh, and I run this, I get these errors.
Invalid column name crap. Over and over again. Even though I’m not selecting crap, I’m just selecting ID. And then down here, he says, cannot retrieve table data for the query operation because the table dbo.ohyeah schema is being altered too frequently.
Because the table dbo.ohyeah contains a filtered index or filtered statistic, changes to this table… Oh, I’m sorry. This is a long error message. Bring that down a little bit.
There we go. Get in there, baby. Hey, yeah. Contains a filtered index or filtered statistics, changes to the table schema require a refresh of all table data, retry the query operation, not retrying. And if the problem persists, use SQL Server Profiler.
Profiler? Hmm. Use SQL Server Profiler, you say, to identify what schema altering operations are occurring. Even if you just try to get a simple count of what’s in the table, SQL Server will spittoon that same error message at you, and you will get all of that same awfulness in red text on the screen.
Isn’t that wild? Isn’t that a weird bug? And just when you thought you were a bad developer…
Someone got paid a lot of money for that. A lot of money. Someone is getting paid for that to this day.
Someone is on the payroll. For that. Someone… Someone got a generous grant of Microsoft Stock. For that.
Someone’s probably on a boat somewhere. Named Microsoft Stock because of that. Now, well. What can you do? Anyway.
It’s Friday. So am I. So are you. So are we. So let’s… Let’s get out of here. Let’s go do something better with our lives, right? Even though I’m just pretending it’s Friday and it’s Monday, this is…
This is the end of my recording days. So… You have no idea how relieved I am. Anyway.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something like you’re maybe not that bad of a developer. I hope that you will like and subscribe and comment and membership and training and consulting me. So I don’t get lonely.
Anyway. There we go. I think… That Microsoft should give me a generous grant for finding all the bugs that I do in SQL Server. I get…
There’s no glory in reporting them. Aside from making… Getting… Sometimes minor improvements made to their product. But sometimes they linger on for years and no one cares to do anything. So…
You know. You gotta take it as it comes, I guess. I don’t know. I’m gonna go take a margarita as it comes. Which is without triple sec or coin trope. Because those are gross.
They shouldn’t be in your margarita. They make your teeth feel like they’re wearing sweaters. To know better. You’re a grown up.
Anyway. That’s good for now. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.