There is a framework called tSQLt that helps you to do it.
There is also a tool from Red Gate Software called SQL Test that provide a nice UI on top of it, integrated in SQL Server Management Studio.
What about using tests for learning TSQL?
I think that it is not unreasonable to design a TSQL course where each lesson is made of a set of tests that you need to make pass. This idea of learning by making tests pass is used in the nand2tetris course that I am doing with the friends of the Cambridge Programmer's Study Group.
In this post, I try to create an example using tSQLt.
The database I use is from the Training Kit: Querying Microsoft SQL Server 2012.
Installing tSQLt
First run the following SQL statements.
)_+-+Microsoft+.png)
Execute the tSQLt.class.sql script provided by tSQLt.
Creating the test
First, you need to create a test class that we call Tests.
)_+-+Microsoft+.png){kind=small}
Then, we create a test.
)_+-+Microsoft+.png)
)_+-+Microsoft.png)
Running the test
You can run the test individually using the tSQLt.Run or alternatively you can run all the tests in the database using tSQLt.RunAll.
)_+-+Microsoft+.png){kind=small}
You can also create a keyboard shortcut to run all the tests easily.
You can see that the test fail!
)_+-+Microsoft+.png)
You can make the test pass simply adding the DISTINCT keyword in the select statement.
)_+-+Microsoft+.png)
Run the tests and they all pass.
)_+-+Microsoft+.png)
I really like the idea of learning by making tests pass.
What do you think?
)_+-+Microsoft+S.png)
)_+-+Microsoft+S.png)
)_+-+Microsoft+S.png)
