Unit Test Sql Server Code

In part 3 of this series of blogs on “getting more agile” I am going to look at unit testing and why we need to do it.

My code is perfect, I need no stinking tests

Ah you are like me, my code is perfect as well, good point. blog end.

actually…your code may well be perfect but:

  • a) It only got perfect by you writing the code and writing a test script to make sure it worked (you did at least test it once, didn’t you?)
  • b) Everyone else in the world (basically everyone other that you and me who are the only people, dear reader, who write perfect code) write horrible code and do terrible things like change table and column definitions, the blighters
  • c) Those other horrible developers and DBAs need examples of how to use the code
  • d) They also need documentation on how it works, what it returns in all cases and what side effects it has as they are too simple to work it out for themselves, the welps

ok, I admit it, I am one of those welps - what is more, I make changes to code without examining every stored procedure, every function, every piece of application code to see if it will cause any issues - I am literally a renegade throwing caution to the wind plus I write very little documentation, I certainly never explain what I have done to a stored procedure, I don’t need to. I can do this because I write unit tests and make sure that every piece of code that can run is covered by a unit test.

The tests:

  • Prove that the code I write, does what it should do (the testing part)
  • Stops me breaking any other part of the database or application, without me knowing about it and giving me a chance to either fix it or reasses the change (this is a big one)
  • Helps document the code and give examples of how it works, see https://the.agilesql.club/Blogs/Ed-Elliott/Unit-Tests-Help-Document-Your…

Tests are also an enabler for much more exciting things namely continuous integration and continuous deployment. To get CI/CD working you need a number of things, one of those is unit tests - without these the whole thing is pretty much pointless.

But I have a big database without unit tests, I can’t start now

Yes you can, write tests for all new code you write and all changes you make to the existing code - but before you do that, go read this: http://www.amazon.co.uk/Working-Effectively-Legacy-Robert-Martin/dp/0131…