Database stored procedures, functions and triggers are source code of your application. Please treat them as such.

Posted by

As a consultant for the past two years, when I come into a batch of code that has many stored procedures or triggers in the database, the first thing I ask is whether those stored procedures and triggers are under some kind of revision or source code control. If the developers/DBAs look at me like I’ve got two heads, then I know I’m in for a doozy of a gig because those devs are probably not doing the best job they could be doing.

And that makes sense right? You don’t call in a consultant if everything is unicorns and rainbows with your system.

Stored procedures, functions, and triggers in your database are part of your codebase. They could even be the most important and most delicate piece of your codebase.

I’ll repeat this again for total clarity… Stored procedures, functions, and triggers in your database are part of your codebase.

But not managing your codebase with a tool is a huge mistake. And yet, I see this time and time again on my gigs. And if I had a nickel for every time I saw a stored proc named with “_new” or “_new2” or “_use_this_one” in the name, I could invite Warren Buffet over to play cards and he would consider it.

For managing the source code of your stored procedures on SQL server, there’s even some commercial tools available that take your database objects and chucks them into your source code repo. Or you can always just create a “sql” directory in your source code repository and store the objects there.

There are a lot of ways to manage this problem. Ignoring it, is not managing the problem, it’s making it worse.

Leave a Reply

Your email address will not be published. Required fields are marked *