Derik Whittaker

Syndication

News


Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Source Control, works for code, why not use it for SQL Scripts

Pretty much any developer with half a brain (or any real world experience) knows that you MUST keep your source code in some sort of source code repository.  There are many of them out there, VSS, Vault, TFS, CVS, SVN, the list goes on.

If putting your code (C#, C++, VB, etc) into a source control system is considered standard practice, why do shops still not put their SQL scripts into the same source control system.  I know that there really is NO good way to integrate sql IDE's with source control systems, but deal with it. 

We ran into a scenario yesterday here at work where a proc was changed, but we have no idea why or by whom.  We are do not put our sql scripts in any scs system (against my protests an rants). We would not have this issue if everything was in an scs.

Can someone tell me why sql scripts are special? 

Till next time,


Posted 11-21-2007 8:28 AM by Derik Whittaker

[Advertisement]

Comments

KG2V wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-21-2007 10:25 AM

You mean, like I've been doing since, oh, the mid 1990s?

Visual Studio does have some add ins to manage SQL Server code for a while now (You have to use Sourcesafe - and may team developer, which I have, but the company has NOT moved to)

Jarle Nygård wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-21-2007 10:57 AM

They are not. We've been storing our sql's in source control for years now. Works like a charm! :)

Tim B wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-21-2007 12:00 PM

I have never worked in a shop that didn't have a SQL branch in source control.  The only time I DON'T check in SQL is when it's a quick one-off data script to correct a specific inconsistency or other data issue.

Dale Smith wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-21-2007 12:22 PM

I'm in the same boat as some of your other commenters - we keep our sql scripts in source control.  However, to be most effective with this, your dba's must make the shift from managing db objects directly in Enterprise Manager (or what ever db management tool they use) to scripting all the objects and managing them from source control.  

Mathew Butler wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-21-2007 12:57 PM

Totally agree.

Put simply, any code artifact that is used to initiate a build, deploy a build, implement application logic should be a configuration control item.

All kinds of build/software configuration issues follow if this is not the case. Issues that are impossible to investigate and resolve, if you can't state definitively what version of which artifact is used.

Mathew Butler

Damien Guard wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-21-2007 12:57 PM

Visual Studio 2003 had "database projects" which helpfully scripted out everything into a project that you could then check-in including a complete build cmd (individual tables/stored procs etc. each got their own file).

This has been deprecated somewhat since then...

[)amien

Derik Whittaker wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-21-2007 1:56 PM

@Mathew,

I agree 100%, anything that is code (C# or sql) needs to be inside some sort of scs.  It is scary to me to know that this does not always happen.  Guess I should add that to my list of job interview questions.

SeeR wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-21-2007 3:54 PM

As Damien pointed there is a "Database Project" in VS 2003 and also in VS 2005 (hidden in "Other project types"). In this project I have all my sql scripts.

Ankh (svn plugin for VS) have some problems with this type of project but there is still Tortoise SVN :-)

Ian Joyce wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-21-2007 4:46 PM

Keeping your sql scripts in version control  is a must when working with different versions/releases/branches of a product. You're just asking for trouble trying to do it otherwise. (As you found out).

TomasTintera wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-26-2007 3:02 AM

Stored procedures belong to source control as any other part of code. Really the same ways as C# code does. It's no questin easy decision.

With table structure there is one more thing to remember.

There is not only the code to create the table, but also a code to make a change and transfer data from previous version.  You need to recognize and version all three of them.

Hope somewhere next year we will have automatic system to apply these triplets to production sites. (We target to have hundrests production servers). Hope we can fing something fitting for us. Otherwise we have to develop it.

PartialClass wrote re: Source Control, works for code, why not use it for SQL Scripts
on 11-28-2007 5:26 AM

why dont u try database projects VS2003 and VS 2005

Jeremy Wiebe wrote re: Source Control, works for code, why not use it for SQL Scripts
on 12-04-2007 7:43 PM

The new "Data Dude" (branded Visual Studio Database Professional, I think) brings database development a long ways forward and makes it a natural step to place in TFS.  All schema objects are represented by scripts (at the most granular level, meaning that PK's are in a separate script from the actual table, etc).  I've played with it a bit and it's not extremely mature, but it's a good first step to bringing DB development in line with the other development tasks done on a project.

Loris wrote re: Source Control, works for code, why not use it for SQL Scripts
on 01-25-2008 5:30 AM

I store the definition of my SPs, tables, views and triggers on a Subversion repository, along with my code.  

I wrote <a href="www.gljakal.com/.../index.php">a small program</a> that exports them for me, so it's just a matter of pushing a button before I check in the code :)

Malcolm Barclay wrote re: Source Control, works for code, why not use it for SQL Scripts
on 02-04-2008 11:16 AM

Derik.

There is absolutely no sane reason on the planet as to why you should not be storing all SQL change & development scripts in a source control system. I even put additions to my type tables under source control. In fact many professional environments will mandate that you do this.

Rick Kwitkoski wrote re: Source Control, works for code, why not use it for SQL Scripts
on 09-28-2009 1:36 PM

This is an old thread by now but I have recently started working with a development team in which the database guys do not use source control, which is insane. They will be soon. For anyone that is working with SQL Server, I found that the best plug-in for SQL Server Management Studio is PushOK SVN. Pretty cheap and works well from with the IDE

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)