Defensive Database Programming with SQL Server

I have just completed reading the ebook edition of this book by Alexander Kuznetsov’s  – which can be downloaded from here.

I would highly recommend it. It is a definite must read for ALL developers.

Jonathan Allen has done a short review of the book which gives an overview of the contents.

A theme that particular resonated with me was Alex’s constant reminder – with example scripts – of the need to test the objects in your database.  Whilst unit testing has become more mainstream in terms of ‘software’ development, in my experience, database unit testing is still not widely practiced.  Hopefully, books like this will encourage more developers to adopt database testing practices.

In Chapter 3 there is a an example of how to handle changes to data types/length on base tables and the issues this can cause for sprocs parameters – he discusses this in this blog article.

This has prompted me to think about adding a new feature to the next release of the database testing framework. This will allow you to test that a sproc/function parameter is the same data type/length as a column.

If someone changes a column without also changing the parameter – the test will fail – and vice versa.

You will also be able to use it taking a test first approach – ie write the test and then create your sql script. If you do not make the parameter the same as a particular column – the test will continue to fail.

A test would be written in a similar way to the script below and would part of the current set of tests for a given sproc.

It would test that the param @Title has the same data type and length – in this case nvarchar(50) – as the column HumanResources.Employee.Title  – (using AdventureWorks uspUpdateEmployeeHireInfo sproc).

Adding new ‘Rows’, as appropriate, would mean that you could test each sproc param that you expect to be the same as a column.

[Row("@Title", "HumanResources.Employee", "Title")]
public void T06_ParameterAndColumn_AreTheSameDataTypeAndLength(string parameter, string table, string column)
	bool areTheSame = sproc.ParameterSameType(parameter, table, column);
	Assert.IsTrue(areTheSame, "Param and column are not the same");

Let me know what you think and, perhaps, after you have read Alex’s book you might have further suggestions for features that could be added to the framework.

Last modified: 2010-07-24


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: