SQL formatting, optimization, and review patterns for Oracle, SQL Server, Cosmos SQL, and migration work.
SQL optimization, formatting standards, and query review patterns.
-- Keywords: UPPERCASE
-- Identifiers: lowercase_snake_case
-- Indentation: 2 spaces
-- Trailing comma on columns
-- Always alias tables in JOINs
-- CTEs over subqueries
SELECT
o.order_id,
o.order_date,
c.customer_name,
ol.quantity,
ol.unit_price,
ol.quantity * ol.unit_price AS line_total,
FROM
orders o
INNER JOIN customers c ON c.customer_id = o.customer_id
INNER JOIN order_lines ol ON ol.order_id = o.order_id
WHERE
o.order_date >= '2025-01-01'
AND o.status = 'ACTIVE'
ORDER BY
o.order_date DESC
LIMIT 1000;
| Anti-Pattern | Fix |
|---|---|
SELECT * | List explicit columns |
| Nested subqueries | Use CTEs |
WHERE YEAR(date_col) = 2025 | WHERE date_col >= '2025-01-01' AND date_col < '2026-01-01' (sargable) |
WHERE col LIKE '%value%' | Leading wildcard kills index. Use full-text search or rethink. |
| Implicit type conversion | Cast explicitly: WHERE col = CAST('123' AS INT) |
COUNT(*) when you mean COUNT(col) | COUNT(col) excludes NULLs |
Missing COALESCE for nullable columns | COALESCE(col, default_val) |
| Functions on indexed columns in WHERE | Sargable predicates only |
EXPLAIN / execution plan-- Use analytic functions instead of self-joins
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;
-- Pivot rows to columns
SELECT *
FROM (
SELECT department, metric_name, metric_value
FROM department_metrics
)
PIVOT (MAX(metric_value) FOR metric_name IN (
'revenue' AS revenue,
'headcount' AS headcount,
'satisfaction' AS satisfaction
));
-- Merge (upsert) pattern
MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.col = s.col
WHEN NOT MATCHED THEN INSERT (id, col) VALUES (s.id, s.col);
-- Cosmos DB SQL API: use system functions
SELECT
c.id,
c.name,
c.orderDate,
ARRAY_LENGTH(c.items) AS item_count
FROM
customers c
WHERE
c.orderDate >= "2025-01-01"
ORDER BY
c.orderDate DESC
OFFSET 0 LIMIT 50;
Ask your AI assistant: