If anyone has heard me talk about SQL development, they know I like unit testing a little too much.  I don’t mind, though.  During my years as a DBA, I developed an appropriate fear and loathing of bad code, so I suppose this is the carryover from all those years of experience.  I wanted to do a short post about how I’ve used inline table value functions to create the smallest “unit sizes” I could come up with.  I will go over how I might refactor a larger query to refactor commonly reused code to make it smaller and wrap a few tests on it.

In this example, we’re going to use the 2016 Adventureworks database.  Consider the following code.  Ignoring the fact that this should probably be in a look-up table, based on the case statement, this will probably see a certain amount of reuse.  So let’s throw it in a TVF and do some tests on it.

Now, below, we have a nice inline TVF, which allows us to reduce the amount of code referenced in the original query.  Note that this code was written in a scalar UDF in the AdventureWorks database.  I opted to convert it to a table valued function for performance reasons.  I will cover the reasons in another post later.

Now let’s set up a couple tests.  I like to start out writing a script in SSMS and then add it to the testing framework.  As you can see, there are several selects that can test different scenarios.  We are testing expected values, an invalid value and of course NULL.  I like to use comments and column names to document expectations.  If you get a lot of tests in one script, this saves a lot of time trying to figure out what’s broken.

Even though there are a lot of different types of tests you can set up, I like going to the Scalar value.  I find I can just write my test queries in such a way that I can make use of this test type.  You can use it with multiple tests per column in a row and multiple results-sets and so on.

Of course it wouldn’t be any fun if we didn’t see the passed tests.

So, to put it all together, here is the usage of the TVF after some testing with example output.  Much better!  And now if the logic changes, we have tests wrapped around it and a single place to change the logic.

I hope this is a helpful view into how you can use TVFs in particular to do unit testing.  You can use these TVFs to encapsulate complex where clause logic or computed column logic and so on.  It allows you to build smaller components that are better tested.  And you don’t run into some of the issues that come with normal TVFs and scalar functions.