Stored Procedure Maintenance

Question

In sql server ...

Should I maintain stored procedures via the object broswer in query analyzer, or as external text files and why?

Answer 1

External text files can be put in source control so this is a better solution. Versioning databases is difficult and this is a major step in having a way to keep your database in sync with all the applications that rely upon it.

Microsoft's Visual Studio 2008 Database Edition does this very thing allowing for more than just version control on database scripts but also facilities for deployment in a single step, unit testing, and other interesting things.

Answer 2

Once your system becomes large enough, using the object browser to find your stored procedures can be a real pain (and it doesn't do a very good job of making them very sortable). Although I regularly like to use the object browser, I would probably recommend for anyone else to start with external files that way you can begin source control on them from the very outset.

Answer 3

Have a tool which extracts the text of the stored procedures on a regular basis and stores them in source control.

That way, you've got the best of both worlds. Your text files exist and are in source control, and you can guarantee that they are as up-to-date as can be. And if anyone changes your stored procedures without you knowing (as a boss I used to have tended to do), you'll see it very promptly in a "recent changes" way.

Answer 4

Either.

External files in source control give a nice feeling, but database objects can not be controlled or locked or checked out like "normal" source files. However, it is good discipline.

If you have strong production controls, good processes and a code difference tool then managing in the object browser is feasible for smaller projects because you can always detect differences and reset them easily.

I hope you don't mean in object browser on production...

Answer 5

A lot can depend on the version of SQL Server you are using, and how important it is to rollback to previous version of the SQL.

I found that with SQL Server 2005+, its easier to maintain them in SQL Server Management Studio, as its all integrated into the .NET Framework.

I feel storing backups as external files are a waste of time if you keep regular backups of your database.

Of course i fully understand people wanting to use something like SourseSafe as an additional external source for historical backups.

Answer 6

...another vote for text files in source control.

• A huge benefit is revision history. Comparing versions can give you insight into problems, and save you hours of debugging, especially that of the "I wonder why they did that," variety.
• If you're having a problem in one database instance that you're not seeing in others, you can export the stored procedure in question, compare it to what's in source control, and make sure you're consistent across instances.
• It makes deployment of code into a new database instance easier-to-control. For instance, you can write an Ant task to audit your database schema, and have a task that runs the procedure-creation code as a step in that process.
• You can look at procedure source without having to connect to the database. This may be especially valuable in recovery scenarios.
• It enforces the discipline of keeping scripts available, instead of having your team to one-off changes in GUI tools. The GUI tool changes work, but are lost forever, if not captured in a script.