Author Archives: Deirdre O'Leary

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

Allow Instant Initialization in SQL Server 2016+

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

SSMS 2016 Run As issue on Windows 10

As highlighted in a number of Connect items, attempting to run SQL Server Management Studio 2016 as another user (e.g. by using Shift + right-click) results in a message stating that “This task requires the application to have elevated permissions”. This issue affects Windows 10 1607 & 1703 but is apparently resolved by KB4016240. Prior to that, […]

Use Google Sheets to access protected Excel files

Aim: To access hidden sheets in Excel files with protected structures using Google Sheets. In an earlier post, I described how to unhide Very Hidden Sheets in Excel. That method does not work if the workbook structure has been protected. In this example, Sheet 2 has been hidden & attempting to unhide it using the […]

Filter Data in Outer Joins

Aim: To clarify the difference between filtering data in a WHERE clause or within the OUTER JOIN. When using an OUTER JOIN, there will be a difference in the results depending on whether the data is filtered in a WHERE clause or within the OUTER JOIN itself. The below queries have been run against a copy […]

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!