• SQL Test
• Database Testing

Testing SQL Server Databases Using SQL Test

Writing database tests in SQL

Database unit tests are performed on every database routine, in isolation, to ensure that it always returns the desired result, for each set of inputs. You need to write unit tests for all database modules, such as stored procedures, functions, triggers and views.

SQL Test is a plugin for SQL Server Management Studio (SSMS) that helps you create and run these unit tests, as well as other types of database tests. It is built on the open source tSQLt testing framework for unit testing SQL Server databases and is compatible with all SQL Server editions from 2005 Service Pack 2 onwards. It is implemented at the database level in the form of tables, views, functions, and stored procedures, all of which are stored in a schema separate from the rest of the database objects. The tests themselves are organized into their own schemas, which you can define as needed to accommodate your requirements. As the name “tSQLt” suggests, it allows you to test your SQL code by writing tests in SQL. For example, to test your GetEmployeeManagers procedure, you can write the test-GetEmployeeManagers procedure to ensure that GetEmployeeManagers produces the correct output in all cases.

Although the tSQLt framework is relatively easy to deploy, SQL Test simplifies the process even further, taking care of all the setup and maintenance and so allowing you to focus on building your unit tests. You’ll still need a solid foundation in tSQLt itself so you can take full advantage of the framework and ensure you’re properly testing your database components. Although I introduce you to the basics of building a unit test, my focus is primarily on SQL Test, not on tSQLt, which includes many more components than I can cover here. To learn about tSQLt, I suggest you start with the tSQLt organization itself.

Setting up SQL Test

Before you can start using SQL Test, you must download the installation file and run the setup program. SQL Test is part of Redgate’s SQL Toolbelt, and you can find details about system requirements, licensing, and installing SQL Test in the product documentation.

After you install the application, launch SSMS, connect to a SQL Server instance, and click the SQL Test button on the toolbar, shown in the following figure (at the right side of the toolbar). The SQL Test interface opens in its own tab, which is divided into two main panes.

The tSQLt framework is implemented at the database level, so you must specifically add the database to SQL Test, which will install the tSQLt framework objects (tables views, procedures) into that database and allow you to start writing tests.

The left pane provides the Add Database to SQL Test link (I’ll explain the Test output and Code coverage tabs in the right pane shortly). Click it to launch the Add database to SQL Test dialog box, shown in the following figure:

Simply select the database that you want to test (I used the AdventureWorks2019) and click Add Database.

When you add a database, it launches the Install tSQLt Framework dialog box:

The dialog box provides an overview of the components that SQL Test installs or configures. Before you can install the tSQLt framework, the TRUSTWORTHY property must be set to ON, and SQL CLR must be enabled on the SQL Server instance. SQL Test takes care of both of these settings for you.

The Install tSQLt Framework dialog box also includes the Add SQL Cop static analysis tests check box near the bottom. The check box is enabled by default, which means the SQL Cop tests will be installed, but you can prevent their installation by clearing the check box. For this article, I did not install these tests, initially, but I’ll introduce them to you later in the article.

After you click OK, SQL Test install the tSQLt framework into it, adds the database to the left pane and lists any available unit tests (if you receive an error about the database owner SID, refer to this Stack Overflow discussion for details on how to address it). At this point, since I did not add the tests included with SQL Cop, there are no tSQLt tests showing in the AdventureWorks2019 database:

In SSMS Object Explorer, navigate to the AdventureWorks2019 database, and you will see the new tSQLt schema, which now contains all the tSQLt framework components:

Test output and code coverage results

Even though we currently have no tests to run, you can still click on the AdventureWorks2019 instance in the left pane and then click Run Tests.

The Test output tab displays the results you receive when you run your tests. If a test succeeds, you’ll receive only a generic message. If a test fails, you’ll receive the message that the test outputs upon failure.

The Code coverage tab provides a full breakdown of the code coverage as it pertains to database objects such as functions, triggers, and stored procedures. Code coverage measures how many statements in your database code were executed when you ran your tests and provides the percentage of coverage represented by those statements. To prevent the Code coverage tab from being displayed, just uncheck the Code Coverage check box on the SQL Test toolbar.

Right now, of course, there are no tests, and so the code coverage is 0%. However, you can still see the list of database objects, along with the statement count, covered statement count, and percentage of coverage for each object.

You can click a link in the list to go to the actual object code to see it for yourself. In this way, you have quick access to the definitions for different database objects. For example, if you click the HumanResources.uspUpdateEmployeeLogin stored procedure link, it will take you to the definition of that procedure. The code coverage feature will highlight any statements in the object definitions that were executed during the testing process (currently none are tested).

Unit testing a SQL Server database

It’s time to add our first tSQLt unit test, which will target the HumanResources.uspUpdateEmployeeLogin stored procedure in the AdventureWorks2019 database.

As you can see from the previous figure, the procedure updates login information in the HumanResources.Employee table, based on the data supplied through the procedure’s six parameters, which are configured with different data types. You’ll need to know this information when building your test because when you create a test, you’re actually creating a stored procedure that calls the HumanResources.uspUpdateEmployeeLogin procedure and checks that the output is what you expect.

Adding a custom test to SQL Test

To add a test, click the New test icon (blue cross) on the SQL Test toolbar, or right-click the database and then click New Test. When the New Test dialog box appears, type test_uspUpdateEmployeeLogin in Test Name text box, ensure that AdventureWorks2019 is selected in the Database drop-down list, and type hr_tests in the Test Class text box, as shown in the following figure.

All tests have a name, and a class to which they belong.

• Test Name – A tSQLt test must begin with the word test (case sensitive). You can include it as a freestanding word (test, followed by a space) or as part of the rest of the name with no space. If it is a freestanding word, SQL Test lists the name in the left pane without the word test. If I had called my test test uspUpdateEmployeeLogin then it would be listed in the left pane as uspUpdateEmployeeLogin. Conversely, if I had called it uspUpdateEmployeeLogin, then the underlying tSQLt test would be called test uspUpdateEmployeeLogin.
• Test Class – The tSQLt framework organizes tests into classes, which map to specific database schemas. The hr_tests class corresponds to the hr_tests schema that has now been added to the database. Our test is part of that schema, and you can view both the schema and procedure in SSMS Object Explorer, where they’re listed along with the other database objects.

When you click Create Test, SQL Test adds the hr_tests schema to the AdventureWorks2019 database and adds the test_uspUpdateEmployeeLogin stored procedure to that schema. In addition, SQL Test adds the corresponding class and unit test to the database instance tree in the left pane of the SQL Test tab, as shown in the following figure.

We can run the test now, even though we’ve not yet written it, simply by selecting it, or right-clicking on it, and clicking Run Tests. Similarly, we can run all tSQLt tests that exist in the database, or all tests within a class. Of course, the test will fail:

In the Test output pane, you will see the following failure message:

Test Procedure: [AdventureWorks2019].[hr_tests].[test_uspUpdateEmployeeLogin] on localhost
[hr_tests].[test_uspUpdateEmployeeLogin] failed: (Failure) TODO:Implement this test.

Writing the test code

When you add a new test, SQL also opens a new query tab in SSMS that contains the initial test definition, or more precisely, the fundamental components needed to get started building the test, as shown in the following figure. As you can see, a tSQLt test is just a stored procedure and the initial definition includes an ALTER PROCEDURE statement, along with plenty of placeholder text. The only line of code, in the Assert section of the test, simply calls the tSQLt.Fail stored procedure, which raises a ‘failure’ error when called, resulting in failure of the test case with the message “TODO: Implement this test”.

The procedure definition includes three sections for defining the code—Assemble, Act, and Assert—which are explained by the accompanying placeholder text. That said, it might be easier for you to understand what’s going on by seeing actual code. A full discussion of how to build tSQLt tests is beyond the scope of this article, but hopefully the following demo of how to create a simple test will familiarize you with some of the basic concepts.

