Book Review - Tribal SQL

How many of you have written a book? Probably not many. Yet how people working with SQL Server have thought that they knew better, or that someone else would really appreciate how you do something? Probably a lot more.

Trying to encourage new authors to share a small, manageable amount of knowledge on a subject, like the lightening talk of the book world, is one of the aims of the book I'm looking at in this post - Tribal SQL.

It came about from a wish of Jen and Sean McCown (The Midnight DBAs) to let people read the insight and enthusiasm which is present in the less often heard corners of the SQL Community. I think they've done a good job in encouraging new voices.

The subjects covered are as varied as the topics - and all are in an informal, insightful manor, which encourages this both as an introduction to areas into which you might not have previously strayed, and also as a reference material / revision of best practice for those who have.

I'd strongly recommend this book to Accidental DBAs, who may find themselves suddenly confronted with a lot of new requirements and challenges, not the least of which is a distinct lack of training budget! (I'd also encourage attendance at SQL Community events, like SQL Saturday Exeter which is being held in March in the South West of England, and is free to attend).

This book doesn't restrict itself to the technical subjects (although it certainly does cover them, for example in Storage Internals and Data Compression) but also covers softer skills, such as project management skills, and how to get a stellar performance review. There's more basic introductory topics too, such as Stephanie Locke's chapter on reports, and how to gather requirements, implement a good relationship with others in the organisation, and produce them a great report.

I found the breadth really refreshing - the authors clearly want you to learn, and the fact that they've donated their royalties to charity really underlines their wish to help those around them.

If you aren't convinced, Red Gate have currently got a sample chapter on their website (UPDATE: as Melanie pointed out in the comments below, there are three sample chapters from the book on the Simple Talk website - Agile Database Development, Guerrilla Project Management for DBAs and SQL Server Storage Internals 101), and you can buy the book from Amazon - there's even an e-book available! The code samples are all available from the Tribal SQL website.


Disclosure: I was sent a review copy of this book free of charge by Red Gate Publishing, but otherwise received no remuneration or reward for this review.

tSQLt adds Function Mocking to the unit testing armoury

The latest release of tSQLt (version V1.0.5137.39257) was published last week, and you can download this version in the usual place. If you're using SQL Test with tSQLt, you can upgrade SQL Test to the latest tSQLt release by following these instructions.

(Note, if you've not come across tSQLt before, it's a database unit testing framework written in T-SQL, and there's a great article explaining why you might want to use it here. Red Gate have written SQL Test to help integrate the power of tSQLt with the SQL Server Management Studio environment. This article assumes you're familiar with tSQLt, but if not I'd strongly encourage you to look at it for unit testing your databases.)

I am pleased to see that this release adds function mocking, as well as a simpler way to rename classes than the previous work around, and checking for a specific error number to tSQLt.ExpectException.

Let's look at that function mocking in a bit more detail. Consider a function which should add two numbers together. We would want to unit test it, and we can do that in the normal way, but a test on a stored procedure which needs to isolate from that function needs to call our new tSQLt method tSQLt.FakeFunction.


CREATE FUNCTION dbo.AddTogether (@a INT, @b INT)
RETURNS INT
AS
BEGIN
   RETURN @a+@a
END;
GO

CREATE PROC Maths AS
  SELECT dbo.AddTogether(1,2) AS SumOfNumbers
GO

Now, we can see there is a bug in the function above (and if we run the Stored procedure, we will get a value of 2 returned), but let's ignore that function and proceed to test our stored procedure. Remember, because we are isolating from our dependencies, we don't expect out stored procedure to fail its unit tests.

The way that FakeFunction works is that you need to supply it with a stub function to use in place of your function to be isolated. I suggest you put them in the test class. So let's create a simple function that returns a static value - 3.

EXEC tSQLt.NewTestClass @ClassName = N'MathsTests' -- nvarchar(max)
GO

CREATE FUNCTION MathsTests.Fake (@a INT, @b INT) 
RETURNS INT
AS 
BEGIN
   RETURN 3
END
GO

Now we are ready to create our test:

CREATE PROC MathsTests.[test I get a value of 3 returned when I add 1 and 2] 
AS
--Assemble
EXEC tSQLt.FakeFunction @FunctionName = N'dbo.Addtogether', -- nvarchar(max)
            @FakeFunctionName = N'MathsTests.Fake' -- nvarchar(max)

CREATE TABLE MathsTests.Expected (SumOfNumbers INT)
CREATE TABLE MathsTests.Actual (SumOfNumbers INT)
INSERT MathsTests.Expected (SumOfNumbers)
VALUES (3)

--Act
INSERT MathsTests.Actual
EXEC tSQLt.ResultSetFilter 1,'exec dbo.Maths'
--Assert
EXEC tSQLt.AssertEqualsTable @Actual = 'MathsTests.Actual', 
                                               @Expected = 'MathsTests.Expected'
GO

If we run this test, we get a successful test, because the stored procedure under test returns a row with a value of 3, the expectation, as our code under test (the stored procedure) is isolated from the function which has the bug in it. Of course, you would want to ensure that any module from which you isolate is properly tested, so that you catch issues such as this, but this somewhat contrived example allows you to see how the true cause of the failure can then be found more easily.

The ability to fake functions in this way is a great addition to the unit test writer's armoury.