In today's post we will install tSQLt and run a simple test. To start
download tSQLt, unzip the zipfile into its own folder. Here is what is inside that zip file, we are looking at executing the tSQLt.class.sql file later on
Open up SSMS, connect to the database server where you want to create the tSQLt database. We need to enable the CLR on the server where you are running your tests because tSQLt is using some CLR functionality.
Execute the following command to enable the CLR on your box
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Now we need to create the database and make it trustworthy, execute the following
CREATE DATABASE tSQLt
GO
USE tSQLt
GO
ALTER DATABASE tSQLt SET TRUSTWORTHY ON;
GO
Go to your unzipped folder, open up the tSQLt.class.sql file, make sure that you are connected to you tSQLt database, execute the code
You should see something like this in the output once all the code has run
Installed at 2015-10-05 17:58:53.360
(1 row(s) affected)
+-----------------------------------------+
| |
| Thank you for using tSQLt. |
| |
| tSQLt Version: 1.0.5686.18945 |
| |
+-----------------------------------------+
After the code has completed, run the following
EXEC tSQLt.Fail 'congratulation, we are ready to go to the next step'
You should see the following output
Msg 50000, Level 16, State 10, Procedure Fail, Line 51
tSQLt.Failure
Let's create a sample database so that we can do some testing
Execute the following code
CREATE DATABASE UnittestExamples
GO
USE UnittestExamples
GO
CREATE TABLE dbo.test1(id int)
CREATE TABLE dbo.test2(id int)
INSERT dbo.test1 VALUES(1)
INSERT dbo.test2 VALUES(2)
INSERT dbo.test1 VALUES(3)
INSERT dbo.test2 VALUES(3)
INSERT dbo.test1 VALUES(4)
INSERT dbo.test2 VALUES(5)
GO
USE tSQLt
GO
Now let's run a couple of tests
tSQLt ships with an assertion to test if 2 tables are the same, the assertion is named AssertEqualsTable
So let's see if these two tables are the same, execute the following
EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'UnittestExamples.dbo.test2'
Here is the output
Msg 50000, Level 16, State 10, Procedure Fail, Line 73
tSQLt.Failure
As you just saw, will get an error, now what will happen if you compare the table against itself, run this to find out.
EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'UnittestExamples.dbo.test1'
There was no error now, this is expected
What happens if we test a table against a non existing table?
EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'UnittestExamples.dbo.test112'
Msg 50000, Level 16, State 10, Procedure Fail, Line 51
tSQLt.Failure
Msg 208, Level 16, State 1, Line 2
Invalid object name 'UnittestExamples.dbo.test112'.
I want to point something important out. Comparing two tables is usually doing for small lookup tables, you create a table inside your unit test with values that you expect to match the table in the database, if these tables don't match, then you have a problem. You wouldn't want to compare two million row tables in a test.
Let's do another assertion, let's see if an table exists.
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'
Msg 50000, Level 16, State 10, Procedure Fail, Line 51
tSQLt.Failure
That fails as expected, now let's try it with a table that exists
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.test1'
That ran fine as expected.
Let's clean up by dropping the two tables we created earlier.
DROP TABLE dbo.test1 , dbo.test12
Creating a test class
What you want to do with tSQLt is group all your tests together, for example if you are testing a stored procedure, you can execute just those set of tests. When creating a test class in tSQLt, a new schema will be created, you can easily verify this. Execute the following
USE tSQLt
GO
SELECT * FROM sys.schemas
WHERE schema_id BETWEEN 5 AND 1000
name schema_id principal_id
tSQLt 5 1
As you can see, the only schema the query returns is the tSQLt schema. Now let's create a new test class, you do this by executing the NewTestClass stored procedure, if we wanted to create a test class named WebFlags, we would do it like this
tSQLt.NewTestClass 'WebFlags'
Running the following code again, you will now see an additional schema in the output
USE tSQLt
GO
SELECT * FROM sys.schemas
WHERE schema_id BETWEEN 5 AND 1000
name schema_id principal_id
tSQLt 5 1
WebFlags 6 1
Let's create a couple of test stored procedures with the examples we used before
First we need to recreate the test1 table again
USE UnittestExamples
GO
CREATE TABLE dbo.test1(id int)
INSERT dbo.test1 VALUES(1)
INSERT dbo.test1 VALUES(3)
INSERT dbo.test1 VALUES(4)
GO
Now we will create 3 stored procedures. It is very important that you name each stored procedure beginning with 'test ', if you don't do this then tSQLt will skip the proc. Here is the code to create our 3 stored procedures
USE tSQLt
GO
CREATE PROCEDURE WebFlags.[test test1 table has all correct values]
AS
CREATE TABLE dbo.test2(id int)
INSERT dbo.test2 VALUES(2)
INSERT dbo.test2 VALUES(3)
INSERT dbo.test2 VALUES(5)
EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'tSQLt.dbo.test2'
DROP TABLE dbo.test2
GO
CREATE PROCEDURE WebFlags.[test SomeTable table exists]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'
GO
CREATE PROCEDURE WebFlags.[test test1 table exists]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.test1'
GO
To execute these procs, all we need to do is run the following
tSQLt.tSQLt.Run 'WebFlags'
Here is the output
[WebFlags].[test SomeTable table exists] failed: (Failure) 'UnittestExamples.dbo.SomeTable' does not exist
[WebFlags].[test test1 table has all correct values] failed: (Failure) Unexpected/missing resultset rows!
|_m_|id|
+---+--+
|< |1 |
|> |4 |
|= |3 |
|> |5 |
|> |2 |
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name |Dur(ms)|Result |
+--+----------------------------------------------------+-------+-------+
|1 |[WebFlags].[test test1 table exists] | 3|Success|
|2 |[WebFlags].[test SomeTable table exists] | 6|Failure|
|3 |[WebFlags].[test test1 table has all correct values]| 33|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 3 test case(s) executed, 1 succeeded, 2 failed, 0 errored.
-----------------------------------------------------------------------------
As you can see, on top you will get the name of the tests that failed, also included is the error message
[WebFlags].[test SomeTable table exists] failed: (Failure) 'UnittestExamples.dbo.SomeTable' does not exist
[WebFlags].[test test1 table has all correct values] failed: (Failure) Unexpected/missing resultset rows!
|_m_|id|
+---+--+
|< |1 |
|> |4 |
|= |3 |
|> |5 |
|> |2 |
Below that you will get a list of each that that ran with an indication if the test was successful or not
|No|Test Case Name |Dur(ms)|Result |
+--+----------------------------------------------------+-------+-------+
|1 |[WebFlags].[test test1 table exists] | 3|Success|
|2 |[WebFlags].[test SomeTable table exists] | 6|Failure|
|3 |[WebFlags].[test test1 table has all correct values]| 33|Failure|
Finally you get a summary
Test Case Summary: 3 test case(s) executed, 1 succeeded, 2 failed, 0 errored.
You can also run all these tests by running the following
Right now since you only have 1 test class, the result is the same if you run all tests or the tests for the WebFlags class
That's all for today, in the next post we will take a look at tSQLt available assertions and how they are used
The list of all the unit test posts can be found here: Unit testing in SQL Server, a list of all the posts
