Should you store your SQL Stored Procedures in Source Control?

When developing an application with lots of stored procedures, should you store them in some sort of source versioning system (such as source-safe, TFS, SVN)? If so, why? And is there a convenient front end way to do this with SQL Server Management Studio?

From stackoverflow

• Yes. All code should be stored in source control.

  Simply put, code is code and mistakes happen. It's nice to be able to go back and see what changed over time and be able to go back to those changes.

  We have to add it manually to a source control system, but you can create addons for the Sql Server the Management System. I haven't ever created one to automatically add it to source control, but I suppose you could. Also, all the code is stored in sql tables, so you could in theory create a process or something to go through the table(s) and retrieve all the code and commit it automatically.

  Update: I would always write extra code to check and see if the code exists and if it doesn't create a filler procedure and then the actual script do and alter procedure.

  IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SomeStoredProcedure]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
  
  EXEC sp_executesql N'CREATE PROCEDURE [dbo].[SomeStoredProcedure] AS
  
  SELECT ''SPROC Template'''
  
  GO
  
  SET ANSI_NULLS ON
  
  GO
  
  SET QUOTED_IDENTIFIER ON
  
  GO
  
   ALTER PROCEDURE SomeStoredProcedure
  

  Doing a drop and recreate will remove all the user permissions you have setup for it.

  Jim Leonardo : And I'd add the table definitions including indices, keys, etc and any 'lookup' data that isn't expected to be created during the lifetime of the app.

• You should.

  To my knowledge, no such tool exists to automate this process. At least, five years ago, when I was considering building one, there didn't seem to be any competition.

  JohnFx : There are tools out there, and visual studio has some built in capability. However, it is almost universally inadequate. We would up buying a souce-code license to a third party product and tweaked in the extras we needed to make it work.

• We store our procs in Subversion, all your SQL Code including DDL should be in some kind of source control repository

• SPs and table schemas for that matter are all assets that should be under version control. In a perfect world the DB would be built from scripts, including the test data, as part of your CI process. Even if that's not the case, having a DB/developer is a good model to follow. In that way new ideas can be tried out in a local sandbox without impacting everyone, once the change is tested it can be checked in.

  Management Studio can be linked to source control, although I don't have experience of doing this. We've always tracked our SP/schema as files. Management studio can automatically generate change scripts, which are very useful, as table drop/recreate can be too heavy handed for any table that has data.

• Most definitely yes. Then the question becomes how you store them in source control. Do you drop and recreate the stored procedure or just alter, do you add permissions at the end of the script or in a separate script. There was a post on Coding Horror a while back about the topic that I found interesting. Is Your Database Under Version Control?

• SQL procs also surely need the same security/benefits of version control as the rest of the code in the project.

• I recommend that you do store them. You never know when you'll need to rollback, or dig into logic you may have removed..

  Here's a good way to easily grab your Stored Procs into files that you can throw into whatever source control you desire..

  http://stackoverflow.com/questions/337601/stored-procedures-to-sql-files

• Get your database under version control. Check the series of posts by Scott Allen.

  When it comes to version control, the database is often a second or even third-class citizen. From what I've seen, teams that would never think of writing code without version control in a million years-- and rightly so-- can somehow be completely oblivious to the need for version control around the critical databases their applications rely on. I don't know how you can call yourself a software engineer and maintain a straight face when your database isn't under exactly the same rigorous level of source control as the rest of your code. Don't let this happen to you. Get your database under version control.

• ABSOLUTELY POSITIVELY WITHOUT QUESTION NO EXCEPTIONS IN ALL PERPETUITY THROUGHOUT THE UNIVERSE YES!

  Kevin : sooo...what you're saying is that you think it's a good idea? :)

  ConcernedOfTunbridgeWells : .. not to put too fine a point on it.

  NTDLS : But do you feel strongly one way or the other? :)

  Mark Schultheiss : no way, wait a sec, let me grab the handle of this slot machine quickly...I will win, I will win...:)

• As others have said, yes they should be.

  I don't know of an easy way to do this with SQL Server Management Studio, but if you also use Visual Studio, database projects are a great way to manage this.

• Sure you should.

  In MS SQL 2008, you can do it right from Management Studio.

• Absolutely. Positively.

  A set of SPs is an interface, that is likely to be modified more frequently than structural changes. And because SPs contain business logic, changes should be stored in version control to track the modifications and adjustments to the logic.

  Storing these in version control is a symptom of organizational maturity at a coding level, and is a best practice.

• YES DEFINITELY

• Storing stored procedures is a great idea. Its a pain though. Just how do you get all that stuff into subversion? You can manually do it, but then its tedious and you end up not doing it at all.

  I use a tool from the subsonic project.

  sonic.exe version /server servername /db databasename /out outputdirectory
  

  This command saves everything to 2 text files. One contains database schema, stored procs, user accounts, constraints, and primary keys. The other one contains the data.

  Now that you have these two files you can use subversion(cvs,source safe) to move it into source control.

  More info for using The Command Line Tool (SubCommander)

• SQL is code. All code belongs under source code control.

  That is all.

• There are methods in SMO to generate scripts if you prefer to code your own scripting tool.

  http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated

• If you're not using asset management alongside source control, then I say throw everything in source control. Images, word documents, the whole shebang. Can't lose it, can always reverse any changes to it and if any machine goes down - nothing is lost.

• Most definitely.