/tsql-review

name: tsql-review
description: Analyze raw T-SQL source code for anti-patterns, security risks, and static performance smells. Applies 85 checks (T1–T85) across structural, correctness, security, deprecated syntax, performance, and SQL 2017–2022 modern syntax categories. Use this skill whenever a user pastes a stored procedure, function, view, trigger, or ad-hoc SQL and asks for a review; asks if code is safe, correct, or optimized; mentions implicit conversions, missing indexes, SET options, or cursor usage; or wants a code review before deploying to production. No execution plan required — trigger for any T-SQL review request.
triggers:
  - /tsql-review
  - /sql-review

T-SQL Static Review Skill

Purpose

Analyze T-SQL source code — stored procedures, ad-hoc queries, scripts, migration files — for anti-patterns that are detectable without running the query or capturing an execution plan. Covers 85 checks (T1–T85) across six categories: structural anti-patterns, correctness and logic, security and dynamic SQL, deprecated and non-idiomatic syntax, performance smells, and SQL Server 2017–2022 modern syntax checks.

This is the "shift-left" complement to `sqlplan-review`. Run it during code review to catch problems before they reach production. Run `sqlplan-review` on the resulting execution plan to catch what only surfaces at runtime.

Input

Accept any of:

• Raw T-SQL source code (paste inline or provide a file path)
• A `.sql` file path
• A description of the query structure ("a stored proc with a cursor that builds a dynamic WHERE clause")

If the user provides a file path, read the file and analyze its content. If the input is inline SQL, analyze it directly. If the input is a description, apply the checks based on what is described and note which checks could not be verified from the description alone.

How to Run

Walk T1–T85 in category order. Report every triggered finding — do not stop at the first match. For checks where the SQL construct is absent, note them as passing in the Passed Checks section. For checks where schema or parameter type information is unknown, state your assumption explicitly rather than skipping the check.

---

Thresholds Reference

| Metric | Value | |--------|-------| | CTE chain depth warning | > 4 levels deep | | Large IN list | > 20 discrete values in an IN() clause | | Nested subquery depth | ≥ 3 levels of nested scalar subqueries | | Excessive parameters | > 50 named parameters in a stored procedure | | Wide index suggestion | > 4 key columns OR > 5 INCLUDE columns | | NOLOCK overuse threshold | ≥ 3 tables WITH (NOLOCK) in the same query | | Small variable-length type | ≤ 2 characters (VARCHAR(1), VARCHAR(2), NVARCHAR(1), NVARCHAR(2)) |

---

Structural Anti-Patterns (T1–T15, T51–T55)

Run these checks for patterns that prevent index usage, expand data volumes unnecessarily, or indicate set-based logic replaced by row-by-row processing.

T1 — SELECT * (No Explicit Column List)

• **Trigger:** `SELECT *` in any SELECT statement (including SELECT INTO, subqueries, CTEs, or views)
• **Severity:** Warning
• **Fix:** Replace `*` with an explicit column list. Eliminates surprise column additions when the schema changes, prevents over-fetching wide rows, and allows the optimizer to consider covering indexes.

T2 — Missing WHERE on UPDATE or DELETE

• **Trigger:** An `UPDATE` or `DELETE` statement with no `WHERE` clause (including `TRUNCATE`-equivalent patterns using DELETE)
• **Severity:** Critical
• **Fix:** Add a `WHERE` clause or, if a full-table wipe is intended, use `TRUNCATE TABLE` (which is faster and fully logged). If the omission is intentional, add a comment explaining the intent.

T3 — Missing WHERE on SELECT (Full-Table Read)

• **Trigger:** A `SELECT` or `SELECT INTO` with no `WHERE` clause on a named user table (not a system view or TVF with no filter parameter)
• **Severity:** Info
• **Fix:** Confirm the full-table read is intentional. Add `WHERE 1=1 -- intentional full scan` as documentation if it is. Otherwise add a predicate.

T4 — Non-Sargable Predicate — Function Wrapping or Arithmetic on Indexed Column

• **Trigger:** A function call or arithmetic expression in a `WHERE`, `HAVING`, or `JOIN ON` clause that wraps or involves a column reference: `YEAR(col)`, `MONTH(col)`, `DAY(col)`, `CAST(col AS ...)`, `CONVERT(type, col)`, `UPPER(col)`, `LOWER(col)`, `LEFT(col, n)`, `SUBSTRING(col, 1, n)`, `ISNULL(col, default)`, `COALESCE(col, ...)`, or arithmetic on the column side: `col + n`, `col - n`, `col * n`, `col / n`. For DATEDIFF specifically see T60; for LEN/DATALENGTH see T74.
• **Severity:** Warning
• **Fix:** Rewrite the predicate so the column is bare and the transformation is applied to the literal or parameter. Example: `WHERE YEAR(OrderDate) = 2024` → `WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'`. This allows an index seek instead of a full scan.

T5 — Non-Sargable Predicate — Implicit Type Coercion

• **Trigger:** A comparison where the column type and the literal or parameter type differ and SQL Server would insert an implicit `CONVERT` on the column side (e.g., `INT` column compared to an `NVARCHAR` parameter, `VARCHAR` column compared to `NVARCHAR` literal `N'value'`, `DATE` column compared to a `DATETIME` parameter)
• **Severity:** Warning
• **Fix:** Align the parameter or literal type with the column type. Declare parameters with matching types. Use explicit `CAST` on the literal rather than relying on SQL Server to cast the column. Confirm in the execution plan using the `sqlplan-review` skill (check S12/N12 implicit conversion warnings).

T6 — Leading Wildcard LIKE

• **Trigger:** A `LIKE` predicate whose pattern starts with `%`: `LIKE '%value'` or `LIKE '%value%'`
• **Severity:** Warning
• **Fix:** Leading wildcards prevent index seeks. If full-text search is needed, use SQL Server Full-Text Search (`CONTAINS`, `FREETEXT`) or consider a computed persisted column with a su