Test Driven Developer - SQL

SQL Server Queues PRINT output

John E. Boal — Wed, 06 May 2009 17:00:43 GMT

Ideally, SQL queries are executed really fast (sub-second). However there are times when it takes many seconds, even many minutes to execute each step. We have traditionally used the PRINT statement in SQL to output trace type information to the console so we can see what's happening.

Recently I noticed that the PRINT statement output wasn't coming out when I expected it should. In doing some research on the issue, I discovered that the PRINT statement output is queued and batched rather than real-time.

The solution for this problem is to use the RAISEERROR statement instead. Here is an example:

DECLARE @msg NVARCHAR(MAX) = 'status message'
RAISERROR (@msg, 0, 1) WITH NOWAIT

The severity of 0 tells SQL that everything is still fine, and NOWAIT instruction tells SQL not to queue it but to output immediately. The 1 is a locator that can be used to tell someone where to find the source of a particular message, if they are kept unique throughout the code.

So, for long-running queries, use RAISEERROR and get those status messages out in real-time!

Unit Testing and Test Driven Development - a Practical Guide

John E. Boal — Sat, 15 Nov 2008 21:08:59 GMT

Here is a presentation I wrote on what Unit Testing is all about, and how TDD fits into the ATDD cycle.

There are specific things here on testing the UI code with Selenium and JSUnit, and recommendations on how to do unit testing on your database code.

This presentation is in PDF format, but I can post the PPTX format also if needed.

A Practical Guide to Unit Testing1.pdf (503.29 KB)

Testing SQL Tables

John E. Boal — Mon, 14 Jan 2008 16:15:53 GMT

Have you ever wanted to test a table in the database for the correct structure? Ever had someone make a column nullable that shouldn't have been? ever have someone make a column VARCHAR instead of NVARCHAR? INT instead of BIGINT? SMALLDATETIME instead of DATETIME? This is an easy way for bugs to creep in - when the code is written for one version of the database and then someone makes a change... So if you haven't been testing your tables (SQL should be TDD'd too), perhaps you should...

Here is a handy stored procedure you could use that describes a table in an easy-to-test way.

CREATE PROCEDURE describe (@table_name varchar(90))

SELECT DISTINCT

sc.column_id as ColumnNumber,

cols.column_name as Name,

cols.data_type as Type,
ISNULL(cols.character_maximum_length, 0) as Length,

cols.is_nullable as Nullable

FROM

information_schema.columns cols

INNER JOIN sys.columns sc ON

cols.column_name = sc.name

AND OBJECT_NAME(sc.object_id) = @table_name

ORDER BY sc.column_id

Just call it with the table name and it produces a nice format the tests can use to extract the info they need to check.

# Name Type Length Nullable

1 Id           int      0      NO
2 TypeId       int      0      NO
3 Name         nvarchar 50     NO
4 Description nvarchar 1500   NO
5 CreateDate   datetime 0      NO
6 UpdateDate   datetime 0     YES

just employ a data reader to read the data in the test, and your unit tests can ensure that all of the tables have the right structure.