Decide pass or fail with checks

The check types, operators and result paths a test can check.

In short

Checks decide whether a test passes: a row count, a cell value, an error raised and more. A run with checks passes when all of them pass; a run without checks passes when the server raised no error.

Before you start

• Who: adding or changing checks needs Edit on the project.
• Plan: every plan.
• First: save the test and run it once so you can click a cell to write its check.

Add a check

1. Open the test in the editor.
2. Press "Add check" and choose the check type, or click a cell in a result grid to add a Cell value check for it.
3. Choose an operator and the expected value. For a cell or a column, write the path (see below).
4. Press "Save test" and run it.

Take a suggested check

1. In the test editor, press "Run now". The answer shows under the editor; nothing is saved yet.
2. Read "Suggested checks" under the answer: checks this run would pass, read from its answer only.
3. Press "Add" on a line, or "Add all", and the check joins the test's checks. A check the test already has is not suggested again.
4. Want more? Press "Draft checks with AI". AI reads only the column names, types and row count of this answer, never a value, and proposes checks you add the same way.
5. Press "Save test".

• What is suggested: the exact number of rows (or at least one row when the answer was cut off), columns with no empty value, columns with no repeated value, the order the SQL asks for with ORDER BY, the rows a change touched, a routine's return value and output values, and a time limit about three times this run.
• The suggestions come from rules over the answer. No AI reads your data and nothing leaves the run.
• Keep only what must stay true: a count that grows with real data is better as a minimum - change "equals" to "at least" after adding it.

The checks

Operators: equals, does not equal, greater than, at least, less than, at most, contains, does not contain, matches regex, exists and does not exist. Numbers compare as numbers.

Paths

A path addresses part of a result as $[set][row].Column. Both indexes start at 0 and default to 0, so $.Total is the Total column of the first row of the first result set. Clicking a cell writes the path for you.

Good to know

• An error with no check expecting it always fails the run.
• Whole-column checks and Row exists look only at captured rows (200 by default, 1,000 at most). Unique on a 5,000-row result proves only the first 200 are unique.
• Error raised counts only an error the database server itself raised. A timeout or an unreachable database fails it.

Example dbo.CreateInvoice must reject a negative amount. Run it with Amount -5 and it fails with a constraint violation - the right outcome. Save it as a test with an Error raised check expecting must be positive, and it passes. Duplicate it with Amount 120 and No error to prove both sides. Both roll back, so no invoice is left behind.

If something goes wrong

• "Result path … is not valid: write it as $[set][row].Column": fix the path, or click the cell to write it for you.
• A check fails on a large result although the data looks right: it sees captured rows only. Raise Rows to capture.

Related

• /help/sql-tester/case-editor
• /help/sql-tester/what-changed
• /help/sql-tester/reading-a-result