Stored Procedures and Database Logic in Source Control

[originally posted 5/16 on "Dev Blog"]

Stored procedures are good. Those of us working with SQL Server know it’s true. They allow us to centralize our data logic. They speed up our data access. And they increase the security of our applications.

Source control is also good. No matter what technology we work in, we all know this is true. It gives us access to the historical versions of our code. It gives us features like code branching, labeling, and of course checking out and locking files. In general, it saves our butts.

So it follows that source control for our stored procedures is very good. While this makes sense in theory, it’s not always obvious when you’re working on a project that the database logic should go into source control. And in working with SQL Server and .NET technologies, source control isn’t offered automatically, as it is when you create projects for web sites, class libraries, and other project types. In fact, when you work in SQL Server Management Studio, source control is often completely forgotten about (even though it integrates with SQL Server Management Studio in the same way it does with Visual Studio).

But source control for our database code gives us all of the same benefits that we know and love for our non-database code. And, in general, it saves our butts.

The easiest way to get your database logic into source control is to add a Database Project to your solution in Visual Studio. It provides a structure for your database code, and if you add a few standard folders that correspond to the ones you see in SQL Server Management Studio (Tables, Views, Stored Procedures, Functions, Triggers, etc.), it can help others find your code easily. In Visual Studio 2008, the database project also includes an example of a test script, which pushes us nicely toward database unit testing. You can even debug the script from within Visual Studio!

And it’s easy to add a database project to source control: it works the same as any other project.

There is, of course, a catch: Database projects aren’t available in all versions of Visual Studio. And I think Professional (the version most of us are using), doesn’t have them.

But that’s no reason not to put our database logic into source control. The strategy for using source control without a database project is simple. When you are working in SQL Server Management Studio, create files for each of your stored procedures, table definitions, views, etc. Organize them in the same way you would within a database project. And then put the files into source control, either directly within SQL Server Management Studio, or from Visual Source Safe itself. Again, the project types and Source Safe integration available to you from within SQL Server Management Studio may vary based on the version you have installed, so use the features that you have available to you.

As you work with your database logic – and usually that will mean editing or creating stored procedures – always work from the files that are under source control. Always check them out before you use them, and don’t forget to check them back in after you’ve run them on your target database.

Here’s a tip: In your stored procedure files, add a conditional drop statement to the top of the file, so that you can run the entire file each time you update it. At the bottom of the file, add the permissions you’d like to associate with the stored procedure (if any), so that you never have to edit those permissions by hand.

Stored procedures are some of our most valuable code. When it comes down to it, our database logic needs and deserves the protections provided by source control at least as much as the rest of our code.

Ss.