I'm so excited about this, I've got to blog it before I walk the dogs..

A requirement emerged recently for our team's database platform to support multiple versions of code within a single database.  The database will know which users are running which versions of our front-end components, and will also know which versions of those components are tied to particular database versions.

The challenge is one of maintainability.  How can we make over 2,000 source files version aware, and, more importantly, how can we maintain multiple versions going forward.  (Who's Jeff Ball, you ask?  Jeff is a colleague of mine at Microsoft, and his passionate argument for the technical feasibility of mass versioning of database components, when no one else in the conversation was comfortable with the idea, got me to thinking, which led me to..)

Here's a script which demonstrates the technical feasibility of Jeff's idea.  It must be run in SQL Server Management Studio with SQLCMD mode enabled (when SQLCMD mode is enabled, the :setvar directive will appear highlighted as depicted below).

:setvar VersionMnemonic V1.2
SELECT 'this is a test of naming$(VersionMnemonic)' AS [SelectedText$(VersionMnemonic)]
GO
IF NOT EXISTS (
    SELECT  routine_name
    FROM    information_schema.routines
    WHERE   routine_name = N'test$(VersionMnemonic)'
    AND     routine_schema = N'dbo'
    AND     routine_type = N'PROCEDURE')
EXEC (N'CREATE PROCEDURE [dbo].[test$(VersionMnemonic)] AS SELECT GETUTCDATE() AS [Baseline$(VersionMnemonic)]')
GO
EXEC [dbo].[test$(VersionMnemonic)]
GO
ALTER PROCEDURE [dbo].[test$(VersionMnemonic)] AS
SELECT 'this is a test of naming$(VersionMnemonic)' AS [FromProcedure$(VersionMnemonic)]
-- silly test to prove we can do it..
IF '$(VersionMnemonic)' <> '$(VersionMnemonic)'
    SELECT 'something is seriously wrong' AS [PlanetaryMeltdownFromProcedure$(VersionMnemonic)]
ELSE
    SELECT 'all is well' AS [CoreTemperatureNormalFromProcedure$(VersionMnemonic)]
GO
EXEC [dbo].[test$(VersionMnemonic)]
GO
SELECT  routine_name
FROM    information_schema.routines AS VersionedRoutines
WHERE   routine_name LIKE 'test%'

The first line :setvar VersionMnemonic V1.2 is where the unique suffix for the version is set.  The remainder of the script demonstrates how this can be used.  To simulate a different version, change V1.2 in the line above to another value and run the script again.  In development, we could apply a bulk change to the appropriate code tree to update the line above with the appropriate new version.

We can simply insert the line above into every versionable component of the database and update every appropriate object reference to include the $(VersionMnemonic) reference, and we’re there.  We have one round of heavy touches to get that done, but thereafter our version update consists of a mass copy and a bulk search-and-replace operation.

Pretty darned maintainable, eh?  Now you know why Jeff is my hero, at least until Monday..

     -wp