In the coding/software development space, you have likely heard, at least once, the term “unit test”. Let’s take a look at a definition of a unit test for software development:
Unit testing is a software development process in which the smallest testable parts of an application, called units, are individually and independently scrutinized for proper operation. This testing methodology is done by software developers and sometimes QA staff during the development process. The main objective of unit testing is to isolate written code to test and determine if it works as intended.
But, how does that translate to the database development space? Let’s see if Unit Test for SQL Server answers that for us.
Installation
You can get Devart’s SQL Unit Testing tool by clicking on the “Get Trial” button. Doing so will also include the whole suite of SQL Server Management Studio extensions, which adds a lot of extra value.
Note: Make sure you have installed SQL Server Management Studio 2012 or higher to proceed.
After you have completed the installation, head straight to SQL Server Management Studio and open it. Once opened, establish a connection to a SQL Server instance and go to any of your databases, and right-click on it.
This is what you’ll be seeing:
Since it’s our very first time checking Unit Test, then let’s go straight to “Add New Test”:
You’ll immediately see this, so let me fill requested information to keep moving forward.
Important note: the tool will check if tSQLt framework is installed and will warn you that the unit test will not be created if that’s not the case, so let me go ahead and install it then.
This is what we get after hitting “Yes”:
So the block of code you see is for altering a stored procedure, so I guess that your unit test will be baked inside this stored procedure. If I take a look at the stored procedures inside my database, I can see a bunch with the prefix “tSQLt”, so now we know why the tool insisted on the installation then.
If we take a look at the comments within that block of code, we can see that it has 3 sections:
Assemble: This section is for code that sets up the environment. It often contains calls to methods such as tSQLt.FakeTable and tSQLt.SpyProcedure, along with INSERTs of relevant data.
Act: Execute the code under test like a stored procedure, function, or view and capture the results in variables or tables.
Assert: Compare the expected and actual values, or call tSQLt.Fail in an IF statement.
If I go ahead and execute the stored procedure exactly as it is, this is what I get:
Since it’s my first attempt at something like this, I have no idea how to proceed, but lucky for me, there are informative links right in the stored procedure template that I’m going to check out before I’m able to figure out what to do next.
Let me give you a tip if you simply don’t know where to start: after you install Unit Test, a new menu option will be made available right there in SQL Server Management Studio, called “Unit Test”, and this will allow you to install a sample database that has some test cases baked in for you to look around and get a feeling of how things work:
Now, if instead of clicking “Install Sample Database” you click “View Test List”, then this is what you will see:
These are all the available tests within the sample database, and if you click the icon being pointed by the green arrow, then you will run all of them, so let’s check those out.
Ok, so this gives me the outcome of each test so that I can see which ones succeeded and which failed. But what if I want to take a look at a failed test to address whatever it is that I have to address? How can I do that? Simply right-click on the row with the failed test and click “Open Test”.
With the stored procedure open that contains the failed test, I’m going to tamper with it just a bit to force a successful test case, just to confirm that’s how that works:
And yes, that worked like a charm, cool!
Let me continue with the one I originally tried to make:
This is a very basic example, so I will save it in my stored procedure and try to manually execute it without using the GUI that I showed previously.
So if I try to execute my test case stored procedure directly, then I’m getting this weird error.
But guess what? To test it properly I have to run the stored procedure called tSQLt.Run and pass my stored procedure as a parameter, just like this:
As you can see, it will tell me that my test case failed because “Expected: <5> but was: <10>”. I also get a fancy table with the summary of the test case, and I assume that as you add more test cases, this table will also grow if you specify to run the entire class instead of an individual stored procedure test case.
Now let me go to my stored procedure and change that 5 for a 10 and run it again to see if I get a different output:
Bingo, it worked! So I have shown you how to run unit tests manually and using the GUI.
Final thoughts
● Obviously, this was just a very brief look at the tool, but I encourage you to continue to dig deeper by checking Devart’s official documentation center.
● After looking at what the tool offers, I think we can safely say that the definition we saw earlier, on unit tests in the software development space, matches in the database development space. With that said, I see this as a precious tool for database developers to raise the bar in the quality department.
● Suppose you are currently not covering unit tests within your database development workflow. In that case, it goes without saying that it is something that takes time to build for your particular environment. However, once you get a good grasp on it, it is worth it!
Top comments (0)