If you write code to SQL Server then you might be interested in this: at the end of 2017 I wrote a tSQLt tdd training course which has helped over 300 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club. The course is free if you are happy to wait 10 weeks to complete it, with 1 lesson being made available per week - if you are in more of a hurry or you would like to help support the project you can purchase the course which makes it instantly available to you.
In the second part of this series on getting more agile and bringing our Sql Server development practices up to date I am going to talk about why you should get your database under source control.
First a little rambling story, I was at the Redgate Sql in the city event in London last October and the speakers asked a few times who had their databases under source control, betwen 5 and 10 percent of the people there had their databases under source control. One speaker asked “who uses source control”, the person sitting next to me didn’t put his hand up and then the speaker asked “who has their databases under source control”, the gentleman then said he thought the first question was about databases, of course he had his code under source control, he would be stupid not to!
That really made me realise how much of a struggle it is going to be to get the worlds Sql databases under source control, but with a fair wind behind us we can do it.
So…What is Source Control?
Huh? What a silly question, it is where you put your code and it gets backed up so it is safe and if you need to you can roll back your changes and get a working copy again.
So…Why bother wih Source Control?
There is no need to bother, we deploy the code to a database and that is backed up and taken offsite - it is all good in the hood so there.
So…What is Source Control?
Well, source control does give you a backup of your code - you spend time and therefore money on writing good code, it must be safe so you need a way to back it up. Backing up the database is one way to backup your code. The fact that you should already have a good system in place for backing up and testing those backups is a good thing but a backup isn’t the only thing that source control is used for.
Source Control allows multiple people to work on some code, when you check it in, it is checked to make sure no one else has changed it since you last checked out, if they have you get to do a merge (or throwaway yours or their changes). Without this checking on check-in that the files haven’t changed you will very easily lose fixes and features that other people have made.
Source Control allows other people to download the version of the code you are working on, without source control you would either have to work on different parts of the code or email or put on a network share different code updates which really is a pain in the backside. Doing a regular check-out in source control is super simple and normally fast so you can be sure you are always working on the lastet code.
There are two aspects of cleaner code I want to talk about, the first is without source control you don’t have an easy way to see what a file used to look like so you can end up with large swathes of code commented out or behind blocks that look like “IF 1=0 BEGIN …” . When you are debugging or looking at a piece of code you haven’t looked at before, this sort of thing really doesn’t help and it just a waste. With source control you can happily delete parts of the code that aren’t being used and check the deletions in. If anyone ever does need them back then they can find them easily.
The second aspect is a little more controversial. It has become pretty common practice to write code and to prove how good your code is, instead of making your code work and look neat and simple (you may be doing this but some people don’t, trust me on this!) time is spent writing comments in the header, something like:
* Name: proc_to_end_all_procs()
* Parameters: @id int - the id of the thing you want to get
* Created Date: 1 April 2010
* Changes: Added extra widget thing - EE 1 April 2011
* Removed extra widget thing - EE 2 April 2011
* Removed extra widget thing - EE - 1 April 2011
create procedure proc_to_end_all_procs()
All this information is probably wrong and with source control, completley redundant, if we start with the name - we know the name of the procedure as we have a semi-encrypted but still human readable string at the beginning of the stored procedure that looks like “create stored procedure proc_to_end_all_procs()“. Next we have the parameters, which according to the procedure header is wrong - although this is an example, this is quite common. Finally we have a list of changes, the strict process controls around this list are quite easy to get around, it takes real discipline to keep it up to date and accurate.
Why would you spend time adding when you did something when you get it for free when you check it into source control?
Why would you document what you have changed when you get it for free with source control?
Obviously you shouldn’t!
To digress slightly, there are actually three valid use cases for procedure headers, the first is copyright information, if you require it can go in there. The second is if you have examples on how to run the stored procedures (although you should really have unit tests for that). Finally the only other thing that should go into the comment headers is ascii art, perferably of donkeys but you are free to experiment with whatever you like.
Source control is an enabler for CI/CD
Yes that is right, source control is an enabler for otehr technologies - you can’t do those without source control and they all add business value and have a whole heap of benefits we will discuss in a future blog.
You can blame people for their rubbish code
If you have source control you can see who did what and if you use decent check-in comments, why. This should never be used for ridicule but to find out if it was you who broke everything or not, before you ridicule the person responsible. Without source control, you can never really be 100% sure who did it.
Source control gives you so much more than just a backup, it really does enable a whole new world of modern, possibly agile, Sql development - embrace it and use it and never look back.