---
title: "Set is empty"
description: "You expect the query to return no row at all"
url: "https://docs.justcat.it/reference/tests/expectations/set-is-empty/"
---
# Set is empty


## 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:


**Properties**



Suite
: Smoke Tests

Name
: Table dbo.Errors is empty

Data Source
: DWH

Query
: SELECT * FROM dbo.[Errors]

Expectation
: set is empty





**YAML**


```yaml
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](https://docs.justcat.it/reference/tests/failure-message/ "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](https://docs.justcat.it/reference/tests/properties/ "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.

> **🐱:** **Common confusion.** CAT really only checks there is no result. So if your query is `SELECT COUNT(*) FROM SomeTable` and the expectation is `set is empty`, the result will always be `Failed`, because one row (with the number of records) is always returned — regardless of whether the table has rows or not.



### 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](https://docs.justcat.it/reference/tests/results/ "Results").

> **🙀:** **This has a significant cost in performance** — the whole set is read. Leave it off unless something consumes the number.




**Properties**



Name
: There are no new errors in the error log

Data Source
: DWH

Query
: ```sql
  SELECT  *
  FROM    Audit.ErrorLog
  WHERE   ThrownOn > DATEADD(DAY, -14, SYSUTCDATETIME())
  ```

Expectation
: set is empty

Maximum Errors Logged
: 5

Log Number of Errors
: true





**YAML**


```yaml
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.`

## Related

* [Failure message](https://docs.justcat.it/reference/tests/failure-message/ "Failure message") — the structure of the message and the `Maximum errors logged` values in detail.
* [Set is not empty](https://docs.justcat.it/reference/tests/expectations/set-is-not-empty/ "Set is not empty") — the opposite check.