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 https://github.com/cprieto/tsqlt-teamcity 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: https://github.com/cprieto/tsqlt-teamcity/releases.  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

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

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!