Get Help

Set is empty

You expect the query to return no row at all.

Example

You expect the provided query does not return anything. For example, if you log errors into a table and you need an automated way to check no errors are being added:

Tests:
- Suite: Smoke Tests
  Name: Table dbo.Errors is empty
  Data Source: DWH
  Query: SELECT * FROM dbo.[Errors]
  Expectation: set is empty

Queries

One: Data source and Query (First data source and First query work too). A Second data source or Second query on the test is an error — the test ends with Error before anything runs.

Result

If no rows are returned, the test result is Passed.

After reading at least one row, CAT marks the test as Failed. By default it stops right there — see below for how much more it reads.

If the underlying provider returns an exception, the result is Error.

Properties it reads

Property Default Meaning
Maximum errors logged 1 How many of the unexpected rows go into the failure message; 0 keeps data out of the logs altogether. CAT stops reading once it has that many rows. See Failure message.
Maximum sample column length 25 Characters kept per column in that sample.
Log number of errors false Read the whole set and report the total number of rows — see below.
Output details false Experimental — every offending row goes to the SQL Server output, not only the sample. See Test properties.

Remarks

CAT stops reading as soon as it has the rows it needs for the message, so the query itself is what costs time on a large table. Let the database do the work: restrict the statement to what proves the rule is broken (SELECT TOP(1) …, LIMIT 1, …) — the syntax depends on the provider.

More rows in the failure message

With Maximum errors logged at 1 (the default) or 0, CAT closes the connection to the provider as soon as it has received the first row. CAT is an indicator that points at a problem, not a tool that returns every row breaking a rule. Raise Maximum errors logged to see more — the maximum is 50, enough to spot a pattern in the offending data.

Log number of errors

Log number of errors: true makes CAT read the entire set instead of stopping at Maximum errors logged rows — that setting then only decides how many rows the message shows. The failure message carries the total, No row was expected, but exactly 25 rows exist. instead of at least 1 row exists, and the total is on the result as Number of errors — a database output with that column receives it. See Results.

Tests:
- Name: There are no new errors in the error log
  Data Source: DWH
  Query: |
    SELECT  *
    FROM    Audit.ErrorLog
    WHERE   ThrownOn > DATEADD(DAY, -14, SYSUTCDATETIME())
  Expectation: set is empty
  Maximum Errors Logged: 5
  Log Number of Errors: true # default is false

If the query returns 25 rows, CAT puts the first 5 into the failure message as the sample, reads the rest, and reports No row was expected, but exactly 25 rows exist.

← Overview
Set is not empty →