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

...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.

Answer 2

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.