Last time, in Identifying Test Cases #1, we looked at some simple examples of identifying test cases for SQL statements. This time, we're going to look at a more complex example in the form of a database view.

Our Example
Suppose we want to know which of our employees are under the age of 18. In many areas, employment rules are different for those employees, and our application will need to find them. We might write a view like this:

CREATE VIEW HumanResources.MinorEmployee AS
    SELECT EmployeeId, DATEDIFF(year, BirthDate, GETDATE()) Age

It takes a lot of practice to write test cases for SQL code, and much of that practice happens on the job. One of the most important aspects in writing test cases is simply identifying what test cases to write. Normally I start by writing my first test case before I touch the code at all, but today we're coding to look at code first and then talk about what test cases are needed. The examples here will be using the tables in the AdventureWorks database for SQL Server.

Example 1 - Expressions

SELECT AVG(DATALENGTH(Comments)) AS AvgLen FROM Production.ProductReview

We've seen FakeTable in tSQLt in action in a few demos now. Did you know that you can use FakeTable to fake a view?

You use FakeTable on a table when you want to test a procedure (or function or view) that relies on a table. It prevents you from needing to concern yourself with all of the other constraints a table might have, such as foreign keys to other tables or not-nullable columns which have nothing to do with your procedure. This is particularly useful if you have a schema where many tables are interrelated through foreign keys because you can focus on the procedure you're testing instead of trying to create test data for dozens of unrelated tables.

Similarly, you would want to fake views.