Learn T-SQL With Erik: Partitioned Views

Erik Darling here with Darling Data. And we’re going to finish off this Friday by talking about partitioned views. And look, there are a lot of things I could say about partitioned views that are great and grand and that have come in handy for me over the years in ways that I’m like, wow, thank you partitioned views. Thank you for not being normal table partitioning. Thank you for existing. But mostly in this video, I just want to kind of go over a couple of things that, you know, are not fun about partitioned views. I want to show you some things that are fun, but some things that are not fun are things like, you know, like the setup of them is like a bit arduous. And if you want your partitioned views to be writable, good luck. It’s a hard road. And I’m going to talk about some of the things that disallow a partitioned view from being writable as well. So that’s our lesson from today. Sorry for pausing on the big reveal there. Apparently, I just forgot to click the mouse. Anyway, down in the video description, if in case you were unaware, down in the video description, you will find all sorts of helpful links.

You can hire me for consulting, you can purchase my training, and you can become a supporting member of the channel. If you don’t feel like giving me any money, well, I don’t like it, but what can I do from here? I can’t exactly punch through a screen here. You can always ask me office hours questions for free. That is a free activity you can partake in. If you would rather hear me answer a question than some robot, I suppose that’s as good a way to do it as any.

Of course, if you enjoy the high-quality, flawless, unmatched content that I produce on this SQL Server channel, the most important SQL Server content that you will ever see in your life, please do like, subscribe, and tell a friend. And continuing in the tradition of you not feeling like giving me money, if you would like to stay on that path, if you’d like to stay that course, you can download my free SQL Server performance monitor. It is wonderfully free. I make the same amount of money whether you use it or not.

So, like, okay, do or don’t. It’s up to you. But it is a replacement for paid commercial SQL Server monitoring tools in many cases, in many ways. And, you know, it just looks at all the stuff that I would look at if I were, you know, coming in to look at your SQL Server as a consultant.

Logs at all the tables, gives you pretty charts and graphs, tells you all sorts of things about your data, allows to get you some root cause troubleshooting stuff done. And if you feel like having the robots do the work for you, it’s got MCP tools built in so that your robots have read-only, well-defined access to the monitoring data.

And they can usually make better sense of that than if you were to just let them loose on a server running DMV queries there and about. Anyway, if you do not prefer the robots, if you do not want to have an MCP analysis of your life, you can come see me out in the world. I will be going places, doing things, talking about SQL Server as long as it’s still alive.

There’s something weird on my shirt that I can’t get rid of. I don’t know what it is. It might just be the light. This might just be an old shirt. It’s hard to tell. Anyway, I will be in Chicago May 7th and 8th for Pass on Tour, the east of the west, I guess that is.

I will also be at SQL Day Poland May 11th through 13th. That’s looking up to be a great conference. I can’t wait to see the Dwarves of Vroslav. I keep hoping I say that right.

And then I will be home for a little bit and then over at Data Saturday, Croatia, June 12th and 13th. Lovely time of year for Croatia, I hear. Distinct lack of dwarf statues around the city of Zagreb, but that’s okay because I’m sure I’ll find other things to be amused by in such a lovely place.

And then, of course, November 9th through 11th, I will be at Pass Data Summit in Seattle, Washington. So these are all things that cost money, so come see me so I can make some money back. Anyway, for now, it is March. No, wait, it is April 1st, right?

Yeah, it is April 1st. Well, today I’m recording this on April 1st. I’m publishing this far down the line because, crap. Ah, the magic is ruined for you, isn’t it?

Anyway, let’s talk about partition views. So this is the first thing, is the arduousness of setting them up, right? So, like, I’m just doing this yearly for the votes table in Stack Overflow 2013. So I’ve got, like, 2008, 9, 10, 11, 12, like, this is my preamble stuff.

But then, like, you’ve got to create a table for each one of these things. And one thing that’s very, very important to do on table create is make sure that whatever your partitioning element is, SQL Server has a valid way of eliminating certain, like, partitioned table elements from your query plans.

It will not take the hint otherwise. So here I’m saying, look, SQL Server creation date is going to be between these two dates, or betwixt, I guess. Why don’t we have a betwixt keyword that just says, that acts like greater than, equal to, and less than?

That would be nice. That would be a good extension. Connor Cunningham, if you’re out there listening, we’re going to have between, which is going to stay the same, and we’re going to have betwixt, which is going to do that, all right?

Write it down. All right, so you’ve got to do that for each table, right? You’ve got to give SQL Server a way to know what data is going to be in what table so that, you know, like, if you want to get partition elimination, you can do that, all right?

And then you have to create a view, and you have to list your columns. Now, it is kind of cool where, like, if you do this, and you have, like, different columns and different tables, you can make that work with just, like, placeholders.

So that’s fine, too. Just be careful with null, because I learned from Kendra Little that SQL Server, if you put, like, some column equals null into a view, or if you, like, select null into a temp table, SQL Server defaults to calling it an integer.

So that might, you might find that disagreeable. So make sure that you are strict with your data types if you’re expecting a different one, other than integer for your placeholder columns there. Anyway, after you create your view, then you have to get your data into these various tables.

That’s all quite boring stuff. And if you’re wondering why I created my tables as heaps, it’s because I’m going to start with these tables as unique, having unique clustered indexes on them.

And I’m doing that for a reason that we will expand upon in a moment. So if you want your partition views to be writable, there are some prerequisites.

You need to have a unique clustered primary key. This is why I’m starting with just a unique clustered index, because it must be a unique primary key.

