Category Testing

Misusing tSQLt: test that view is not empty

The following tSQLt test checks that a view is not empty. This test is primarily used to check the views that expose data to the end users of data warehouse. For performance purposes, this test uses TOP 1 when querying the actual view. CREATE PROCEDURE testFacts.[test that ViewName is not empty] AS BEGIN IF OBJECT_ID(‘testViewName’) IS […]

Misusing tSQLt: test that indexes exist

The following tSQLt test checks that all the indexes exist & are enabled as expected: CREATE PROCEDURE testSchema.[test that all indexes exist] AS BEGIN IF OBJECT_ID(‘actual’) IS NOT NULL DROP TABLE actual; IF OBJECT_ID(‘expected’) IS NOT NULL DROP TABLE expected; CREATE TABLE actual ( name SYSNAME NOT NULL ,type_desc NVARCHAR(60) NOT NULL ,is_disabled BIT NOT […]

Misusing tSQLt: test that table data is correct (historical unchanging fact data)

The following tSQLt test checks the historical unchanging data in a fact table to ensure that it has not been changed unexpectedly as part of the data warehouse update. This test compares data between the staging database (DatabaseName_stg) & the live data warehouse (DatabaseName) which have the same or similar schema. For performance purposes, this is […]

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 […]

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!