January 20, 2011 at 11:37 am
Tutorials on schemabinding don't seem to address this, or at least, none that I have found. They only explain how it works and why you might want to use it.
Many of my views have schemabinding so that I may put an index on the view, for performance reasons. All good, but when I make a change to a table, all the views using that table get their schemabinding blown away and I have to rebind it and recreate the indexes. This is starting to become a PIA and I find myself considering code workarounds rather than a table change, because it's such a nuisance.
I don't want to save the schemabinding 'for last' - for one thing, this DB regularly undergoes expansion and development cycles, so there isn't much of an 'end'; for another, I couldn't then test with indexed views, which I use a lot. Seems like there ought to be some reasonable way to allow table changes without manually having recreate everything downstream, especially when the changes don't have any impact on the schemabound views.
Does anyone know of a sensible way to deal with this?
January 20, 2011 at 2:11 pm
Two things you may find helpful are to separate your prod and test environments, and to modify your database objects through scripts rather than through SSMS.
The second is the key in your case, because you can organize your scripts to handle these dependencies. You can also manage them with source control, deploy them with SSIS, and all sorts of good stuff.
If your script followed the pattern:
Check if index is created and drop it
Check if view is created and drop it
Check if table is missing and create it
Alter table to correct specification
Create view
Create index
Etc...
Then all you have to do is rerun the script and everything works.
Yes, it can be a pain sometimes, but it's just a matter of getting in the habit of doing it this way, and it is a good habit to have.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
January 20, 2011 at 3:58 pm
weitzera (1/20/2011)
Two things you may find helpful are to separate your prod and test environments, and to modify your database objects through scripts rather than through SSMS.
I do have separate databases for test and real, I've got enough sense (acquired through the exercise of not enough sense) to not experiment on real data. But that's actually adding to my grief, somewhat, because I have to make all these persnickety changes on my test instance, keep track of what I've done and then make them all over again on the real version, usually on a weekend, when I can play with the server without bothering anyone else.
The second is the key in your case, because you can organize your scripts to handle these dependencies. You can also manage them with source control, deploy them with SSIS, and all sorts of good stuff.
I've only got the Express version, so I can't use SSIS. I've looked at scripting and have used it a few times, modifying scripts generated directly by SSMSE. It's helped, but it's still a pain in the fundament. Are you saying I should have a DROP and a CREATE script for every single item and every single property in the DB? It seems to me that creating, then picking through, possibly modifying and executing all those individual scripts will be just as much work as using the graphical interface to simply do the work, no? Or are there some tricks to managing scripts that I should know? Do you know of any good tutorials for this sort of scripting work?
January 20, 2011 at 5:33 pm
Unfortunately, I don't know of any tutorials.
You only _need_ to have scripts for the objects that are in development, but that tends to change, so I generally have scripts checked into subversion for everything.
It's not that you need one script that drops it, one script that creates it and so on.
Consider the example of promoting changes from dev to prod. On the one hand, you could make all the changes from the interface, or if you normally make changes to dev via script, you could simply rerun the scripts against prod.
You're never going to have the perfect world where every script takes care of every dependency, but you can make that workload smaller this way.
Of course, you always want to have changes to a particular object only occur in one script, and you want to have your scripts named and organized in such a way that when you say "I want to modify table X" you know which script that will be in.
For starting out, there's a tool called Scriptio http://www.sqlteam.com/publish/scriptio/
that will generate scripts for every object in your database, and there's a tool called xSQL.ScriptExecutor http://www.xsqlsoftware.com/Download/xSQL.ScriptExecutor.exe that will run a bunch of scripts on a database for you.
I've never used either of these, always preferring to roll my own and batching calls to osql/sqlcmd using a windows batch file.
Also see http://www.sqlservercentral.com/articles/T-SQL/63473/ for some ideas on getting a grasp of the dependency tree, which might get you thinking about how to make the scripting task even better.
I'm sure some other folks around here have good input as well.
Anyone???
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
January 21, 2011 at 2:35 am
Sounds like something I should learn to use. The organization of scripts into packages seems to be an important factor - THIS table is changed, so THOSE scripts need to run. Thanks for the links, I've already downloaded the Execs and willl start experimenting with them this weekend.
One more question, if you don't mind: when you have a view that refers to several tables, and so the necessity to run the script that recreates it can be prompted by changes in any one of several tables, how do you keep track of that? Do you have some naming convention for the scripts, a separate set of notes for the scripts, multiple copies of the script in different places...?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply