T-SQL Tuesday #125 – Unit testing databases – we need to do this!!

Welcome to the first April edition of T-SQL Tuesday. I’m honoured to be hosting it and have picked a topic that is near and dear to me.
If you are new to T-SQL Tuesday this is the blog party started by Adam Machanic (b|t) and now hosted by Steve Jones (b|t). tsql2sday150x150-1[1]

It’s an awesome way of encouraging blog posts from the community and helping to share that knowledge out.

My topic is about unit testing databases – something that I don’t see enough of when I am working with clients. The good news is that over the years I’ve noticed that more people are speaking and writing about unit testing databases and folding that testing into DevOps processes like Continuous Integration & Continuous Delivery processes (CI/CD).

I hope that this topic drives some conversation both for it (because it protects your code and data…) and against it (it takes too long to write these pesky unit tests…!!).

We’re now delivering Bugs to Production faster than ever!!

This clickbait type heading actually describes what will happen if you embrace DevOps processes without doing any form of testing. Because DevOps is all about accelerating the delivery of software – we want to do more deployments and do them quicker…

..which is why testing is core to DevOps practises.  Specifically testing right throughout the deployment pipeline – that is starting at your laptop and finishing in Production (yes I advocate for testing in production but that’s a whole other blog post..).

What is Unit Testing?

Unit testing is a software testing method by which individual units of source code are tested to determine whether they are fit for use. A unit is the smallest possible testable software component.  A unit is small, so it is easier to design, execute, record, and analyse test results for than larger chunks of code are. Defects revealed by a unit test are easy to locate and relatively easy to repair.

This is opposed to finding the defect in Production – which is harder to triage and is now affecting users – whereas if you find that bug on your laptop via a unit test – it is way easier to remediate and only affects – you.

The ideal qualities of unit tests:

Decisive – the unit test has all info to determine success/failure

Valid – it produces a result that matches the intention of the code written

Complete – it contains all information it needs to run correctly within the test harness

Repeatable – always gives the same results if the test harness and code are same

Isolated – is not affected by other tests run before nor affects the tests run after it

Automated – requires only a start signal in order to run to completion

Benefits of Unit Testing

Below are the benefits of unit tests – this relates to application and databases alike.

Code Quality goes up:

Unit testing improves the quality of the code. It identifies every defect that may have come up before code is sent further for integration testing. Writing tests before actual coding makes you think harder about the problem. It exposes the edge cases and makes you write better code.

Find Issues early:

Issues are found at an early stage. Since unit testing is carried out by testing individual code before integration, issues can be found very early and can be resolved then and there without impacting the other pieces of the code.

Simplifies Integration

Unit testing allows us to refactor code or upgrade things at a later date and make sure everything still works correctly. Unit tests detect changes that may break things and help with maintaining and changing the code base.

The best part about unit testing is that it verifies the accuracy of the each unit of code. Afterward, when we integrate the code units together and run some form of integration testing during the build process we can then verify the individual units of code.

So what about databases?

So now that I have introduced unit testing – is it valuable for implementing with databases? Rather than write my own opinion – I’m going to hand it over to the community to answer this…


(BTW I think it is valuable – I speak on it regularly and I implement it with clients to safeguard their production databases…)


The Rules

  1. Your post must be published on Tuesday 14th April. This counts as long as it’s still Tuesday anywhere in the world.
  2. Include the T-SQL Tuesday Logo and make it link to this invitation post.
  3. Ping backs should work, but to be sure, add a comment to this post with a link to your own so I know where to find it.
  4. Tweet about your post using the #tsql2sday hashtag


Lastly – we’re currently in a lockdown in New Zealand – because of the COVID-19 pandemic sweeping the world.

I want to say to all who are reading this:

Be Safe

Be Strong

and please:

Be Kind

If you’re struggling with things – please reach out to your support network (we care about you), I wrote some things that have been helping keep myself:


Take care – we’re in this together and you’re not alone




Being awarded the PASS PASSion Award – you can too

The annual PASSion Award is the highest accolade given to a PASS volunteer.

Presented during the annual PASS Summit conference, this award recognizes an individual’s exemplary service and commitment to the PASS organization, by inspiring our community of data professionals to connect, share, and learn.


I was lucky enough to be nominated for this award – people nominated me back in September 2019 – although I didn’t know that they had…

Long story short I was awarded the PASSion Award at PASS Summit 2019:

This is a photo of a humbled and nervous Hamish…
The actual award – it’s heavy – it has pride of place next to my daughter’s dance awards 😊

During the recent weeks in COVID lockdown it got me thinking that I should celebrate this award – I had not publicised it – as to be honest I’m still very humbled by it…

So I would rather turn this around – you too can be awarded this – now is a great time to reach out to your networks, to help setup virtual conferences and volunteer for things that might not have existed or were possible before.

Give it a go – or on the flip side – when nominations open in September 2020 – nominate someone you know that makes a huge difference in our PASS Community.


Be Safe, Be Strong and Be Kind…. surviving COVID Lockdown in New Zealand

Currently we have have the COVID-19 pandemic sweeping the world. Here in New Zealand we are currently in lockdown – what this means is that we Kiwis are required to stay at home.

