Introduction to Test Driven Development for SQL Server

Overview

We all want to build better code faster. That’s the mandate many of us are faced with, in today’s fast-paced world. One of the ways that many of the top developers in the world have improved their code is with the addition of unit tests that check their programming logic and prevent them from making the same mistake twice.

It’s bad enough to spend hours finding and fixing database defects, explaining to the rest of the team what went wrong, and trying to clean up the mess. It’s even worse when a mistake in your code causes the end-user to make a bad decision. Database defects are far too costly, and most testing practices do not adequately detect or prevent them.

This fast-paced class introduces tSQLt, the framework for automated unit testing in SQL Server. You’ll learn techniques to write T-SQL code that is resilient to defects and is easy to change and maintain.

This class also includes hands-on exercises helping you learn to efficiently write effective unit tests for your applications.

You will walk away with examples for testing your tables, views, stored procedures, and functions, for ensuring calculations are correct, and error handling is implemented.

Here is an outline of what we will cover over two action-packed days.

Modules

Module 1: Introduction to Unit Testing and TDD

Software development teams around the world are discovering the benefits of automated unit testing. But there is also a lot of disbelieve and wrong information surrounding this topic. The goal of this module is to introduce you to the world of unit testing and test-driven development, answer the common questions, and alleviate any concerns.
You are going to discover why Unit Testing is important - particularly when it comes to protecting your company’s most important asset: the data - and what its benefits are. We will dive into the differences between Unit Testing and Test-Driven Development and explore the benefits of both.
At the end of this module you will be able to point out the perceived and actual cost of automated unit testing and weigh them against the benefits of more confidence in your codebase, early discovery of functionality deviations, and fewer defects overall. In short, you will be able to explain to peers and stakeholders, why taking on Unit Testing or even TDD in your organization is a great idea.

Module 2: tSQLt - Writing and execution Tests

One of the tools you need to use to implement Unit Testing successfully is a Unit Testing Framework. We will first explore what to look out for when selecting a unit testing framework (for any language). Afterward, we will take a look at tSQLt.
In this module, you’ll discover what tSQLt is, where to get it from, and how to install and use it. You will be able to explain the concepts of test, class, and suite and know how to organize your tests.
At the end of this module, you will have written your first test in tSQLt. You will also have explored the different ways of executing your tests and will be able to pick the right one for any situation.

Module 3: Assertions

One of the core features of any testing framework is a robust set of assertions. In this module, we will look at the different assertions available in tSQLt.
You will discover how to use assertions in your tests, not only to achieve the immediate goal but also to minimize long-term maintenance costs for your entire test suite. In particular, we will explore the single reason principle and identify what it means for your test case selection.
You will also discover, how to select the type of assertion to use and how to avoid common pitfalls that are particularly dangerous in the set-based world of relational database code.
At the end of this module, you will have written several tests using different assertions, and you will feel comfortable selecting the right type of assertion for each test you will have to write in the future.

Module 4: Writing Effective Test & Test Suites

The primary goal of Unit Testing and Test Driven Development is to reduce overall maintenance effort (and with that cost). However, new practitioners often engage in patterns that could be counteracting on achieving that goal.
In this module, we will discuss patterns and best practices to follow as well as red flags to look out for when embarking on this journey. You will discover the concept of separation of concerns. You will identify best practices and pitfalls unique to unit testing in a database environment, particularly when it comes to dealing with test case data.
We will take a look at test case heuristics that will guide you in writing “just enough” tests to keep test case (and with that overall code) maintenance cost low.
At the end of this module, you will be able to identify necessary test cases and know when you have written enough of them. You will be able to explain which (if any) test case heuristic is appropriate for a given scenario and use it to guide you in the development of a robust test suite.
You’ll also discover several best practices for writing good tests and be able to explain where and why they are different from general coding best practices.

Module 5: Increasing Test Maintainability

A common problem unit testing practitioners encounter is that of fragile tests. One of the main goals of automated unit testing is to increase your confidence in your codebase and your ability to apply changes.
Fragile tests, however, can undermine that confidence and quickly derail the entire effort to take on unit testing in your organization.
In this module, you will discover what fragile tests are, and what scenarios commonly lead to this particular problem. You will also identify ways to prevent fragile tests from occurring in each of these scenarios.
At the end of this module, you will have identified fragile tests and implemented the necessary changes to prevent or stabilize them. You’ll also have discovered several patterns to help isolate your tests from changes in the environment and to manage test data efficiently.
You will be able to pinpoint potential scenarios causing test fragility before even writing any tests and with that be able to prevent them from hampering your organization’s unit testing success.

Module 6: Testing Special Scenarios

Once you embark on unit testing more complex projects, you will encounter scenarios that raise two questions: Should you even test them, and if so, how can you achieve adequate test coverage for them?
In this module, we are going to dive into a number of these types of scenarios. You will discover when and how to test database constraints. You will also determine what functionality is important to test, and when to rely on the correctness of the platform.
You will write tests that expect exceptions to happen as well as tests that expect them not to happen. You will also identify ways to deal with triggers, the output of procedures, and multiple result sets.
At the end of this module, you will have written tests for several of these special scenarios and will feel confident that you can handle any of them in your projects.

Module 7: Legacy Code

When developing real live systems, you will likely have to deal with testing pre-existing code. You might encounter T-SQL modules that are seemingly impossible to write tests for. You’ll also have to decide when to write tests for existing code, and when to rely on your best judgment instead.
In this module, you will discover techniques you can use to make complex modules testable. We will look at code smells and discover the concept of seams.
At the end of this module, you will be confident in writing enough tests for your legacy codebase at the right times. You will also be able to apply safe changes to existing modules to make them more testable. With that, the project of bringing the existing code base under test does not become insurmountable, and you can be confident when addressing new requirements or otherwise maintaining that codebase.

Module 8: Extending tSQLt

Every once in a while you will encounter a problem that tSQLt can’t help you solve. And sometimes you can avoid a lot of work by just extending the tSQLt functionality a little to suit a specific use case in your application.
In this module, we will be looking at what you can do in these situations. This includes writing your own assertions or creating test doubles for your specific needs.
At the end of this module, you will have written tests that rely on your own assertions, and you will have successfully faked objects not currently supported by tSQLt.

Module 9: Beyond Unit Testing

Automated unit testing plays a vital role in any modern software development process. However, it is not the only form of testing that is required to successfully improve your code quality and reduce your code maintenance costs.
In this module, we will look at other forms of testing and other technologies surrounding unit testing. We will discuss how they fit into your development process and where potential pitfalls are when dealing with them.
We will also take a short look at how to integrate tSQLt test cases into a CI environment.
At the end of this module, you will be able to explain the differences between different forms of testing and identify when to use which form within your company.

Prerequisites

To get the most out of this class, you should have a basic understanding of the T-SQL language, and you should be able to create databases and T-SQL modules in your own environment.
The unit testing framework tSQLt which we will be using in this class requires CLR to be enabled. For most effective learning each participant should have their own instance of SQL Server installed on their machine. You can use SQL Server Developer Edition for that purpose which is available for free from Microsoft.

How to attend

If you would like us to teach it in your neighborhood, contact us now.

Executive Summary

Software development can be a risky endeavor. Projects often get delayed and even after deployment, the ongoing maintenance costs often far surpass projections.
In recent years, many organizations have been able to improve that situation by embracing best practices like agile development, DevOps, automated unit testing, and test-driven development for their application code. However, database teams often struggle to keep up with these changes.
This is caused by two forces: Database teams have been trained for years to be cautious and conservative when it comes to applying changes to databases and processes; the restrictive environment of a relational database management system makes embracing agile development, and particularly automated unit testing, significantly more complex.
This complexity often leads to mistakes early in the adoption timeline that can turn very costly later on, potentially forcing companies to pull out of embracing modern development techniques and going back to a more waterfall-like process for their database code. That not only leads to the problems discussed above but also hinders development in other areas of the application.
“Unit Testing in SQL Server with tSQLt” is a class designed for teams developing SQL Server code who are looking to add Automated Unit Testing to their operations.
In nine modules, your team will learn what they need in order to

write automated tests in tSQLt for their T-SQL code
improve code quality
reduce the number of defects deployed
implement best practices for easily maintainable code

This will help your organization reach the goal of rapidly delivering value to the business while preventing costly and time-consuming mistakes.

After attending, your team will be able to keep test development costs low and code quality high because they can

confidently write unit tests for their SQL Server modules
achieve effective code coverage with a minimal number of tests
use isolation and other techniques to prevent test case fragility

Sending your team to attend this class will allow them to fully embrace techniques like unit testing, test-driven development, and automated ci/cd pipelines (DevOps). Cost savings and positive culture change will not be far behind.