October 17, 2011 at 10:17 am
Hey Guys -
I'm thinking of enabling SchemaBinding on an application we're working on, but is there any simple way to change a View when SchemaBinding is enabled so it will test the change through all dependent and referenced objects then commit the change if nothing would break?
For example I'm thinking of enabling this on for one of the projects we're working on, but we have several Views at the top which are referenced by a dozen or more other Views. The only way I can see to modify the upper level view is to Alter every view referencing it to remove SchemaBinding then re-enable SchemaBinding on those Views after the fact. And due to cascading dependencies I've found some scenarios where changing a core view would involve altering 20+ Views or Functions just to disregard SchemaBinding even though the View change in the end won't break any of them.
So is there any simple way to work with SchemaBinding? I like the stability it brings, but through development and even with minor changes after deployment it just seems like it could cause some major headaches. Not to mention altering a View with an Index drops the Index.
Thanks --
Sam
October 17, 2011 at 10:28 am
sam.alexander (10/17/2011)
Hey Guys -I'm thinking of enabling SchemaBinding on an application we're working on, but is there any simple way to change a View when SchemaBinding is enabled so it will test the change through all dependent and referenced objects then commit the change if nothing would break?
For example I'm thinking of enabling this on for one of the projects we're working on, but we have several Views at the top which are referenced by a dozen or more other Views. The only way I can see to modify the upper level view is to Alter every view referencing it to remove SchemaBinding then re-enable SchemaBinding on those Views after the fact. And due to cascading dependencies I've found some scenarios where changing a core view would involve altering 20+ Views or Functions just to disregard SchemaBinding even though the View change in the end won't break any of them.
So is there any simple way to work with SchemaBinding? I like the stability it brings, but through development and even with minor changes after deployment it just seems like it could cause some major headaches. Not to mention altering a View with an Index drops the Index.
Thanks --
Sam
Working with schemabinding can be problematic. If you find a good solution let me know. I have done some schemabinding with some functions to help improve performance. My issue was similar I have a chain of 9 functions that all call each other. So they have to be dropped in a certain order and then re created in a certain order. My solution to that was I named the function files with a prefix number so they would get created in order. The first script I added the correct sequence of delete statements to delete all of the functions. So when my database gets scripted from source control everything works out fine.
I would say if you aren't seen serious benefit from schemabinding it can be a real pain to manage.
October 17, 2011 at 12:06 pm
bkubicek (10/17/2011)
I would say if you aren't seen serious benefit from schemabinding it can be a real pain to manage.
I'm starting to see this to be true, so I'll probably just avoid using them all together I guess. I really like the benefit they bring, but given we're not using table indexes to any large degree I think it'll probably be more of a PITA to implement it in the project we're working on now. Our app developers just get frustrated as I work on the database in parallel to them working on the app, so I hoped this could be a good solution to keep the schema in check. Not worth the headache though.
Sam
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply