Writing unittests for sql objects was one of my blind spots. Red-gate has a tool called SQL Test that should fill in the gap. I have given it a try.

SQL Test is a graphical user interface for tSQLt, a unittest framework for Microsoft Visual Studio. The GUI makes it much easier to use. It integrates with SQL Server Management Studio.

The first step to using the framework is to add the SQL objects to your database. This also enables the CLR.

SQLCop can be installed together with tSQLt. These are database best practices like enable sql agent and data and log files on different disks. My development instance failed 7 tests. How about your production instance?

With new test you create a new stored procedure to run as a unittest. This can be done in your own schema, just make sure the extended property tSQLt.TestClass is set to 1 on the schema.

The new test is made mostly of placeholder comments. You can use the following framework functionality:

1. AssertEqual, checks variables are equal,
2. FakeTable, creates a temporary table based on the table to fake, this isolates your unittests,
3. AssertEqualsTable, checks the contents of two tables is the same,
4. SpyProcedure, fake stored procedure, logs the parameters used in the call,
5. ApplyConstraint, creates the constraint in the parameter on the FakeTable

More details in the references.

Final thoughts

SQL Test helps sql server development. It should be part of every (database) developer.
Keep in mind that it actually is tSQLt and SQL Cop with some GUI. You don’t need a license for all instances, just for the GUI. 💡

References

• Red-gate SQL Test
• tSQLt Tutorial
• Sql Cop