October 5, 2007 at 10:06 am
Hello.
My company has a third party database which we have purchased. They update the data in the database remotely, every day. Their data is split into multiple tables for performance (i.e. Table1, Table2, Table3) and they are not using partitioning (unfortunately). Each table is millions of rows in size. The split of data is not logical, so I have to union the tables to get the data.
I only need a few columns from these tables, so was hoping to create indexed views with just the columns I need, and union the tables together in the view. This will require schema binding. I have had great performance improvements with this in the past. I understand that shema binding prevents schema changes as long as the view exists.
However, the vendor will not allow me to add the index views, because they claim that shema binding will lock the tables and cause a remote update to fail. As far as I know they are only updating the data.
Is there any truth in this? Can anyone shed some light on this for me?
Thanks,
Wayne
October 5, 2007 at 10:11 am
Tell your boss that you think the data transfer will not fail. Create the view with HIS permission. Be there when the data is reloaded and see what happens.
I'm sure you are aware of the pitfalls that could cause a failure of that transfer. Be sure you are ready to handle that case.
October 5, 2007 at 10:13 am
My understanding is that schemabinding simply causes any attempts to alter or drop the tables to fail. So I suppose if you were doing an update to the software and had to issue an ALTER TABLE statement as part of that update package, then that would fail.
What I'd do is create the indexed views and then create a script which dropped them and a script which recreated them. When I had to apply a patch I'd drop the indexed views, apply the patch/upgrade, then rebuild the views/indexes.
As always, testing would probably be in order, however...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply