Category Testing

Misusing tSQLt: test that table data is correct (dims or reference data)

The following tSQLt test checks the unchanging (or very slowly-changing) data in a dimension or reference table to ensure that it has not been changed unexpectedly as part of the data warehouse update: CREATE PROCEDURE testSchema.[test that TableName data is correct] AS BEGIN IF OBJECT_ID(‘actual’) IS NOT NULL DROP TABLE actual; IF OBJECT_ID(‘expected’) IS NOT […]

Misusing tSQLt

tSQLt is a great T-SQL based unit testing framework for SQL Server. In addition to using it for unit testing, I’ve recently been (mis)using it to do some sanity testing of a slowly-updating data warehouse that I’m responsible for. The data warehouse is updated roughly once a quarter from new or refreshed data sources & […]

Misusing tSQLt: test that table/view metadata is correct

The following tSQLt test checks that the basic schema of a table (or view) to ensure that it has not been changed unexpectedly as part of the data warehouse update: CREATE PROCEDURE testSchema.[test that TableName metadata is correct] AS BEGIN IF OBJECT_ID(‘expected’) IS NOT NULL DROP TABLE expected; — Create a table with the expected […]

tSQLt Part 3: Running tests from TeamCity

Aim: Automatically run the tSQLt tests from TeamCity following each successful build & deploy. Fortunately, there is a TeamCity runner for tSQLt available at which makes running tSQLt tests from TeamCity very easy. Add a step to run the tests: Download the latest version of the tSQLt runner from:  Via Services, stop the […]

tSQLt Part 2: Deploying tests

Aim: Deploy the tSQLt framework & tests using TeamCity & Octopus Deploy. In production, you may have separate TeamCity, Octopus Server & Tentacle environments. However, as this is a basic setup for testing purposes, I will install all three on the same machine as my local SQL Server instance. For clarity, I will refer to […]

tSQLt: Cross-database testing

A note on cross-database testing In my previous post, I outlined how to install tSQLt on a specific test database. To test a database called Accounts, I installed tSQLt on the Accounts_Tests database. It’s worth noting (and I forgot to mention it in the last post) that tSQLt is designed to be installed on the […]

tSQLt Part 1: tSQLt & SSDT

Aim: Include a SQL Server test framework in an existing SSDT project so it can be added to version control & deployed to environments. Set up the tSQLt framework: In SSMS, create a new database to hold the tests, e.g. if you plan to run tests against an Accounts database, create a database called Accounts_Tests […]

Kevin Kline

Career and Technical Advice for the IT Professional


Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert


A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!