SQL database unit testing in SSMS
Unit Test for SQL Server is based on a free and open source tSQLt framework, which allows creating and running unit tests in T-SQL.
With the tSQLt framework, you can benefit from the following:
- Write your code and run unit tests within a single tool
- Use T-SQL code for creating unit tests
- Run unit tests in isolation from other objects
- No data cleanup required - unit tests are automatically executed in a transaction log
You can write SQL unit tests directly in SQL Server Management Studio.
The guide describes the following:
- Install the tSQLt framework
- Create a unit test
- Install predefined test samples
- View test lists
- Run unit tests
Install the tSQLt framework
Before you create unit tests, you need to install the tSQLt framework. To do that, in Object Explorer, right-click the required database and select Unit Test > Install Test Framework. In the Install Test Framework wizard that opens, choose the database for which the framework will be installed and click Install. Alternatively, you can initiate the tSQL framework installation from the Unit Test > Install Test Framework menu. For more information about the tSQLt framework installation, see How to install the tSQLt framework.
Create a unit test
On the Unit Test menu, click Add New Test. In the Add new test dialog that opens, do the following and then click Add Test:
- Choose a connection and a database for which you want to create a unit test
- Specify the name of the unit test
- Choose or enter the class name to which the unit test will be added
Then, the progress window will display that the operation has been complete, and you can click Finish to close the wizard. For more information, see Creating new SQL unit tests through tSQLt.
After the test has been created, a new SSMS document opens with a query template to manage the test, where you can write T-SQL code for your test. The template is divided into three blocks:
- Assemble: Set up the environment and declare database objects to be tested.
- Act: Execute the tests against the code and capture the results in variables or tables if any.
- Assert: Compare the actual and expected values.
Install predefined test samples
dbForge Unit Test provides a list of predefined test samples so that you can quickly start working with unit tests.
On the Unit Test menu, click Install Sample Database. In the Install Sample Database dialog that opens, choose the connection on which you want to install the database, enter the database name, and click Install.
When the installation is complete, click Finish to close the dialog. The database will be displayed in Object Explorer. For more information, see Installing and running the example tests.
View test lists
dbForge Unit Test provides a built-in Test List Manager to view and manage unit tests visually:
- Add a new test
- Run the selected or checked tests
- Delete the test
- Open the test procedure script in a new SQL document in order to view or modify it if needed
- Add, rename, or delete a test class
- Run all tests in bulk
- Run all tests against multiple or selected databases
To open the Test List Manager, on the Unit Test menu, select View Test List. For more information about how to manage tests, see Viewing test list.
Run unit tests
In dbForge Unit Test, you can run all tests either from Object Explorer or Test List Manager. In the latter, you can also run the selected unit tests or those having check marks next to them.
The Test Results grid displays all running tests either with the Succeeded or Failed statuses. For any failed tests, you can view an error message that displays the expected and actual results.
At the bottom of the results grid, you can view the total amount of passed, failed, or checked unit tests. For more information, see Running SQL database unit tests.
