Writing T-SQL With an AI Assistant: What to Check Before You Run It

Generated SQL can look polished while updating the wrong rows. An AI assistant supplies a draft, and you still need to verify its objects, logic, execution plan, and failure behavior before running it.

Define the Result Before Asking an AI Assistant

Write the business rule in plain language before reviewing the statement. Identify which rows qualify, what changes, and what must remain unchanged. For a report, define grouping and duplicate handling. For a write, define the transaction boundary and expected failure behavior.

I ask for that sentence before checking the generated syntax. A valid statement can implement the wrong request perfectly. The assistant cannot resolve an unspoken rule about refunds, time zones, or duplicate customers merely by arranging SQL keywords convincingly.

Give it a minimal schema description and synthetic examples when possible. Keep confidential values, credentials, and unnecessary production text out of the prompt. The code review should be based on the approved schema and rule, not on an assumption that a fluent answer has access to your database.

Check Every Object and Column the AI Assistant Names

Inspect the actual tables, columns, types, nullability, keys, and schema names. A join on two similarly named columns does not prove they represent the same relationship. Verify the key that prevents duplication and the foreign-key or business relationship that connects the rows.

Use a scratch session for this small review table. It creates an indexed UTC timestamp and a status column. The metadata query reads its real columns from tempdb. For application objects, inspect sys.tables and sys.columns in their actual database and schema instead.

CREATE TABLE #ReviewOrders
(
    OrderID int NOT NULL PRIMARY KEY,
    OrderedUtc datetime2(7) NOT NULL,
    Status varchar(12) NOT NULL,
    Amount decimal(12,2) NOT NULL
);
CREATE INDEX IX_ReviewOrders_OrderedUtc ON #ReviewOrders (OrderedUtc);
INSERT #ReviewOrders VALUES
(1, '2026-01-02T09:00:00', 'Pending', 25.00),
(2, '2026-01-03T00:00:00', 'Pending', 40.00),
(3, '2026-01-02T15:00:00', 'Paid', 15.00);
SELECT c.name AS ColumnName, t.name AS TypeName,
    c.max_length, c.precision, c.scale, c.is_nullable
FROM tempdb.sys.columns AS c
JOIN tempdb.sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID(N'tempdb..#ReviewOrders')
ORDER BY c.column_id;

Read Write Predicates Before Anything Else

A missing WHERE on UPDATE or DELETE is an immediate review failure for a targeted change. A WHERE that exists but matches the wrong population is equally dangerous. Preview the exact predicate with a SELECT, including the key and the current value.

For joins, inspect how many matches each target row receives. Multiple source matches can make an UPDATE ambiguous. A preview should use the same joins and filters as the proposed write. Do not preview a carefully filtered query, then execute a broader statement copied from another answer.

Which identifiers should change under this rule? Here, the request is to close pending orders from one UTC day. The preview exposes those identifiers and calculates the candidate count. That count is evidence to inspect, not permission to assume every candidate is correct.

DECLARE @DayStart datetime2(7) = '2026-01-02T00:00:00';
DECLARE @DayEnd datetime2(7) = DATEADD(day, 1, @DayStart);
SELECT OrderID, OrderedUtc, Status, Amount
FROM #ReviewOrders
WHERE Status = 'Pending'
  AND OrderedUtc >= @DayStart AND OrderedUtc < @DayEnd
ORDER BY OrderID;
SELECT COUNT_BIG(*) AS CandidateRows
FROM #ReviewOrders
WHERE Status = 'Pending'
  AND OrderedUtc >= @DayStart AND OrderedUtc < @DayEnd;

Inspect Functions and Date Boundaries

A generated filter such as CONVERT(date, OrderedUtc) equals a requested date deserves a plan review. Functions on indexed columns can complicate efficient access. Some transformations have special optimizer handling, so inspect the plan rather than declaring every such predicate a scan.

An explicit start-inclusive, end-exclusive range states the boundary clearly and avoids wrapping the indexed column. It also includes fractional seconds correctly. Do not use a guessed final time such as 23:59:59 when the column holds more precise values.