Assemble

The Assemble section is where you set up the foundation needed to carry out the test. The script in this section creates a fake table based on the HumanResources.Employee table and then creates a second table and populates it with the results expected when running the test, as shown in the following code:

The Assemble section starts by calling the tSQLt.FakeTable stored procedure, which recreates the original table (Employee) but without any constraints. The FakeTable procedure makes it easier to test database components that modify data, which is the case for the uspUpdateEmployeeLogin procedure. This way, when running your test, you do not touch the data in the original table, and you do not have to contend with constraints such as foreign keys or check constraints. At the same time, you can reference the original table name within your test, which means you don’t have to modify object definitions to carry out a test.

After creating the fake table, the Assemble section populates the table with the seed data necessary to perform the test. You do not have to populate every column in the table, only those relevant to your test. After populating the table, the code creates the Expected table to hold the values that the test is expected to produce. In other words, these are the values that should exist in the fake Employee table after running the test. The Expected table will then be compared with a table that holds the actual results.

Act

After the test components have been assembled, the Act section executes the uspUpdateEmployeeLogin stored procedure, passing in the six parameter values. The procedure will update the values in the fake table, not those in the original Employee table. The BusinessEntityID and CurrentFlag values are the same as the seed data, but the other four columns will be updated with new values, as shown in the following code:

After the Act section executes the stored procedure, it runs a SELECT…INTO statement that creates the Actual table and populates it with the updated data in the employee table.

Assert

The final section, Assert, runs the tSQLt.AssertEqualsTable stored procedure, which compares the before and after data. To this end, the procedure takes two parameters: the Expected and Actual tables:

The AssertEqualsTable procedure compares the two tables to determine whether their values are equal. If they are equal, the test passes, otherwise the test fails.

Saving the test

Once you’ve finished writing or editing a test, you can execute the ALTER PROCEDURE statement to update the definition in the database. You can then rerun the test in SQL Test, and it will incorporate your changes.

I’ve included the full test definition here in its entirety so you can see how all the pieces fit together (and to make it easier to copy-and-paste the code):

Running the test in SQL Test

To run your new test in SQL Test, right-click the test in the database tree in the left pane and then click the Run Test. The test should run successfully and show only a generic message in the right pane. SQL Test also adds a green check mark to the test listing in the left pane, as well as the nodes above it in the database tree, as shown in the following figure.

The Code coverage tab in the right pane will display the test coverage information. It should show that overall test coverage is now around 1% and that the uspUpdateEmployeeLogin stored procedure is 66.67% “covered”.

If you click on that procedure’s link, you’ll also discover that SQL Test has highlighted the procedure’s UPDATE statement, as shown in the following figure.

In most cases, you might want to verify that your test will catch errors as expected. One way to do this is to update the test definition with a value that should fail. For example, I temporarily changed the LoginID value for the test’s Expected table to adventure-works\login13, rather than adventure-works\login12. I then reran the ALTER PROCEDURE statement, returned to the SQL Test interface, and reran the test. The following figure shows the results I received after I ran the test.

As you can see, the test failed. The left pane now shows red Xs rather than green check marks, and the right pane shows the failure message generated by the test. The message includes the results returned by the AssertEqualsTable procedure. The first row in the table shows the expected results, and the second row shows the actual results. Together they indicate that the LoginID values are different when they should be the same.

After I confirmed that the test will properly generate an error, I changed the LoginID value for the test’s Expected table back to adventure-works\login12 and then ran the ALTER PROCEDURE statement once again. I then returned to SQL Test and reran the test, which now runs successfully.

Expanding your code coverage

That’s all there is to running your first custom test in SQL Test. Of course, this was only a simple example. There is much more to tSQLt than what’s covered here, but this should be enough to help you understand some of the basic concepts that go into building a test and allow you to start building out your collection of tests and improving your code coverage. If you’re looking for example unit tests, you might want to check out the tutorial from the tSQLt organization.

Adding the SQL Cop static analysis tests

As discussed earlier, the SQL Cop static analysis tests will, by default, be added when you install SQL Test. I unchecked that box initially, but you can still add the suite of tests simply by right clicking the database instance and selecting Add SQL Cop static analysis tests.

You’ll now see the SQLCop class as a node beneath the database instance. This class corresponds to the SQLCop schema that was added to the database when the tests were installed. All SQL Cop tests are part of that schema.

The SQLCop tests are just like any other tSQLt tests but slightly different in nature. Our unit test executed the stored procedure and checked the result. The code coverage was then a measure of how many statements in the procedure were tested, and full coverage is when it tests every line. SQL Cop tests are static code analysis tests that don’t execute any user code, and so will not have any effect on code coverage, at least not for any of your user objects in the database. Instead, they perform static code analysis of the metadata, to check for issues such as missing keys, fragmented indexes, and badly sized data types.

Having added the SQLCop tests, we can now run all the tSQLt tests that in the database, or just all tests within the SQLCop class. When you run a collection of tests, SQL Test executes them one at a time and displays the results in the right pane and the information returned depends on the number of tests you run and whether any of the tests failed:

• If you run a collection of tests and they all succeed, the results include only a brief message for the collection that ran.
• If you run a collection of tests and at least one of those tests fails, the results include only the messages returned by the failed tests.
• If you run an individual test and it succeeds, the results include only a brief message about the test that ran.
• If you run an individual test and it fails, the results include the failure message returned by the test.

The following figure shows the SQL Test tab after running all tests in the SQLCop class and the Test output tab shows the message returned by the failed SQLCop tests:

The database tree in the left pane shows which tests succeeded (green checkmarks) and which ones failed (red Xs). In this case, 13 tests failed.

The Test output pane displays the failure messages returned by each failed test. You can see the message for the first two failed tests, the Agent Service test and the Column Name Problems test. In the latter case, it identifies the column names that don’t adhere to the best naming practices. In this case, the culprit appears to be the use of periods in the object names.

The Code coverage pane does, somewhat confusingly, show that our code coverage has increased even though these tests don’t execute any user code. In fact, as you can see in the next figure, the only extra code coverage you are seeing is for an “internal” SQLCop procedure, not for any of the user objects in the database you’re testing. Again, you can click on any of these objects in the list, to see its definition:

To access a test definition itself to get a better sense of its logic, right-click the test in the left pane, and click Edit Test. SQL Test opens the test in its own tab. The following figure shows the definition for the test Column Name Problems test.

As you can see, again, this tSQLt test is just a stored procedure. In this case, the procedure runs a SELECT statement that uses a regular expression to determine which column names include non-alphanumeric characters. If any columns are identified, the procedure returns them as part of the failure message. You can edit this test if you wish, just like any other tSQLt test, although I’m not recommending you do that for the SQL Cop tests.

Making the most of SQL Test

SQL Test is a basic tool that operates on the backs of SSMS and the tSQLt framework. There are few moving parts and the learning curve is relatively low. But the tool does make it easy to set up and manage your tSQLt tests, freeing you up to focus on the test development itself. In addition, because SQL Test works with standard tSQLt tests, you can access and run those tests directly, without going through SQL Test. In this way, you can automate your database testing process as part of an overall DevOps strategy.

Despite the advantages that SQL Tests provides, you still need to have a good understanding of how to build unit tests that fully leverage the components available to the tSQLt framework. The more adept you are at tSQLt, the more effectively you can create unit tests that check your database for potential issues before deploying the database to production. SQL Test can certainly make the process easier, but it’s still up to you to get those tests to do what they’re supposed to do.

 

Tools in this post

SQL Test

Unit test add-in for SQL Server Management Studio

Find out more

SQL Toolbelt Essentials

The industry-standard tools for SQL Server development and deployment.

Find out more