Script Libraries and Version Control

Raise your hand if your collection of T-SQL scripts is sitting in a folder under My Documents. Why isn’t that script library in version control? You have version control at work, right? [caption id=“attachment_957” align=“alignright” width=“300”]Not that kind of library Not that kind of library[/caption]

Why should I version my scripts?

There’s three big reasons that you should version your scripts. The first is to have a history of your work. Version control lets you keep track of all your changes. And, if you realize months down the road that you made a huge mistake in one of your own scripts, you can just hit undo. I’ve been able to make huge course corrections in my scripts without a lot of headache. When you’re collaborating with team members on your scripts, version control makes it easy to sync your changes. You’re probably thinking “But a file share is much easier!” And you’d be right. A file share is incredibly easy, but a file share also means that only one person can safely modify a file at a time. Version control lets multiple people work on different parts of the same file and merge their changes together. Best of all, you can use “blame” tools in your version control to see who last worked on a specific line. I’m not saying you should be blaming your co-workers, but it does help you realize that you’re the one who broke everything. Last, but certainly not least, it’s your legacy. Once you’ve set up version control for your scripts, your co-workers can contribute and build on what you started. By working together and collaborating, you’ll be able to make everyone’s life easier with the tools you’ve created internally. I understand your urge to hoard secret knowledge like an alchemist of old. Remember: these are your co-workers, you’re all there for a common goal. If you don’t think that’s the case, there are problems bigger than version control.

But I don’t have version control!

You may work at a company that doesn’t have version control. While that’s weird, it’s possible. You may also not have access to version control. The thing is, it’s really easy to get access to version control. Best of all, it’s pretty much free. I’m not going to tell you how to set up version control for yourself, but here’s a few starters:

  • Github - you can create private repositories, just check the box.
  • Bitbucket - you can also create private repositories here!
  • Host your own git server - not as hard it sounds, but probably not for the faint of heart.
  • Ask your dev team where they keep code. They’re probably happy to set you up.

You could have version control for your scripts right now!

What are you waiting for?

You should get started today! There’s no reason to put off versioning your scripts, you only have something to gain.

“Alchemy (Speculum Alchemiae)” by Steven Feather is licensed under CC BY-NC-SA 2.0.

Chase Marler - Jan 2, 2016

It’s worth noting that to create private repos on Git, you gotta pay. Its free if you’re creating public repos with open source code. Bitbucket appears to be free for teams up to 5 people and have some great tutorials for getting started. Thanks for the read - to source control I go!

Jeremiah Peschka - Jan 2, 2016

True, GitHub does charge for private repositories, but anyone can host their own Git repository. There are a lot of tools out there that make it really easy. The bigger question is - should you be hosting company scripts outside of company control? ;) Personally, I keep my scripts publicly visible just so I can share them with people. And let you guys criticize the way I indent and/or name variables.

Update 2020-10-25: Private repos have been free for a while on GitHub.

Chase Marler - Jan 2, 2016

You’re absolutely right. In my case, I have scripts that I don’t want to be publicly accessible for security reasons (and hey…maybe they’re scripts I’ve moved around with from company to company…). Prolly time to have a different conversation with management before I go rogue with source control. ;) I love LOVE that you keep your scripts publicly visible for the community, and I don’t think I just speak for myself there. All of us who follow you appreciate all of your efforts to keep your experience and wisdom transparent so we can learn that rock-star data folks are real people that can make mistakes too. :-)

Danielle - Oct 0, 2019

How would you handle a single script that can be run on any version of the software application? Does that single version of the script need to be checked in to each version of source control that is available? ie. If a have clients on 3 different versions of the application, do I need to create 3 release versions to deliver the reference content contained in a data population script or can I have a single release that is version agnostic?

Jeremiah Peschka - Oct 0, 2019

I’d say that you should maintain a single script that detects the version where necessary. sp_BlitzCache does this in a number of places - it uses temporary tables with optional columns to accomplish this, but you could use whichever technique you want. If you have multiple scripts, you have to make the same change in three places which means there are more opportunities for bugs.