Red-gate SQL Test

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. 💡


About erictummers

Working in a DevOps team is the best thing that happened to me. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Tooling and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.