Natural language to SQL

While Ask Finch answered product questions, a different request arrived from the store managers' meeting. Managers wanted to ask questions about their own numbers in plain words, such as which paint colors sold best at my store in October, or how many returns did we have for cordless drills this quarter, without waiting for the reporting team. A language model can translate such a question into a SQL query. Nikolai's first reaction was to ask who reads the result, and how much harm a wrong answer does. A manager who reorders stock based on a wrongly written query loses money; one who sees a wrong number and asks the reporting team loses only time. The design had to make the second outcome far more likely than the first.

How natural language to SQL works

The pattern, often called NL2SQL or text-to-SQL, has four parts. The model is given a description of the tables and columns it may use, a few examples of questions with correct queries, and the user's question, and is asked to return one query. The application then checks the query, runs it under a restricted identity and shows the result with the query beside it. The quality of the description matters more than anything else: a model writing SQL against 900 cryptically named tables guesses; one writing against six well-named views with described columns rarely needs to.

A schema made for the model

Hana and the reporting team built a reporting schema with six views: sales by store, product and day; returns with a reason code; stock snapshots; product attributes; stores; and a calendar. Each view has readable column names, such as StoreName and NetSalesAmount rather than STR_NM and AMT2, and each column has an extended property with a one-line description. The prompt is built from those properties, so it stays accurate when the views change.

EXEC sys.sp_addextendedproperty @name = N'Description',
     @value = N'Net sales in Canadian dollars after discounts and returns, excluding sales tax',
     @level0type = N'SCHEMA', @level0name = N'rpt',
     @level1type = N'VIEW',   @level1name = N'vSalesDaily',
     @level2type = N'COLUMN', @level2name = N'NetSalesAmount';

-- Build the schema description the prompt uses
SELECT v.name AS view_name, c.name AS column_name, t.name AS data_type,
       CAST(ep.value AS nvarchar(400)) AS description
FROM sys.views AS v
JOIN sys.columns AS c ON c.object_id = v.object_id
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
LEFT JOIN sys.extended_properties AS ep
       ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = N'Description'
WHERE SCHEMA_NAME(v.schema_id) = N'rpt'
FOR JSON PATH;

The US version of the same view describes amounts in US dollars; the reporting team keeps the descriptions in the language and currency of each company's books, because a model told nothing about currency will happily add mixed amounts.

Examples in the prompt

A handful of question-and-query pairs in the prompt, called few-shot examples, teach the model the house style: which view holds what, how fiscal months are defined, and that store names come from the store view. Hana chose eight examples covering each view and the commonest traps, such as returns being negative amounts and the fiscal year starting on February 1. Adding the fiscal-year example alone fixed most wrong answers to questions about this year.

Never trust the generated query

A generated query is untrusted input written by a program that does not know the consequences. It might be wrong, slow, or, if the question contained something malicious or the model misbehaved, an attempt to change data. Defenses go in layers, so a failure in one is caught by the next.

CREATE USER nl2sql_reader WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::rpt TO nl2sql_reader;
DENY  SELECT ON SCHEMA::catalog TO nl2sql_reader;

-- Compile without executing: returns the result columns, or an error
SELECT error_number, error_message, name, system_type_name
FROM sys.dm_exec_describe_first_result_set(@generatedSql, NULL, 0);

-- Run as the restricted user
EXECUTE AS USER = 'nl2sql_reader';
EXEC sys.sp_executesql @generatedSql;
REVERT;

The restricted identity is the layer that matters most, because it holds even if validation is fooled. A string check that looks for the word DELETE can be bypassed in many ways; a user with only SELECT on six views simply cannot delete anything. Elena also pointed the feature at the read-only replica of the reporting database, by connecting with ApplicationIntent=ReadOnly, so a badly written query can slow only the replica.

A validator in miniature

The validation layer is simple to describe and hard to make perfect, which is why it is not the main defense. Hana's checks in the prototype were: exactly one statement; it begins with SELECT or WITH; it references only names from the allowed list; and it contains no semicolons except a trailing one. Nikolai built a small model of the whole flow in Python with an in-memory database to test the checks against a list of awkward queries.

import re, sqlite3

