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.

sqltest

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.

install.tsqlt.framework

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.

create.tsqlt.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

About erictummers

My work as a recruited developer changes almost every month. 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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s