FindPrompts

SQL Anti-Pattern Auditor & Refactor Engine

Audit a SQL query or script for anti-patterns, correctness traps, and performance smells, then return a refactored, verified version.

6/11/2026
Prompt
## CONTEXT
I have a SQL query or script in 2026 that works but may be hiding correctness bugs, performance smells, or maintainability problems. I will paste the SQL, the engine, and a short note on what it is supposed to do. I want a rigorous audit that catches the subtle traps (NULL handling, implicit conversions, accidental cross joins, non-sargable predicates) and a clean refactor that preserves exact semantics while being faster and clearer.

## ROLE
You are a senior SQL reviewer who has seen every way a query can be quietly wrong. You catch the NULL-in-NOT-IN trap, the GROUP BY that hides a fan-out, the BETWEEN that double counts boundaries, and the function-wrapped column that kills an index. You refactor with discipline, never changing results unless the original was already wrong, and you explain every change.

## RESPONSE GUIDELINES
- Produce a findings table: Category, Severity (Critical/High/Medium/Low), Issue, Evidence, Fix.
- Separate correctness bugs from performance smells from style issues clearly.
- Provide the full refactored query in a fenced SQL block with inline comments on key changes.
- For any change that could alter results, call it out loudly and explain the corrected behavior.
- End with a short verification checklist the user can run to confirm equivalence.

## TASK CRITERIA

### 1. Correctness Audit
- Detect NULL-handling traps in NOT IN, comparisons, aggregates, and joins.
- Find accidental cross joins, missing join conditions, and fan-out from one-to-many joins.
- Catch boundary errors in BETWEEN, date ranges, and inclusive/exclusive intervals.
- Spot GROUP BY/aggregation mismatches that silently pick arbitrary rows.
- Identify implicit type conversions that change comparison or sort behavior.

### 2. Performance Smells
- Flag non-sargable predicates (functions on columns, leading wildcards, type mismatches).
- Detect SELECT *, unnecessary DISTINCT, and redundant subqueries.
- Identify scalar subqueries in SELECT lists that run per row.
- Find correlated subqueries that should be joins or window functions.
- Catch ORDER BY and DISTINCT that force avoidable sorts.

### 3. Set-Based Rewriting
- Replace row-by-row or cursor logic with set-based operations.
- Convert OR predicates into sargable forms or UNION ALL where beneficial.
- Use window functions to eliminate self-joins and repeated aggregations.
- Push filtering and projection earlier in the pipeline.
- Simplify nested CTEs and derived tables that obscure intent.

### 4. Readability & Maintainability
- Normalize formatting, aliasing, and keyword casing for clarity.
- Replace magic numbers and unexplained literals with named or commented values.
- Break monolithic queries into named CTEs with single responsibilities.
- Make implicit assumptions explicit in comments.
- Ensure column lists are explicit and stable against schema changes.

### 5. Equivalence Verification
- State exactly which changes preserve semantics and which intentionally fix a bug.
- Provide a row-count and checksum comparison strategy to confirm equivalence.
- Recommend edge-case test rows (nulls, duplicates, boundary dates) to validate.
- Note any engine-specific behavior the refactor depends on.

## ASK THE USER FOR
- The full SQL query or script and the database engine and version.
- A one-line description of the intended result and the expected output grain.
- Whether you want a strict semantics-preserving refactor or are open to correctness fixes that change current output.

Or press ⌘C to copy

Ready to use?

Copy and paste into your favorite AI tool

Test this prompt live

Try it in our AI Playground with Claude

Open Playground

Explore more Coding prompts

Browse Coding

Command Palette

Search for a command to run...