Okay, I can follow and agree with that logic. I guess feeding someone with the firehose instead of step by step MIGHT be construed as a little overboard. 🙂

@tom (Didn't quote for length):

Well, you're quite correct there, it's a narrow perspective. However, someone who's writing against views and can't see base schema also won't be able to adjust the FKs or see the execution plan, so this article isn't really written towards them in my mind. It's written for the person trying to cater to them, which means they're already pretty deep in the optimizer to care about this topic at all. However, you are showing a real world example of where this makes an actual difference, as the alternate would be to write umpteen different views depending on exactly what data the developer was trying to access.

At the same time, I rarely take this approach for app devs to access my data because of all the intrinsic concerns that views bring into play, one of which is the one you mention. Another is data width, one of my biggest concerns. Additionally, it's against most best practices and depending on your security concerns, straight up illegal in production. The fact that I have usually dealt with medical and/or securities and retirement data through my career may have colored my opinion in this regard.

You're right, however, that ad-hoc queries written against the base schema or the views are difficult to monitor at best. By their nature, however, ad-hocs are supposed to be one-offs. Your business should understand that. If you're ad-hoc'ing the same query at the end of every month, it's no longer ad-hoc. It's requirements you haven't given to your tech department.

Trying to keep views the same constantly is a nice to have to me, but I have no issue with ripping a view to pieces and rebuilding it if I need to, as a view is merely a subquery container for most usage and should be able to be traced in sys.sql_modules. If your app coders are blazing ahead of your sql resources, then you've unbalanced your development team. You've over-hired on app guys (or maybe you've just got a couple of superstars). It's time to get them more support... even if you have to let go of an app guy to get your ratio(s) right.

So...

You're using a shop where inexperienced coders are relying on workarounds for ad-hoc queries as your example of when to use this optimization. You're right, they exist, and when I get there I try to fix that problem. I understand your point, and you're correct within that venue. That the venue exists is a whole different issue I probably shouldn't be derailing the topic with, so I won't go too deep unless the conversation hangs a left turn there.

I just feel like we're addressing the wrong concern with this. I do appreciate the very detailed explanation however of where something like this could actually come to use... as a stopgap until they fix things properly.

I've edited this twice now and I think it finally reads correctly. I'm honestly not trying to come off as an ***, but I just can't see people at the level of skill you're discussing having a clue this could help them, nor them looking for it in the first place. Anyone with enough skill to know and/or remember the intricacies of the optimizer at this level and requires to use this for your use-case is either insanely overwhelmed with workload or is in the first steps of cleanup when they arrive at a new site.