Sql Server Stored Procs vs Linq with C# and unit testing

Question

I'm going to run into a situation where I am, where the stored proc vs Linq is going to come up. I know I'm asking this in the DBA stackexchange so most of you might say use stored procs, but I'm curious on the unit test suites that exist in SQL Server 2012.

Visual Studio 2015 has a test explorer part of the IDE and MSTest framework which makes writing unit tests really easy, and honestly a lot of "business" logic can be done in SQL/Linq so that's the part that needs to be tested most often.

I'm open to using stored procs but coming from C# my expectation is:

1) Be able to mock some data up just for said tests (in memory not disk for both speed and to make sure it always stays around with the test vs someone deleting it or moving it sometime in the future)

2) Call the proc against mocked data, and then compare the results and get some nice visual feedback that the test failed/succeeded.

3) Seeing all unit tests listed out nicely in some kind of Test Explorer and able to run them all with a click of a button or have them grouped in various ways.

My question is, does this or something similar to this exist on Sql Server to help aid in unit testing stored procedures?

Answer 1

Take a look at tSQLt (http://tsqlt.org/) which is a great free tool to create unit tests in SQL Server.

You can fake tables (by executing tSQLt.FakeTable) within your tests and then create mock data as you describe. Tests are themselves stored procedures and any changes made as part of your testing suite are rolled back post test.

You run your tests by executing the tSQLt.RunAll stored procedure and it gives you visual feedback summary of your test results.

To get started, navigate to http://tsqlt.org/downloads/ and download. Running Example.sql will create an example database called [tSQLt_Example], create the testing framework and some example tests to see what is possible.

Also available is the Redgate paid product SQL Test (http://www.red-gate.com/products/sql-development/sql-test/). This is a GUI wrapper around tSQLt but is not required to use tSQLt.