Database Unit Testing for SQL Server using tSQLt
I am a consultant at Amazon Web Services, and this is my personal blog. The opinions expressed here are solely mine and do not reflect the views of Amazon Web Services (AWS). Any statements made should not be considered official endorsements or statements by AWS.
In this blog, I am sharing the key points that I noted while learning the basics of database unit testing for SQL Server.
tSQLt must be installed on the SQL Server database.
- Download the .zip file from its website.
- Unzip that file, there will be mainly 3 SQL files.
- First Enable CLR from the first file.
- Second, set the database trustworthy from the script given in the link
- Third, run
- These 3 steps will install
tSQLt on your database.
Creating a Test Class
- A Test Class is basically a database schema with some special properties applied so that
tSQLt can recognize them as a test class.
- A Test Class can contain many Test Cases. (Note: A Test Case is nothing but a Stored Procedure.)
- To create a Test Class, we must run the below command.
EXEC tSQLt.NewTestClass 'MyTestClass';
Creating a Test Case
- Creating a test case is easy. You only need to create a stored procedure on your test class schema.
- The procedure name must begin with "test".
Running Test Cases
- RunAll - RunAll executes all test cases on all test classes.
- Run - Run is a versatile procedure for executing test cases. It can be called in three ways:
- With a test class name
- With a qualified test case name (i.e. the schema name and the test case name)
- With no parameter
- Creating a Test Class, Test Case & Running them all