Part six of ten. AI-written SQL fails in a way that plain bad code never does. It runs, it returns a number, and the number is wrong. Here is one that came out 28 times too big.
The number
While writing chapter 6 of my new book, I asked an assistant for sales by territory. Nothing exotic. Total sales and order count for 2024, from the header table, with product detail from the line items.
It wrote a clean query. It joined the header to the detail and summed the header total.
On AdventureWorks2025, the Northwest territory has 2,061 orders. The query reported 7,775. The 2024 total went from 6,763,100.58 to 190,392,567.96.
Every order had been counted once per line item. No error. No warning. A number that was ready to paste into a slide.
Four questions an interviewer will ask
These are from chapter 6. Answers short enough to say out loud.
1. Where do AI coding assistants struggle?
They struggle with what they can’t see: your schema, your data volumes, your business rules and your team’s unwritten conventions. They also struggle with long, tangled code and anything newer than their training. Their mistakes look like working code.
Why it matters. Most data bugs live in business rules. “Active customer” means something specific at your company, and no model knows it unless someone writes it down.
What the interviewer wants. Gaps named as missing context, not as the model being dumb. Missing context is fixable, and that’s the point.
2. What is text to SQL?
Text to SQL turns a question in plain language into a SQL query. “Top ten customers by sales last quarter” becomes a SELECT with joins, filters and an ORDER BY. The query runs, and the result comes back as a table or a sentence.
Why it matters. It opens data to people who don’t write SQL. It also moves the risk, because now someone who can’t read the query is trusting its result.
Watch out. Ambiguous questions produce confident queries. “Last quarter” can mean calendar or fiscal, and the model picks one without asking unless you tell it to ask.
3. How do you test that an AI-written query returns the right rows?
Compare it with something you trust. Run it next to a known-good query or a report total, and compare the results with EXCEPT in both directions. Then check row counts, totals and a few rows by hand.
Why it matters. A wrong query that runs is more dangerous than one that fails. The 28 times too big number above is what that looks like.
Watch out. Test the edges too: empty results, NULLs, the first and last day of a period, and duplicate keys. EXCEPT ignores repeated rows, so group by every column and compare the counts when repeats matter.
4. Why can AI-written code be correct and still slow?
Because correctness and performance are different problems. The model writes a query that returns the right rows without seeing your indexes, row counts or plans. It can wrap a column in a function, or pick a pattern that scans a billion rows.
Why it matters. On a small test table, everything is fast. The same query on production data can time out or block other work.
What the interviewer wants. The execution plan and a test on realistic volumes. Name one trap, such as a filter that can’t use an index.
Do this in five minutes
Reproduce the mistake yourself, so you can describe it from memory rather than from a blog post.
Paste this into any AI assistant:
Write a SQL Server query for total sales and order count by territory for 2024. Use Sales.SalesOrderHeader and include product detail from Sales.SalesOrderDetail.
Check the reply. Does it join the detail table and still use COUNT and SUM from the header? If so, it made the mistake.
Then ask: does this join repeat any rows? Watch whether it fixes the problem or defends it. That reaction tells you more than the first answer did.
One fix to refuse, by the way. SUM(DISTINCT TotalDue) looks clever and is wrong, because two orders can have the same total and DISTINCT throws one away.
Why the book helps here
The pattern above is one of ten questions in one chapter. The chapter also covers reviewing AI code, giving a model your schema, and semantic layers. It covers SQL injection from generated code, and what to do when an AI-written change breaks production.
Every answer carries the part most cheat sheets skip. What the interviewer is listening for, and where the neat answer stops being true.
100 AI Interview Questions and Answers for Data Professionals has 100 of them across ten chapters. There are also 184 key terms and a thirty-minute scored mock interview. It’s in Kindle (also on Amazon.in), paperback and audiobook.
Part seven is about the support ticket that gives an AI agent orders, and watching it obey.
A query that runs is not a query that is right, it is a query that did not complain.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
