Introduction
AI assistants can generate SQL surprisingly quickly.
A developer can describe a requirement such as:
"Find the top five customers by total order value
during the previous month."
and receive a complete query within seconds.
The difficult part is not generating SQL.
The difficult part is determining whether the generated SQL is correct, efficient, safe, and appropriate for the target database.
A query can return results and still contain a serious problem. It might use the wrong date range, duplicate rows because of an incorrect join, ignore an important filter, or perform an expensive table scan.
This makes AI-generated SQL a good candidate for systematic benchmarking.
Instead of asking which AI model "writes the best SQL," a more useful approach is to measure several independent dimensions:
Semantic correctness
Result correctness
Query-plan quality
Execution behavior
Resource consumption
Safety
Database cost
This article presents a vendor-neutral methodology for building such a benchmark.
Why AI-Generated SQL Needs Testing
Consider a simple schema:
CREATE TABLE customers
(
id bigint PRIMARY KEY,
name varchar(200) NOT NULL
);
CREATE TABLE orders
(
id bigint PRIMARY KEY,
customer_id bigint NOT NULL,
order_date date NOT NULL,
total numeric(12, 2) NOT NULL,
CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(id)
);
An AI assistant might generate:
SELECT c.name, SUM(o.total) AS total_sales
FROM customers c
JOIN orders o
ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_sales DESC
LIMIT 5;
The query is syntactically valid.
But suppose the requirement was:
Return the top five customers by order value during the previous month.
The generated query does not contain a date filter.
It can execute successfully while producing the wrong business result.
This is why:
SQL executes successfully
!=
SQL is correct
Define What Correctness Means
Before comparing AI-generated SQL, define the expected behavior.
A useful evaluation model is:
AI-Generated SQL
|
+--> Syntax Correct?
|
+--> Schema Correct?
|
+--> Result Correct?
|
+--> Business Logic Correct?
|
+--> Safe?
|
+--> Efficient?
Each category should be evaluated separately.
Build a Benchmark Schema
Create a controlled database schema that resembles real application workloads.
Include relationships such as:
Customers
|
+---- Orders
|
+---- OrderItems
|
+---- Products
You can also include:
One-to-many relationships
Many-to-many relationships
Nullable columns
Date and timestamp fields
Numeric values
JSON columns
Indexes
Constraints
The goal is to create realistic SQL problems without exposing production data.
Create Representative Questions
The benchmark should contain natural-language requirements rather than only SQL syntax exercises.
For example:
Find all customers who placed at least
three orders in the previous 30 days.
Another:
Return the five products with the highest
revenue during the current quarter.
And:
Find customers who have never placed an order.
These questions test different SQL concepts.
Organize Tests by Difficulty
A useful benchmark can have several levels.
| Level | Example |
|---|
| Basic | Filter rows by a condition |
| Intermediate | Join multiple tables |
| Advanced | Aggregation and grouping |
| Complex | CTEs and window functions |
| Production-like | Multiple joins, filters, and business rules |
This makes the results easier to interpret.
Establish Ground-Truth Queries
Each natural-language requirement needs a trusted reference query.
For example:
SELECT
c.id,
c.name,
SUM(o.total) AS total_sales
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.id
WHERE o.order_date >= DATE '2026-07-01'
AND o.order_date < DATE '2026-08-01'
GROUP BY
c.id,
c.name
ORDER BY
total_sales DESC
LIMIT 5;
The benchmark should treat this as a reference implementation, not necessarily as the only valid SQL solution.
Two queries can be structurally different and still be equally correct.
Why Result-Based Validation Is Better
Suppose the AI generates:
SELECT
c.id,
c.name,
SUM(o.total) AS total_sales
FROM orders o
JOIN customers c
ON c.id = o.customer_id
WHERE o.order_date >= DATE '2026-07-01'
AND o.order_date < DATE '2026-08-01'
GROUP BY c.id, c.name
ORDER BY total_sales DESC
LIMIT 5;
The structure is different from the reference query, but the result can still be correct.
Therefore, compare result sets where possible.
Conceptually:
Reference Query
|
v
Expected Result
|
+----------------+
|
AI Query |
| |
v |
Actual Result ---------+
|
v
Compare
This is more useful than comparing SQL strings.
Testing With a Controlled Dataset
Create deterministic test data.
For example:
INSERT INTO customers (id, name)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
INSERT INTO orders
(id, customer_id, order_date, total)
VALUES
(101, 1, DATE '2026-07-05', 500),
(102, 1, DATE '2026-07-10', 300),
(103, 2, DATE '2026-07-12', 900),
(104, 3, DATE '2026-06-20', 1200);
Now the benchmark knows exactly what the correct result should be.
Use multiple datasets when necessary to expose edge cases.
Testing Edge Cases
A good SQL benchmark should include cases such as:
For example, a question about customers without orders should expose incorrect use of an inner join.
Correct:
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.id IS NULL;
An AI-generated inner join could silently return the wrong result.
Testing Date Logic
Date filtering is a common source of subtle SQL errors.
For example:
WHERE order_date
BETWEEN '2026-07-01' AND '2026-07-31'
may behave differently from an explicit half-open interval when the column contains timestamps.
A safer pattern for timestamps is often:
WHERE created_at >= TIMESTAMP '2026-07-01 00:00:00'
AND created_at < TIMESTAMP '2026-08-01 00:00:00'
The benchmark should include boundary cases.
For example:
2026-07-31 23:59:59
2026-08-01 00:00:00
This can reveal whether the generated query handles the requested time period correctly.
Testing NULL Behavior
AI-generated SQL can also mishandle NULL values.
Consider:
SELECT *
FROM customers
WHERE email <> '[email protected]';
Rows where email is NULL do not satisfy the comparison in the same way as ordinary values.
Benchmark questions should include NULL-heavy datasets when the business logic requires them.
Measuring Query Correctness
Create a result-comparison layer.
For example, conceptually:
bool ResultsMatch(
IReadOnlyList<Row> expected,
IReadOnlyList<Row> actual)
{
return Normalize(expected)
.SequenceEqual(Normalize(actual));
}
The normalization step may need to account for:
Row ordering
Numeric precision
Date formatting
Column ordering
Only normalize differences that are semantically irrelevant.
If ordering is part of the requirement, preserve it during comparison.
Testing Query Plans
A correct query can still be inefficient.
Use the database's execution-plan tooling to inspect generated SQL.
For PostgreSQL, for example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
c.id,
c.name,
SUM(o.total) AS total_sales
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.id
GROUP BY c.id, c.name;
For SQL Server, equivalent execution-plan and statistics features can be used.
The benchmark should use the tools appropriate for the target database.
What to Measure in a Query Plan
Useful metrics include:
| Metric | Why It Matters |
|---|
| Scan type | Shows how tables are accessed |
| Estimated rows | Shows optimizer expectations |
| Actual rows | Shows real execution |
| Execution time | Measures observed latency |
| Logical reads | Measures data access |
| CPU time | Measures processor usage |
| Sort operations | Identifies potentially expensive operations |
| Join strategy | Shows how tables are combined |
| Temporary work | Can expose expensive intermediate operations |
Not every database exposes these metrics in exactly the same form.
Use the native execution-plan tooling available for the target database.
Testing Index Usage
Consider:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
A benchmark can compare query behavior with and without the index.
The goal is not to force an index.
The database optimizer should be allowed to select the plan it considers appropriate.
Instead, measure whether the generated SQL gives the optimizer a reasonable query shape.
Measuring Query Cost
Cost should be evaluated at multiple levels.
Database Execution Cost
Measure:
CPU
Reads
Memory
Temporary work
Execution time
Application Cost
Measure:
Connection time
Result transfer
Serialization
Application processing
AI Generation Cost
If the application uses a paid model, also consider:
Input tokens
Output tokens
Number of requests
Retry count
The complete workflow looks like:
Natural Language
|
v
AI Request
|
v
Generated SQL
|
v
Database
|
v
Result
|
v
Application
A query benchmark should identify where resources are being consumed.
Testing Large Datasets
A query that looks efficient on 1,000 rows may behave very differently on 10 million rows.
Create several dataset sizes:
Small
Medium
Large
For example:
10K rows
100K rows
1M rows
The actual sizes should reflect the application's expected scale.
Avoid claiming that a query will scale linearly simply because it performed well on a small dataset.
Benchmarking AI Models
If comparing multiple AI models, keep the test conditions consistent.
For example:
Model A
|
v
Same Prompt
Same Schema
Same Rules
Same Dataset
|
v
Validation
Model B
|
v
Same Prompt
Same Schema
Same Rules
Same Dataset
|
v
Validation
Do not change the database or prompt between models.
Otherwise, the comparison becomes difficult to interpret.
Avoiding Prompt Bias
The benchmark prompt should provide the information a real developer would have.
For example:
You have these tables:
customers(id, name)
orders(id, customer_id, order_date, total)
Find the top five customers by order value
during July 2026.
Do not give one model additional hints that another model does not receive.
If schema information would normally come from an application integration, include it consistently for every model.
Testing SQL Safety
Correctness and performance are not enough.
Generated SQL should also be checked for unsafe behavior.
For a read-only application, the benchmark can reject statements containing operations such as:
DROP
TRUNCATE
DELETE
UPDATE
INSERT
ALTER
when those operations are not part of the requested task.
A simple application-level validation layer can inspect the generated statement before execution.
However, string filtering alone is not a complete SQL-security solution.
Use database permissions and parameterization as additional controls.
Parameterization
AI-generated SQL should not be assembled by directly inserting untrusted user input.
Avoid:
var sql =
"SELECT * FROM customers WHERE name = '"
+ userInput
+ "'";
Prefer parameterized commands:
using var command =
connection.CreateCommand();
command.CommandText =
"""
SELECT id, name
FROM customers
WHERE name = @name
""";
command.Parameters.AddWithValue(
"@name",
userInput);
Parameter APIs differ between database providers, so use the provider's recommended implementation.
Common Mistakes
Comparing SQL Strings
Different SQL statements can produce identical results.
Testing Only Syntax
Valid SQL can still answer the wrong business question.
Using Tiny Datasets
Small datasets can hide performance problems.
Ignoring NULL and Boundary Cases
These often reveal logical errors.
Measuring Only Execution Time
CPU, reads, plans, and result size provide additional evidence.
Giving Models Different Information
This makes model comparisons unreliable.
Executing Generated Write Queries Automatically
AI-generated SQL should not receive unrestricted database permissions.
Troubleshooting Incorrect SQL
When generated SQL fails a test, classify the failure:
Syntax Error
Schema Error
Logic Error
Result Error
Performance Issue
Safety Violation
For a result mismatch:
Compare the generated query with the requirement.
Compare the result set with the reference.
Identify the first incorrect row or missing row.
Check joins.
Check filters.
Check aggregation.
Check NULL handling.
Check date boundaries.
This is more useful than simply recording "model failed."
Building an Automated Benchmark
A reusable benchmark can follow this architecture:
Test Case
|
v
Prompt Builder
|
v
AI Model
|
v
SQL Validator
|
v
Test Database
|
+--> Result Validation
+--> Plan Analysis
+--> Cost Measurement
+--> Safety Validation
|
v
Benchmark Report
A test case could be represented as:
public record SqlBenchmarkCase(
string Name,
string Prompt,
string ExpectedResultFile,
string Database);
The runner can execute each model against the same test cases.
Creating a Benchmark Report
A useful report should not produce only one score.
For example:
| Test | Correct | Safe | Plan | Cost | Result |
|---|
| Customer ranking | Yes | Yes | Good | Measure | Pass |
| Date filtering | No | Yes | Good | Measure | Fail |
| NULL handling | Yes | Yes | Good | Measure | Pass |
| Large join | Yes | Yes | Review | Measure | Review |
This makes the results actionable.
A single "SQL quality score" can hide important failures.
Best Practices
Use Realistic Schemas
Include relationships, constraints, indexes, and realistic data.
Validate Results
Do not assume that executable SQL is correct.
Test Edge Cases
Include NULLs, empty results, duplicate values, and date boundaries.
Inspect Execution Plans
Correct results do not guarantee efficient execution.
Test Multiple Data Sizes
Evaluate behavior as the dataset grows.
Separate Safety From Correctness
A query can be correct but still unsafe for the permissions available to the application.
Use Least-Privilege Database Accounts
Read-only workloads should use read-only credentials.
Automate the Benchmark
A repeatable benchmark is much more useful than manually testing a few prompts.
Advantages
Provides objective evidence about AI-generated SQL quality.
Detects logical errors that syntax checks cannot find.
Measures database performance in addition to correctness.
Helps compare different models using consistent workloads.
Creates reusable regression tests for AI-assisted database development.
Encourages safer execution of generated SQL.
Disadvantages
Building representative datasets requires effort.
Query performance depends on database configuration and hardware.
AI responses can vary between runs.
A benchmark cannot cover every possible SQL requirement.
Maintaining ground-truth queries and expected results requires ongoing work.
Performance results from one environment should not automatically be generalized to another.
Conclusion
AI-generated SQL should be evaluated like any other automatically generated production artifact.
A query that executes successfully is not necessarily correct. A query that returns the correct result is not necessarily efficient. And an efficient query is not automatically safe to execute with unrestricted database permissions.
A meaningful benchmark therefore needs multiple validation layers:
Generated SQL
|
+--> Syntax
|
+--> Result Correctness
|
+--> Edge Cases
|
+--> Query Plan
|
+--> Resource Usage
|
+--> Safety
The most reliable approach is to create a controlled database, define realistic natural-language requirements, establish trusted reference results, execute generated SQL against deterministic datasets, and inspect both results and execution plans.
As the benchmark grows, add real failure cases discovered by developers. A query that once caused a production-like problem can become a permanent regression test.
The goal is not to find a model that produces the most impressive-looking SQL.
The goal is to determine whether AI-generated SQL is correct enough, efficient enough, and safe enough for the workload where it will actually be used.