We store all our Sprocs, Triggers, Views etc. in separate files, and those are stored in a Revision Control System - SVN in our case, but I'd like to upgrade to a distributed system so I can commit locally.
I started out with Dave Burton's TLib[/url] - looking at my repository I find that it goes back to 1986 ... and the EXE folder includes this example file from Valentines Day 1985 🙂
.S GOODBYE.X 14-Feb-85 Dear John letter
I'm sorry, but I don't love you anymore. I've found someone
new. I'll always remember the special times we've had together.
.S GOODBYE.X 14-Feb-85 Dear Mark letter
.C 2 5
Seems to me that the alternative is to just right-click a Stored Procedure in SSMS and say EDIT and then EXECUTE when I am ready. Is that about the Top & Bottom of it?
Hopefully I'm preaching to the converted ... if not and folk do use the RightClick-Edit in SSMS then do please stir-your-stumps and get some Revision Control installed. Anything will do to start with, migrating to a different tool is a pain, but rather than prolong the choice process I think more important to get something installed and consider if there is a Better Alternative later.
EVEN in a one-man-band shop.
Assuming RightClick-Edit in SSMS / no Revision Control system installed:
1. Two people editing, by chance, the same SProc are "latest-save wins" and earliest-save is "all code changes lost"
2. Nightmare to rollout from DEV to TEST and then PRODUCTION. I suppose such folk would use a Database Changes Difference tool, like RedGate's. maybe that works Just Fine? Our SProc scripts include EXECUTE Permissions and sometimes a "INSERT this CONFIG row if not already exists" - none of that would be in a Datbase DDL DIFF script. We also have an SProc call at the top of each SProc Create Script to "log" that the script was run, and what version of the Sproc it is (we basically just use 'YYYYMMDD' as the version number). We can query the Log to see which version of the script was run/when, and compare that against DEV / TEST if we need to. It also shows (a high count on) days when we did a rollout, which is sometimes a useful clue / aide memoire.
We create a rollout script by concatenating all individual files that have a Modified Date more recent than the last rollout. Sometimes the sequence of execution means we get an error because "B is before A" in which case we rearrange the individual pieces within the rollout script and run it again until it runs clean. Fair chance that if it ran clean on TEST it will similarly run clean on PRODUCTION. We have APPs used by lots of clients, so a rollout to production, for us, is "lots of servers" rather than just one, in-house, one.
3. We do, occasionally, go back and compare code from an old version to figure out how a new bug has crept in particularly when we can associated it with "Something must have changed on THIS date"
4. I peer-review all code by juniors (using the Revision Control System to compare original-version and recently-checked-in-version). There is no-one to peer review my code, so I use the CHECKIN process for that job - easy enough, during checkin, to right-click each file in the Check In list and use the "DIFF against most recent version" to see what changes I made and double-check that they make sense!! If a day or two has elapsed since I actually made the change, and thus I am seeing it with fresh eyes, so much the better. I think that would work in one-man-band shops too.
5. We have
markers in our code (hopefully I'm not alone??!!). With Revision Control I can be aggressive deleting them as I know I can retrieve them from repository if I need them again. Thus if I see them in a Peer Review I know that the "verbose" version is already in the repository so I can clean-up the code, check it in again, and we are then running with minimalistic code.
6. Code Collisions, when two people edit at the same time, don't matter. The second person to check-in gets to do a merge (in fact SVN will usually suggest an automatic merge where the code changes don't trip over each other within the single file)
7. Then there is code branching. Maintain service pack fixes to Production code whilst simultaneously carrying out new development work on Production. That must be a nightmare without Revision COntrol