It must be a clustered primary key. That includes the partitioning column. You need non-overlapping check constraints on a partitioning column, which means there are no gaps, no overlaps.

You need identical schema across all member tables, same column names, types, and nullability. You need, they all need to be in the same database. You cannot do that across, across databases.

If you want your partition view to be writable, that would just be insane. And you must use the union all syntax, not union.

There are some limitations as well. Like you can’t have an identity column in any member table. You must strip these tables of their identity-ness. You can’t have a computed column as a partitioning column.

And you can’t use any timestamp columns. That makes the view non-updatable. So screw you timestamp columns anyway. Constraints?

Well, you can’t have default. Well, yeah. So you can’t have default constraints. You can’t have cascading foreign keys. And you cannot put triggers on member tables. You can only put instead of triggers on the view.

You can’t use the default keyword or an insert or update. So that’s fun. You must provide all columns and insert statements.

You can’t do bulk operations like bulk insert or BCP. And you can’t have any self-joins with the view or member tables when you are, if you want this view to be writable. So like if you were trying to do like, oh, I need to put data in here that doesn’t exist.

And you like write, ah, insert, blah, blah. Where not exist, select, blah, blah. That SQL Server will say no. There are all sorts of limitations around data types like XML and other lobs.

User-defined types, CLR types, not supported. Not that anyone uses, though. That’s insane. And some other like just weird random trivia stuff. No full-text indexes, no indexed views.

Check constraints must be trusted and enabled. And all member tables must have the same number of columns. So if like some of the flexibility that you get with indexed views is reduced quite significantly if you want that partitioned, sorry, partitioned views is lost a bit if you want that partitioned view to be writable, not indexed views.

Though those have many restrictions as well that I find unfortunate. But if we look at execution plans, and I realize this might not be connected to anything, but that’s okay.

We’ll get there. Ah, see, I knew something weird would happen. There we go. Anyway, it ran. It worked. All right, let’s give that a second run just to make sure. All right, so we’ve hit.

You can see that we are touching our partition view here. And this partition view is all those tables. But our query plan only shows one table being hit, right? Because we’re only looking at 2010 and 2011.

And because I went and created all those check constraints across all of my, all the tables in the indexed view, SQL Server knows precisely where to go. Right?

And if we do the same thing with a multi-year query, then we will see that we hit more than one table, but we did not touch all of the tables. We just hit the necessary ones, which were 2010, 11, and 12. So SQL Server’s optimizer can be very smart with how it directs queries to index views, even if those queries are parameterized, right?

So if we look, this is a store procedure, which accepts a start date and end date, and you can probably guess where they end up. This thing basically just runs this query, right?

We run that, and we’ll see in the execution plan that some of these lines actually have things, actually have data flowing through them, like 2010, 11, and 12, but the rest of them do not.

So this is sort of dynamic partition elimination, right? So if I change, well, you know what, if I change this to 2012.01.01 and 2013.01.01, and we run this, the execution plan, you’ll see that SQL Server is able to dynamically just get us to the 2012 table.

So the optimizer is pretty good about this. Where that can get confusing is if you look in like a cached plan, so like either in the plan cache or query store, or even if you just get like an estimated plan for this, like you’re gonna, like, you notice what we see here.

We see three thick arrows, right? And that’s because when we cached in, or we compiled and cached a plan for this, it was for 2010 through 2013, right?

So we see three thick arrows here, right? So like cached and estimated plans for these can look pretty confusing because you’re gonna, like, they might look like they’re hitting all of the tables and doing all of the work when they are really not.

So the one point that I wanted to make a little bit with this is if we try to update this, because remember, I only created unique clustered indexes on my member tables, right? So if I try to run this update, it’s gonna fail, right?

And it’s gonna say it’s failing because a primary key was not found on, well, it says just 2008, but really none of the tables have primary keys. So if I create primary keys across all of those tables, right, which doesn’t take too long, you know, they’re all pretty, fairly small tables, and we already have an index on the columns that we’re primary keying, right?

So this is all pretty quick, but, and this is why I try to tell people, trying to make these things writable is often not worth it, right?

It is not worth the effort. Just like, it’s much, much, you’re much, much better off spending the time and coding efforts to just hit the right table somehow, like use dynamic SQL or do something else, right?

Like an instead of trigger that does things because like, you know, like we run this update, man, it’s not fun, right?

So yeah, making these things writable, again, not worth the mental effort and then modifications against them, man, they’re not a lot of fun either, right?

Like if we look at the query plan for this, man, we heave-hoed a lot of work into this one, right? There’s a lot of spooling going on, right?

This thing is a big, wide mess. Even though we, like, it’s like, okay, all right, well, we’re hitting the clustered index on just two tables, 2011, 2012. Man, oh man, we do a lot of work, a lot of work here, right?

It is not worth your time and effort to make the partition view writable. Please just spend your time either writing an instead of trigger or writing, you know, some sort of dynamic SQL that touches the right table based on user input, hopefully sanitized user input, of course, but I don’t know.

You can’t have everything unless you hire me. My rates are reasonable. Anyway, this is just a snippet of some of the class material from Learn T-SQL with Eric.

You can, of course, purchase the whole course via a link down in the video description. There is even a coupon code attached to that.

So if you have enjoyed anything that we’ve talked about today, you can learn much, much more from the entire corpus of class material. Anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I’ll see you in tomorrow’s… Oh, no, I’ll see you in Monday’s video for Office Hours. I lied to you. I apologize. All right, it’s a bad way to end things.

I should say I love you. I’m sorry or something like that, right? All right, goodbye.