To test or not to test. That is the question. Or is it? Shouldn’t we always test? That was the question posted here. It’s a big question so let’s break it down a bit.

Upgrades

You have a system and are about to upgrade some or all of it. For example upgrading an instance from SQL Server 2012 to 2014. At this point testing is essential. Unfortunately testing every part of even a small application is probably not going to be possible. At that point I would do what I would call a “working” test. Is the basic system working. Run through your common tasks start to finish. Don’t test every option, just the main path.

When doing a SQL Server upgrade there is also some required reading. Basically you want to read the Backward Compatibility entry for the new version (here is the 2014 one, and the 2016 one) and make sure that you don’t have anything in any of the lists (breaking changes, behavior changes etc).

Application Code

Here we are looking at new/changing application code (because of course anything existing has already been tested right?). In this case everything should be tested. You should have test cases set up ahead of time and run through at least the majority of your affected code. Preferably at this point you should also have someone else do a similar check. This code is going to be in place, probably for a fairly long time, and used by a large number of people. You want to make sure it works and works well.

One of the things that can really help with this is to generate a set of unit tests that are easily repeatable. Steve Jones recommends using tSQLt for testing your TSQL code (SQL Server only I’m afraid). But by doing this you can run through a fixed set of tests quickly and it will really aid in regression testing (testing everything, say before doing an upgrade).

Features/Configurations

Even more than application code changes you want to test new features and configuration changes thoroughly. If for example you decide to start working with columnstore indexes for the first time you will need to test every piece of code that touches the affected tables. Use the unit tests that you generated to test your application. These features are probably new to you (and possibly new in the platform) and will probably have some gotchas you didn’t expect. As for configuration changes, you are talking about something that can affect your whole system, possibly significantly. The rule of thumb is to test, and test carefully. There are some changes that you won’t really see until you get into an active system (possibly only your production system) but that isn’t an excuse not to try them in a test environment first.

Ad hoc queries that reference/affect user data

When you have code that affects your user data you generally need to test it, even, and perhaps especially, because it’s Ad Hoc. Now that being said if you are running the same piece of code, over and over again, just with different parameters, then you probably don’t need to worry about testing each time.

For example you need to delete one or more Ads from the AdList table every quarter.

DELETE FROM AdList WHERE AdName 
IN ('January 2015 Ads','February 2015 Ads','March 2015 Ads')

At that point you’ve already tested the code (you are just changing fixed strings) and are probably fairly safe just running the code (assuming you have good backups just in case).

One easy way to test a DELETE, UPDATE or INSERT is to change them to a SELECT and run them, then confirm that the number and type of rows you expect are returned.

You might think that you don’t need to test SELECTs because they don’t actually change any data. However you are running the code for a reason right? Let’s say you are doing research for your manager, who will in turn hand this data to their manager and so on. You test to make sure that you aren’t getting the wrong data (or blocking others from collecting their data).

Ad hoc queries that reference/affect system data

This is possibly the one exception to the “test everything” rule. You are running information queries on system data. The important thing here is to get back the data you expect. If the query is something simple (querying a system view) then you are probably ok as long as you’ve checked what the view/columns really mean. If the query is complex (say hitting 3 or 4 system views with calculations on the columns returned) then you may want to run a few tests just to make sure you are going to get back the data you expect.

Summary

In summary, yes, you want to test everything. If it’s important enough for you to write it and run it then it’s important enough for you to test. That doesn’t mean you have to spend enormous amounts of time testing every branch of every line of code however. But some level of testing needs to be done.

Automated unit testing is your friend here. With the advent of DevOps and Continuous Integration you will see more and more applications and methods of quickly and easily testing your code. Of course that does require having a good test environment and data to go along with it, but that’s a whole different discussion.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication Tagged: microsoft sql server, testing