Covering Data Consulting, LLC, Minneapolis, MN

From the blog

Getting Tactical with Unit Tests: Inline functions

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.

TVF_snippet1

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.

TVF_snippet2

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.

TVF_snippet3

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.

TVF_snippet4

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

TVF_snippet5

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.

TVF_snippet6

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.

Leave a Reply

Your email address will not be published.

Services

Performance Tuning

When your employees can’t work because of a poor performing application, your business can lose thousands of dollars in productivity. Worse yet, you can lose customers on a client application. Since a large portion of application performance issues trace back to the database, you need a professional who can quickly[…]

Learn More

Database Design

Your data is some of your most important assets, yet some businesses are hand-cuffed by poorly performing databases. Application development is difficult and many developers are tempted to have tools auto-create the databases. Don’t compromise your future data needs by skimping on your database design. We can work with your[…]

Learn More

SQL Server Health Checks

You know your database server has problems, but you don’t know where to start.  The sys-admins blame the code and the developers blame the hardware.  We can come in and identify your issues and work with your staff to get the database working again.  Plus, we will provide detailed information[…]

Learn More

Database Solution Architecture

You’ve done the indexing and performance tuning.  You’ve run out of options, but you still need more out of your database.  We can help you maximize the use of your existing database instance to save money and get you back on track. Contact now »

Learn More