T-SQL Tuesday #031 – Logging

June 12, 2012

This month’s T-SQL Tuesday is hosted by Aaron Nelson (blog | twitter) with a theme of “logging”.  Although Aaron’s definition of this topic is deliberately broad, I thought I would go with a more typical definition and talk about the approach I use for logging the actions of large multi-step stored procedures.

Read the full article →

How to Delete From Large Tables in Batches Without Growing the Transaction Log

April 29, 2012

I was working with a colleague a few months back to find an efficient method for deleting the entire contents of a large table without using TRUNCATE or causing excessive log growth. There were reasons why we couldn’t temporarily remove the foreign keys which meant that TRUNCATE TABLE wasn’t an option so we had to […]

Read the full article →

A mock too far? What is good practice for mocking database objects with tSQLt?

March 28, 2012

tSQLt, the open source unit testing framework for SQL2005+ has some really great features that allow database objects to be mocked, dramatically reducing the time needed to set up reference data for each test.  But sometimes, just because you can do something doesn’t mean that you should; at least not all the time.  In this […]

Read the full article →

Unit Testing Databases with tSQLt Part 10 – testing a foreign key's ON DELETE or ON UPDATE actions

March 20, 2012

In Part 9, we looked at basic unit testing of a foreign key ensuring that basic referential integrity was properly enforced. In this post, we continue writing tests for foreign keys, delving in to cascading deletes (or not cascading as the case may be). We’re using deletes in this article but the same approach and […]

Read the full article →

T-SQL Tuesday #028 – Specialist? Generalist? Or Both?

March 13, 2012

delaware florida This month’s T-SQL Tuesday is hosted by Argenis Fernandez with a theme of “Jack of All Trades, Master of None?”.  As Argenis asks, are you a generalist, a specialist, or both? Can you even be both? In this post I will look at what those terms have meant and do mean to me.

Read the full article →

From Zero to SSIS – Course Review

March 12, 2012

I’ve used SQL Server Integration Services (SSIS) for a number of years, although not with any regularity so I wanted to find a course to help me build on that experience and take my skills to the next level. The course I chose was “From Zero to SSIS” presented by Andy Leonard (blog | twitter) […]

Read the full article →

Unit Testing Databases with tSQLt Part 9 – testing a FOREIGN KEY constraint

March 7, 2012

In Part 8, we looked at testing string searches as part of a WHERE clause and also validating the effect of supplying multiple, cumulative search conditions. In this post, we go back to our DDL tests and explore a couple of approaches to unit testing foreign keys.

Read the full article →

Guest Post – All-Pairs Testing

March 1, 2012

I am very pleased to be able to present this article written by Dennis Lloyd Jr (blog | twitter), creator of the Test Driven Databases Initiative and one of the authors of the tSQLt Unit Testing framework for SQL Server.  One of a series on applying test case heuristics to database testing, this article explores […]

Read the full article →

Unit Testing Databases with tSQLt Part 8 – testing string searches in the WHERE clause

February 16, 2012

In Part 7, we looked at how to write tests to confirm that parameterised start and end dates were correctly applied in a WHERE clause. In this post we will extend that SELECT procedure further by writing more tests for the WHERE clause, specifically searching the contents of a string, and validating how default values […]

Read the full article →

Unit Testing Databases with tSQLt Part 7 – testing date ranges in a WHERE clause

January 13, 2012

In Part 6, we looked at writing tests against a simple SELECT procedure checking that the correct columns were included and that results were returned in the correct order. In this post we will extend that SELECT procedure writing some tests for the WHERE clause, specifically dates and date ranges, and validating how default values […]

Read the full article →