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

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

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

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

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

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

Following on from my previous post about Instant Initialization of database files, SQL Server 2016 and above allows you to set this up as part of the instance install simply by ticking a box. On the Service Accounts tab of the Server Configuration step, tick “Grant Perform Volume Maintenance Task privilege to SQL Server Database […]

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!