Confirm what the timestamp means. A local calendar day and a UTC day have different boundaries. If the business asks for local dates, calculate the appropriate UTC interval first, accounting for the approved time-zone rule. Correct index access cannot rescue the wrong calendar interval.

Reject Isolation Hints Added Without a Reason

NOLOCK is not a generic performance fix. It permits dirty reads and can produce inconsistent results, including missing or duplicated observations. It also does not remove every kind of blocking. A count used to authorize a write needs reliable semantics.

Ask why any hint appears in generated SQL. If the answer is simply that it makes the query faster, remove the assumption and investigate the actual blocking or access path. Choose isolation based on the operation's correctness requirements and the database's supported concurrency design.

I review hints before adopting any suggested index. A hint can change the meaning of the result while leaving its columns looking familiar. A report that reads quickly but invents an intermediate business state has not solved the original problem.

Read the Estimated Plan Without Executing the Write

SSMS can display an estimated plan without running the statement. The following equivalent T-SQL technique uses SHOWPLAN_XML in separate batches. Create the temporary table first. Run the complete block and ensure the final OFF batch executes before continuing with other commands.

Inspect access predicates, joins, estimated rows, and conversions. An estimate is not an observed result. Check whether the plan follows the intended filtering rule and whether parameter and column types match. A small temporary example also does not predict the production plan for a large table.

SET SHOWPLAN_XML ON;
GO
UPDATE #ReviewOrders
SET Status = 'Closed'
WHERE Status = 'Pending'
  AND OrderedUtc >= CONVERT(datetime2(7), '2026-01-02T00:00:00', 126)
  AND OrderedUtc < CONVERT(datetime2(7), '2026-01-03T00:00:00', 126);
GO
SET SHOWPLAN_XML OFF;
GO

Test a Controlled Write With Rollback

Use a disposable test database or representative test copy before touching real data. A transaction that rolls back is useful, but it still executes the write. It can take locks, fire triggers, consume resources, and affect nontransactional or external side effects. Identity allocation also does not rewind like ordinary row changes.

The next example writes only the local teaching table. Enable the actual plan in SSMS if you want to inspect execution details. It previews the changed rows while the transaction is active, then explicitly rolls back. The handler also rolls back after an error and preserves the failure with THROW.

This template owns its transaction and refuses an ambient one. An application procedure participating in a caller's transaction needs a different ownership contract. Never use a diagnostic rollback to cancel unrelated work that the session already had open.

IF @@TRANCOUNT <> 0
    THROW 50000, 'Use a clean session for this rollback test.', 1;
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE #ReviewOrders
    SET Status = 'Closed'
    WHERE Status = 'Pending'
      AND OrderedUtc >= CONVERT(datetime2(7), '2026-01-02T00:00:00', 126)
      AND OrderedUtc < CONVERT(datetime2(7), '2026-01-03T00:00:00', 126);
    DECLARE @AffectedRows int = @@ROWCOUNT;
    SELECT @AffectedRows AS AffectedRows;
    SELECT OrderID, Status FROM #ReviewOrders ORDER BY OrderID;
    ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;
SELECT OrderID, Status FROM #ReviewOrders ORDER BY OrderID;

Verify System Columns and Edge Cases From the AI Assistant

Generated DMV queries deserve the same review as application SQL. Verify each column against the exact view's documented schema. A plausible column name from a related DMV is still wrong. The following metadata check shows the columns SQL Server exposes for sys.dm_exec_requests.

Test nulls, empty inputs, duplicate joins, boundary timestamps, and unexpected statuses. Check both data results and transaction cleanup after failure. Then review the AI assistant's revised query again, since a correction can introduce a different mistake elsewhere.

SELECT name AS ColumnName, column_id
FROM sys.all_columns
WHERE object_id = OBJECT_ID(N'sys.dm_exec_requests')
ORDER BY column_id;

The AI assistant accelerates drafting, but your schema and business tests establish correctness. Keep the accepted query and its review evidence together. Run it on real data only through the normal authorized process, with a clear expected result and an appropriate recovery plan.

Related reading on this blog: AI Hallucinated a Table That Was Never There and Validating AI-Generated Index Recommendations.

Generated SQL is not an approved change, it is a draft that still needs a database review.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.