Not go out – except to get groceries or go to the pharmacy.

What this actually means is that we are now at home with our families – but only our families.  I am an extrovert… I love interacting with people. My lockdown is a bit different than most:

I recently travelled to Canada and returned to NZ on Friday 20th March and went into “self isolation” – as the NZ Government decreed any international travellers had to self isolate for 14 days.  I had a friend who had an empty apartment so was planning to stay there for the 14 days – so as not to accidentally infect my kids – who then might infect their grandparents (who are over 70..).

So I stayed by myself 30 kilometres (20 miles) from my family. However NZ went into lockdown 6 days into my self isolation so I made the decision to go home and self quarantine myself in 2 rooms (one of which had a ensuite bathroom). Luckily my main client has allowed me to work remotely which has helped my sanity greatly and means I’m not unemployed….

I am now 12 days into my 14 days and I thought I would share how I, as an extrovert, have coped and managed my isolation – from people.

These were my things that I came up with – yours will be different so these are only suggestions. Like the pyjamas suggestion – for me I need to have a state change to get into work mode

  1. Stick to a routine
    Go to sleep and wake up at a reasonable time, write a schedule that is varied and includes time for work as well as self-care.
  2. Still dress well
    Get showered and dressed in comfortable clothes, do the normal things you did. Don’t wear pyjamas all day (but you know what – if that works for you – do it.)
  3. Go outside for some fresh air
    Get out at least once a day, for at least thirty minutes. If you are concerned of contact, try first thing in the morning, or later in the evening.  Remember you only need to keep 2 metres or 6 feet away from people.
    Even just opening a window and letting in fresh air helps.
  4. Talk to people
    This was a huge one for me and I had some wonderful friends who WhatsApp video chatted, Teams “meetings” and even employees at my client site who regularly contacted me to talk. It was a massive difference to my wellbeing.
    For you – you might not like video chats – but please keep in contact with people via text messages at least – it will let them know you’re ok. Or if you’re not OK then you have a way of interacting with your support network.
  5. Be silly
    I have a friend who sends me a picture of a beer every day – a different beer and his description of that beer.  It has made me laugh and kept me slightly sane – because I’ve had to think of witty replies back. Gold.
  6. Eat well
    Stay hydrated and eat good food.  This one may seem obvious, but stress and eating often don’t mix well, and we find ourselves over-indulging, forgetting to eat, and avoiding food. Drink plenty of water, eat some good and nutritious foods, and challenge yourself to learn how to cook something new!
  7. Spend time being a kid
    If you have them – spend time with your kids – being a kid.
    Spend extra time playing with your kids, or if you don’t have any – go play some games, do things that you normally wouldn’t that might bring some playtime to your life. Play is cathartic – for kids and adults too.
  8. Respect each other
    Have a space – it’s yours to retreat to.
    It is important that people create their own separate space for work and for relaxation. For children, help them identify a place where they can go to retreat when stressed. My house currently has 3 “forts” built that the kids use to “get away from it all”.
  9. Kids will be kids
    And adults will be grumpy.
    Kids thrive on routine and that routine has likely been upended – same deal for adults. So expect behavioural issues with kids and adults too – expect increased anxiety, worries and fears, nightmares, difficulty separating or sleeping, testing limits, and meltdowns. Focus on emotional connections with your close ones. They need you as much as you need them.
  10. Self-acceptance is key
    We can too easily be hard on ourselves..
    ..be a bit easier on yourself, accept that you might fail at some things. These are huge unprecedented times and accept everything about yourself, your current situation, and your life without question, blame, or pushback. You cannot fail at this.
  11. Exercise – if its your thing..
    I setup a small gym (weight bench and 4 dumbbells of various weights) in one of the rooms I am quarantined to. I have to say that doing weights has helped my state of mind GREATLY. For you it might just be a 30 minute walk out in the fresh air to get away from things. Exercise is good – you don’t have to run a marathon – just that physical state change.
  12. That thing you keep putting off..
    You probably have a project that you’ve been meaning to do but haven’t (I know I do – there are a heap of things on my rural property I could be doing that don’t require me to leave it!!).  Find something that will keep you busy, distracted, and engaged to take breaks from what is going on in the outside world.
  13. Reach out for help
    We’re here for you, we care.
    Keep up your medications and if you do them –  therapy sessions the best you can. If you are having difficulty coping, seek out help for the first time.
  14. Chunk it out
    Take each day as moment by moment. Break your day or tasks up into manageable chunks. It’s like the analogy of eating an elephant – you do it bite by bite (BTW please don’t eat elephants – they’re awesome creatures!!)
  15. This is temporary…
    It might seem like this will never end – and uncertainty is worrying. Although this is scary and we’re in difficult times it will pass in time.
  16. Most of all – be you
    …and remember to converse with others – however works best for you
    We’re in this together.
  17. Let’s chat
    Do you know me? Reach out and say hi.
    Don’t know me? Reach out and say hi – I like to talk, listen and make people laugh.

And please – remember:
Be Safe
Be Strong
and most importantly:

Be Kind