The usual way to test SQL Server data is a folder of T-SQL scripts. Someone writes SELECT COUNT(*) WHERE CustomerID IS NULL for the three tables that broke last quarter, schedules them in SQL Server Agent, and the other 300 tables go untested.
The scripts aren’t wrong. They just never get finished.
Why hand-written checks stall
DataKitchen’s Purview benchmark counted what full coverage takes: 20 tables and 1,000 columns came to 2,526 tests at about 2.5 per column. At 30 minutes per hand-written test, that’s about 1,250 hours. Most SQL Server databases are wider than 20 tables.
Hand-written checks also only test what someone thought of. A column that has always been 100% populated has no null check, because nobody expected it to break.
Generate tests from a data profile
The alternative is to let the data tell you what normal is:
- Data profiling. Read every column once: null share, distinct values, min and max, lengths, patterns, frequent values.
- Generation. Turn each observed fact into a test with a threshold. A column that was never null gets a null test. A column with five distinct codes gets a valid-value test.
- Review. Deactivate what doesn’t matter, tighten what does.
- Add business rules. Write only what the data can’t show.
Here’s one column, both ways:
| Hand-written | Generated | |
|---|---|---|
Null check on Orders.CustomerID | SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID IS NULL; plus a threshold, a schedule, and an alert | Required Entry test, generated because the profile found no nulls |
Valid Status codes | WHERE Status NOT IN ('O','S','C','R'), updated by hand when a code is added | Value Match test from the profiled list |
| Duplicate orders | A GROUP BY ... HAVING COUNT(*) > 1 query | Duplicate Rows test on columns the profile picks as the key |
A whole table, before and after
Before: the checks a careful engineer writes for a dbo.Orders table. Each query returns rows only when something is wrong.
-- Required customer
SELECT OrderID FROM dbo.Orders WHERE CustomerID IS NULL;
-- Unique order numbers
SELECT OrderNumber, COUNT(*) AS copies
FROM dbo.Orders
GROUP BY OrderNumber
HAVING COUNT(*) > 1;
-- Valid status codes
SELECT OrderID, Status FROM dbo.Orders
WHERE Status IS NULL OR Status NOT IN ('O', 'S', 'C', 'R');
-- No negative totals
SELECT OrderID, TotalDue FROM dbo.Orders WHERE TotalDue < 0;
-- Every order has a customer
SELECT o.OrderID
FROM dbo.Orders AS o
LEFT JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NOT NULL AND c.CustomerID IS NULL;
-- Loaded in the last day
SELECT MAX(OrderDate) AS latest_order
FROM dbo.Orders
HAVING MAX(OrderDate) < DATEADD(day, -1, SYSDATETIME());That’s six checks for one table, before the scheduling, history, and alerting around them. They cover the columns someone thought about.
After: one data profiling run on the same table, then test generation. TestGen generates tests like these when the profile supports them:
| Hand-written check | Generated test type | Generated when the profile shows |
|---|---|---|
| Required customer | Required Entry | No nulls in CustomerID |
| Unique order numbers | Unique Values | Every OrderNumber distinct, in a table over 500 rows |
| Valid status codes | Value Match | A short list of Status values |
| No negative totals | Minimum Value | The lowest TotalDue in the profile, used as the baseline |
| Loaded in the last day | Recency | Recent dates in OrderDate |
| Every order has a customer | Reference Match | Not generated: you pick the two tables and columns |
Review what comes back. Minimum Value fails below the profiled minimum, so if the profile already found a negative total, set the threshold to 0 yourself. The columns nobody wrote a check for get tests too, such as Pattern Match on a formatted order number or Percent Missing on an optional column. The orphaned-customer check stays yours, because a profile of one table can’t know which other table it should match.
How DataOps TestGen does it
TestGen connects to SQL Server with a read-only SQL login, profiles 55 characteristics per column, and flags 32 kinds of hygiene issue, such as placeholder blanks and dates stored as text. Test generation picks from 34 test types and sets thresholds from the latest profile. Every test runs as T-SQL inside SQL Server; you never see it unless you open the query.
For rules a profile can’t infer, TestGen has 19 business-rule and custom SQL types. Reference Match checks that values exist in another table; Aggregate Balance checks that grouped totals match a reference table. You fill in the tables and columns. The test harness, history, and alerting already exist.
Then schedule it. Runs can start on a TestGen schedule or from SSIS, SQL Server Agent, or any scheduler through the REST API. See adding checks after an SSIS package.
The SQL Server setup answer has the grants and a walkthrough. Open source is free for one user, one connection, and one project. Start at the SQL Server install page.