ALLOWED = {"vsalesdaily", "vstore"}
def validate(sql):
    s = sql.strip().rstrip(";")
    if ";" in s: return "rejected: more than one statement"
    if not re.match(r"(?is)^(select|with)\b", s): return "rejected: not a SELECT"
    names = {n.lower() for n in re.findall(r"(?i)\b(?:from|join)\s+([a-z_][a-z0-9_.]*)", s)}
    bad = {n for n in names if n.split(".")[-1] not in ALLOWED}
    return f"rejected: not allowed {sorted(bad)}" if bad else "ok"

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE vstore (StoreId INT, StoreName TEXT);
CREATE TABLE vsalesdaily (StoreId INT, Category TEXT, SalesDate TEXT, NetSalesAmount REAL);
INSERT INTO vstore VALUES (12, 'Store 12'), (15, 'Store 15');
INSERT INTO vsalesdaily VALUES (12,'Paint','2026-10-03',1840.5),(12,'Paint','2026-10-17',2210.0),
                               (15,'Paint','2026-10-09',990.25),(12,'Tools','2026-10-09',3120.0);
""")
tests = [
    "SELECT s.StoreName, SUM(d.NetSalesAmount) FROM vsalesdaily d JOIN vstore s ON s.StoreId = d.StoreId "
    "WHERE d.Category = 'Paint' AND d.SalesDate LIKE '2026-10-%' GROUP BY s.StoreName ORDER BY 2 DESC",
    "SELECT * FROM vsalesdaily; DELETE FROM vsalesdaily",
    "SELECT * FROM customers",
    "UPDATE vstore SET StoreName = 'x'",
]
for t in tests:
    verdict = validate(t)
    print(verdict)
    if verdict == "ok":
        print("   ", db.execute(t).fetchall())

ok
    [('Store 12', 4050.5), ('Store 15', 990.25)]
rejected: more than one statement
rejected: not allowed ['customers']
rejected: not a SELECT

The validator caught everything in the test list, but Nikolai wrote three more queries that slipped past it, including one that read a table through a function call rather than after FROM. They failed anyway, because the restricted user had no permission on those objects. That was the point of the exercise: validation catches the common cases cheaply and gives a clear message, and permissions catch the rest.

Showing the query

Every answer shows the query that produced it, in a collapsible panel, and a one-line reading of it generated from the query, such as net paint sales by store for October 2026, largest first. Managers who do not read SQL still read the one-liner, and it is where they spot a wrong interpretation: a question about returns answered with sales, or this year read as the calendar year. In the prototype test, managers spotted 6 of the 9 wrong queries this way before acting on them.

Prompting for SQL

The prompt for query generation is stricter than the prompt for answers. It gives the schema description as JSON, the eight examples, today's date and the fiscal calendar rule, and the manager's store from the session, and it asks for a JSON reply with two fields: sql, holding a single T-SQL SELECT statement, and reading, holding a one-line description of what the query returns. It tells the model to use only the listed views and columns, to filter to the manager's store unless the question names another store the manager may see, to return at most 100 rows with TOP, and to reply with an empty sql field and an explanation in reading if the question cannot be answered from the views. Temperature is 0, because the same question should give the same query.

Generated queries are also logged with the question, the validation result, the compile result, the row count and the duration. The reporting team reviews queries that failed or ran slowly, and several became new examples in the prompt or new columns in the views. Over time, Nikolai expected the most common questions to become ordinary saved reports, which is a good outcome: natural language to SQL is often the fastest way to discover which reports people actually need.

Not every question should be answered this way, and the feature says so. Questions about individual customers, employees or prices before they are announced are outside the reporting views by design, so a model asked about them can only reply that it cannot answer. Questions that need judgment, such as whether a store should order more of a product, get a query for the underlying numbers and a note that the decision belongs to the buying team. Setting those boundaries in the views and the prompt, rather than hoping the model infers them, kept the feature useful without letting it drift into decisions it cannot make well.

AI-assisted tools and their security impact

Natural language to SQL also appears in developer tools. GitHub Copilot in SQL Server Management Studio and in Visual Studio Code, and Copilot in Microsoft Fabric, can write queries from descriptions, and agents in those tools can connect to databases through Model Context Protocol servers, which expose a database's schema and query tools to the model. The DP-800 outline asks candidates to interpret the security impact of these tools. The same principles apply: the tool acts with the permissions of the identity it connects as, so a developer connected as db_owner hands that power to the agent. Connect such tools with a least-privilege identity, to development or test data, and review generated changes before running them; these tools are covered in MSAZ-300.