Using T-SQL Assertions in Database Unit Tests
Important This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer

Using T-SQL Assertions in Database Unit Tests

In a database unit test, a Transact-SQL (T-SQL) test script runs and returns a result. Sometimes, the results are returned as a results set. You can validate results by using test conditions. For example, you can use a test condition to check how many rows were returned in a specific result set or to verify how long a particular test took to run. For more information about test conditions, see Using Test Conditions in Database Unit Tests.

Instead of using test conditions, you can also use T-SQL assertions, which are RAISERROR statements in a T-SQL script. In certain circumstances, you might prefer to use a T-SQL assertion instead of a test condition.

Using T-SQL Assertions

You should consider the following points before you decide to validate data either by using T-SQL assertions or by using test conditions.

  • Performance. It is faster to run a T-SQL assertion on the server than to first move data to a client computer and manipulate it locally.

  • Familiarity with language. You might prefer a particular language based on your current expertise and therefore choose T-SQL assertions or C# or Visual Basic test conditions.

  • Complicated validation. In some instances, you can build more complex test validation more just in C# or Visual Basic and validate your tests on the client.

  • Legacy validation libraries. If you already have code that performs validation, you can use it in a database unit test instead of using test conditions.

The RAISERROR Statement

You can directly use T-SQL assertions on the server by using the RAISERROR statement in your T-SQL script. Its syntax is:

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)


@ErrorMessage is any user-defined error message. You can format this message string similar to the printf_s function.

@ErrorSeverity is a user-defined severity level from 0 – 18.


The values '0' and '10' for the severity level do not cause the database unit test to fail. You can use any other value in the range 0 - 18 to cause the test to fail.

@ErrorState is an arbitrary integer from 1 – 127. You can use this integer to differentiate between occurrences of a single error that is raised at different locations in the code.

For more information, see "RAISERROR (Transact-SQL)" ( in the SQL Server Books Online. An example of using RAISERROR in a database unit test is provided in the topic, How to: Write a Database Unit Test that Runs within the Scope of a Single Transaction.

See Also

© 2016 Microsoft