January 19, 2018 at 12:02 pm
I use With Schemabinding whenever possible, on the theory that it is safer to have things locked up to prevent accidental or untracked changes. However, when I actually DO need to make a change in a table, the number of views that need to be unbound and rebound after saving the changed table can quickly get large and unweildy. I have tried to write scripts to automate the changes, but not had much luck - the trail of links quickly gets very complicated, and is not amenable to set-based code. Getting the error message text, finding what blasted next view is blocking the change, going down, changing that, going back, trying the change again, getting another error...
It gets tedious, and I often find myself losing track of the proper order, so I rebind something out of order, then I can't rebind something later and have to go back and unbind a bunch of things again, and again. It also doesn't help that SSMS doesn't give the schema for the blocking view - just the name, which often means a good bit of hunting around, end the tabs in SSMS don't give the name of the view.
Does anyone have a good method of dealing with all this? I'm tempted to just dispense with schemabinding altogether, but I keep reminding myself that these databases exist for the users needs, not my personal convenience. But it does get on my nerves.
January 19, 2018 at 12:09 pm
I'm assuming although you don't explicitly tell us that you have schemabound views on schemabound views. The views for which I use schemabinding are directly related to the base tables. I don't have any views that build on views and would not recommend that whether you use schemabinding or not. It gives views a bad name. Even with this straightforward approach, schemabinding can be a pain, but what is gained is worth it.
If you aren't building views on top of other views, please clarify.
January 19, 2018 at 12:12 pm
No, you're right - I do have chained views. Not a lot and not too deep, but they do exist. They're very useful in preventing multiple duplicates of the same code. I know, you get a performance hit, but it's not a big enough one to worry about in the systems I'm running, and avoiding the hassle of having numerous copies of the same code are worth it.
January 19, 2018 at 12:19 pm
You're apparently taking a maintenance hit as well. I don't have a good system of maintaining schemabound chained views as I don't use them. But as already said, that's not good practice as a rule. You seem to have a good reason for it, so you'll have to decide if it's worth the tradeoff. I'm sure there are some who would disagree with my use of the schemabound views at all and just use the table instead.
January 19, 2018 at 12:29 pm
Everything is a trade-off. For the most part, I like the way I'm doing it, and I generally add schemabinding late in the game, when the design is relatively stable. But it's never cast in stone, and I do have these issues occasionally. Probably what I'm going to have to do is exercise some discipline when making these changes, and copy/paste the names of views I'm unbinding into a stack, in the order in which I unbind them, then follow the stack back the other way to rebind them. It's going to be a PIA, but at least I will be sure of getting them all put back correctly.
January 22, 2018 at 11:17 am
to be honest it sounds like a lot of hard work for little benefit
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2018 at 11:46 am
to be honest it sounds like a lot of hard work for little benefit
I agree it's an extra step that isn't strictly necessary, at least in my use. I started using views for my fact and dimension tables after reading the suggestion in Kimball's latest book. I was skeptical at first because I am not generally a proponent of views. They have their place, but I've never needed to do either of the things for which I thought they'd be useful and generally just add unnecessarily to the complexity. I became a convert of views in this instance, however. It gave me a certain flexibility in design that I didn't otherwise have as easily. This led me to the schema binding aspect though. This allows you to add a unique clustered index, essentially keying the view. This key shows up in the BIDS DSV. It has also captured ETL errors in development. An OLAP design without these schema bound views can still be a good one (otherwise my older designs would not be), but I use them for my current designs.
As for "hard," they can be annoying in development when a change is necessary. But I have scripts that will drop and recreate the schema bound views without difficulty.
January 22, 2018 at 1:20 pm
A collection of scripts that would unbind and rebind every view was one of my thoughts, but when you chain views, order is important - you can't bind a view that references an unbound view, or unbind a view that references a bound view, so just trying to change everything with no plan is almost certain to result in an error. Maintaining the scripts in the proper sequence, and keeping the ordering functional through additional design changes would be a major hassle.
What I have started trying to do is write a script that unbinds views, traps errors, pushes the view that can't be unbound down the stack and unbinds something else first, rinse and repeat, until all affected views are unbound, in the order necessary for unbinding. Then make the table change, and traverse the stack in the opposite direction, rebinding everything back to the root. But it's not going well - locating everything that needs to be unbound is difficult.
January 23, 2018 at 9:27 am
Have you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 23, 2018 at 9:31 am
Have you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes?
You can't change the table if a view is schemabound to it.
January 23, 2018 at 9:36 am
RonKyle - Tuesday, January 23, 2018 9:31 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes?You can't change the table if a view is schemabound to it.
Correct. My suggestion is to consider the option of removing schemabinding and instead refresh all views / all related views after any table change.
[We do have a handful of critical views that we schema-bind here, but not all views.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 23, 2018 at 10:03 am
Correct. My suggestion is to consider the option of removing schemabinding and instead refresh all views / all related views after any table change.
I'm not sure what this gets you. The views that are schema bound are schema bound for design reasons and for the way the SSAS Data Source View interacts with them. If they aren't schema bound, then I'd lose this advantage.
January 23, 2018 at 10:20 pm
Not sure how much this would help, as I've not done this, but when dealing with the order of dependencies there is the a system procedure ,sp_depends, that may be of use. Try exporting the results to a temp table. See if this give something useful to the logic.
----------------------------------------------------
January 24, 2018 at 2:24 am
ScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)
Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
January 24, 2018 at 2:51 am
MMartin1 - Tuesday, January 23, 2018 10:20 PMNot sure how much this would help, as I've not done this, but when dealing with the order of dependencies there is the a system procedure ,sp_depends, that may be of use. Try exporting the results to a temp table. See if this give something useful to the logic.
Hm, that looks useful. It doesn't appear to directly address the issue of cascaded views, but just knowing what all views are impacted is a huge step in the right direction. What I've been doing so far is just trying to make a change, and reacting to error messages - slow and frustrating. Maybe I can iterate this procedure to arrange the list into order by dependency sequence - that would do exactly what I need.
Many thanks for the tip - I've got some experimenting to do.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply