If you are serious about dealing with data within SQL Server you need to be just as serious about unit testing the changes that you are making.
These changes can include new or modified Stored procedures, functions, triggers, tables and the like. Basically anytime you change something that will affect your database you should write a unit test for it. As I always have this saying when it comes to making changes for any system:
“When would you like to find that change – at 3am when the PROD online banking system is crashing or how about 3pm when it’s only on your laptop and you’re the only one who knows you just wrote bad code?”
In fact I’ve written about how important unit testing is in my previous post:
https://sqlmastersconsulting.com.au/SQL-Server-Blog/want-to-bring-devops-to-your-database/
After reading that post you will have realised how important it is to unit test your data and code so here’s how to install tSQLt to your database.
First – go to the tSQLt website:
and go to download the framework:
The direct URL is http://tsqlt.org/download/tsqlt/
You will get a zip file downloaded with some files in it:
There is:
Example.sql
Which is a script that allows you to create a database and run your first unit tests.
It’s a great example and you can follow the documentation at https://tsqlt.org/user-guide/quick-start/
SetClrEnabled.sql
Which is a script to enable CLR. This is partly why you should only run tSQLt on your DEV or selected TEST databases.
tSQLt.Class.sql
This is the framework itself.
We now run the SetClrEnabled & tSQLt.Class scripts and voila – we have tSQLt in our database and can now write or run all the unit tests we want.
I guess at this stage you might ask – why do I need to enable CLR – well let’s say you don’t enable it and just run tSQLt.Class.sql – you will get the following error:
Once you enable CLR, you then run the tSQLt.Class.sql script:
So now you can start writing your own tests.
The tSQLt framework adheres to the three A’s of unit testing:
Assemble
Act
Assert
The tSQLt website has some great examples and tutorials and honestly – the learning curve is very small – I’d say most Data Professionals can be up and running unit tests within an hour.
As you will be most familiar with your code – so the unit test is now the easy part.
So go visit
https://tsqlt.org/user-guide/tsqlt-tutorial/
or these other great sites:
https://www.simple-talk.com/sql/t-sql-programming/getting-started-testing-databases-with-tsqlt
http://www.pluralsight.com/courses/unit-testing-t-sql-tsqlt
https://www.itprotoday.com/sql-server/getting-started-test-driven-design-sql-server
http://d-a-green.blogspot.co.uk/search/label/tSQLt
http://datacentricity.net/tag/tsqlt/
and in no time at all you will be writing your very own unit test.
Honestly – unit tests have saved my database and my continued employment time and time again and I hope they will for you too.
In my opinion tSQLt is that sweet spot of easy to use and comprehensive enough to make a difference.
Try it out today!!
Leave a Reply