Last year Steve
Jones (or is it Jeve Stones?) did a great
series on version control. At the stage where tools are now, source code for
SQL just isn't fun. Or easy. Is there a better way? Not sure this is better, but
this week I'd like to discuss a technique that Sean,
Leon & I
used recently that has a bit different focus.
It started with an enterprise application that had to go from design to
production in 90 days, with a team of three developers. The new application was
to be a new UI over an existing schema, in places we would be able to add or
change things, in others we were stuck with it because of the number of legacy
applications we already had in place. The old app uses a total of about four
stored procedures - let me tell you that this is NOT the way to get great
performance. One of our main goals in building was to move as much code as we
could into stored procedures.
By the time we rolled out the first beta for testing, we already had fifty or
more procs. Fairly well organized, lightly commented, but a good initial
structure. That was on about day 30. By the time we hit our final roll out date
2 months later, we had done more than a hundred builds, almost everyone
having at least one new or changed procedure. To make matters a little more
complex, we had a test db that we used for development, then we would deploy our
changes to yet another test database. We had about 10 people testing different
portions of the app, often with different versions.
We used VSS for the application code (two projects, one UI and one dll), but
NO source control for the procs.
Amazed? Astounded? Aghast? Im out of "a" words, so I'll continue.
If the tools had better integration we probably would have put them in source
control, but as Steve discusses in his series it's not that easy. Not
impossible, but takes a bit of work AND a system. We didn't really have a system
in place that we were used to for team development. Growing pains of a small
company I guess!
Anyway, what we decided to do was anytime we changed a procedure, we
versioned it by creating a new proc and appending the version number to it,
something like this:
| Create proc usp_test @param varchar(12) as --blah blah Then when we versioned, the next one would look like this: Create proc usp_test1 @param varchar(12), @other param varchar(10) output --blah blah |
Some of the procs never got changed. Others wound up having 10 versions. The
nice part was that any point we could look back in VSS and see which version of
the proc we were using at the time. Even better, we could have many different
versions deployed and working at the same time, all while we continued to make
changes based on test results. Worked pretty well for a low tech solution. More
importantly, we didn't have to spend a lot of time managing it.
Now to digress a bit, a couple weeks ago I finished reading SQL
Server 2000 Performance Tuning and came across a trick that I mentioned in
the review I wrote - and that is interesting enough that I'd like to repeat it
here. You can use a little known feature of SQL (little known to me anyway!) to
create multiple versions a different way, like this:
| Create proc usp_test as .......... Create proc usp_test;2 as ......... |
If you do this, you wind up with the source code for both (or more) procs in
syscomments. You can just call usp_test and it calls the original/default proc,
or you can qualify it as usp_test;2 to execute the later one. It's interesting,
but I think confusing to administer since you only see one proc in Enterprise
Manager or Query Analyzer. You have to look at the source to see that there are
multiple versions.
Would I use the same system again? You bet! At the end of the development
cycle it had never caused us a problem and turned out to be as efficient as we
could have hoped. I'm looking forward to your comments on this one:-)
