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

Invitation from Hamish Watson.

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…)

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
#sqlfamily

Yip.

T-SQL Tuesday #124 – Using Query Store or Not Let’s Blog

Invitation and summary from Tracy Boggiano.

Ever since Microsoft introduced Query Store I’ve been working with it, back to the CTPs in 2016.  I started presenting on it because it benefited my current company at the time.  I heard there are low adoptions rates and from a couple people implementations problems or just not having time to implement it.  After 3 years of presenting on it and writing a book about it I’m curious as to adoption rate of Query Store, but we won’t be writing about that.

For this T-SQL Tuesday, write about your experience adopting Query Store, maybe something unique you have seen, or a how your configure you databases, or any customization you done around it, or a story about how it saved the day.  Alternately, if you have not implemented yet blog about why if you are using 2016 and above, we know why if aren’t on 2016.  If you are unfortunate to be on below 2016 write about what in Query Store you are looking forward to the most once you are able to implement it.  Basically, anything related to Query Store is in for T-SQL Tuesday, hopefully everyone has read up on it and knows what it can do.

T-SQL Tuesday #123: Life hacks to make your day easier

Invitation from Jess Promfret.

So here we are, the first Tuesday of February. I personally always find February to be the month where my motivation is a little low. I live in the northern hemisphere so it can be a pretty dreary winter month where it still feels like there is a long way to spring (I will say this January I moved from Ohio back to England and the distinct lack of piles of snow is helping this cause somewhat). This makes my topic even more relevant as we need a little extra help to be productive and get through the month.

My topic is looking for your favourite ‘life hack’, something you use to make your day easier. This could be anything from a keyboard shortcut in SSMS that runs ‘sp_whoisactive’, to a technique you use to get and stay organised.  It doesn’t have to be directly related to a technology, just whatever you use to make your life easier.

Now, I’m personally a huge proponent of using keyboard shortcuts to get things done faster. In the last year or so I’ve started using Visual Studio Code as my editor of choice and the number of little ‘life hacks’ I’ve found has grown incredibly. I’m going to share a couple that I use often to get your ideas flowing.

Multiline Select – Ctrl + Alt+ Direction Key

This is something I love for formatting queries, among other things. I know you can use T-SQL to generate some queries from the metadata but if you have a list of tables you want to truncate, for example, you can easily accomplish this. Select the start of each line by using Ctrl + Alt + down direction key, add the TRUNCATE TABLE text and then press end to get to the end of each line, no matter the length, to add the semicolon.

The other use I have for this hack is to generate names and descriptions of Active Directory groups for tickets to have them created.  At my previous job we created read and admin groups for databases that users could then request access to. Multiline select made this really easy to generate the required information.

You can use multiline select at the beginning of the row. Start by selecting the first word and copying it (Ctrl+C), then you can type to format your group name. For example, I put SqlDb- before the database name and then -Read afterwards.  Pressing enter at the end of the group name will create a second line for all three groups where you can add the description. Notice I can now use paste (Ctrl+V) to add the database name that we copied from each line.

This ability to change multiple lines at once is really powerful and once you get the hang of what you can do with it you’ll find so many opportunities.

Change all occurrences – Ctrl + F2

A similar hack to my first, VS Code also lets you change multiple occurrences of characters. I say characters because you can select whole words, parts of words, or even punctuation. This is really handy, for example, for formatting a comma separated list on one row into a list with each value on a separate row.

Carrying on from my previous example, now that we have formatted the group names and description. I can select the word ‘Read’ and replace all with ‘Admin’. Just like that I have all I need to get the group request off to the help desk for creation.

Command Palette –  F1 or Ctrl+Shift+P

VS Code also has a really great Command Palette that offers a lot more for you to explore. A few of my favourites are:
– Sort Lines Ascending/Descending – Select some lines in VS Code and easily alphabetise them.
– Git: Undo Last Commit – Rescue that last commit back from your source control. Useful if you realised a second too late you committed to the wrong branch.
– File: Compare Active File With – This clearly highlights differences between two files.

Over to you

I hope my VS Code life hacks have got your ideas flowing, so now it’s over to you.

T-SQL Tuesday #122 – Imposter Syndrome

Invitation from Jon Shaulis

As we enter the new year, I’m sure many of us are setting goals, resolutions, or perhaps beginning new challenges. Change can often be terrifying, but that’s how we grow. With this in mind, the topic I’d like us to write about this month is “Imposter Syndrome”.

Imposter syndrome isn’t a topic I’ve seen addressed before via T-SQL Tuesday and this is an issue I’ve commonly seen in the IT industry.

Imposter Syndrome – The persistent inability to believe that one’s success is deserved or has been legitimately achieved as a result of one’s own efforts or skills.

https://www.lexico.com/definition/impostor_syndrome

I can assure you that if you have felt this way before, you are not alone. People in the community who I would consider experts have stated they felt (and sometimes still feel) imposter syndrome. These are people with more experience than years I’ve existed on this planet and they still feel this way. Coincidentally, this triggers my own imposter syndrome when I think about that.

T-SQL Tuesday Topic

I want to read your stories about when you’ve experienced, seen, or overcome imposter syndrome! Was there a job that you felt you were ill-prepared for? Did you make a mistake or did someone say something that made you question if you were a true data professional? Maybe there was a particular task you ran into that made you question your experience? Did you resolve your tasks and succeed in your job? How did you overcome that feeling of being an imposter and solve your challenges? Maybe you haven’t experienced it yourself but you saw someone who was feeling imposter syndrome, were you able to help them?

You can be technical or non-technical with this post, the goal is to share experiences to help those also experiencing imposter syndrome. Maybe you are still feeling it, sometimes walking through your challenges can help you brainstorm solutions.