sql

SQL Expert Skill

Write high-quality, optimized SQL for enterprise environments. Covers performance, edge cases, and real-world gotchas.

When to Use

• Writing new SQL queries
• Optimizing existing queries
• Handling large datasets (100K+ rows)
• Creating stored procedures or views
• Debugging slow queries

---

SQL Quality Standards

1. Always Include Comments

-- Get open orders with SLA breach risk
-- Filters: Last 7 days, excludes test data
-- Expected rows: ~500-2000
SELECT
    OrderID,
    CustomerName,
    DATEDIFF(HOUR, CreatedAt, GETDATE()) AS hours_open
FROM Orders
WHERE CreatedAt >= DATEADD(DAY, -7, GETDATE())
    AND Status = 'Open'
ORDER BY hours_open DESC;

2. Readable Formatting

SELECT
    o.OrderID,
    o.CustomerName,
    o.Status,
    COUNT(i.ItemID) AS item_count
FROM Orders o
LEFT JOIN OrderItems i ON o.OrderID = i.OrderID
WHERE o.Status = 'Open'
GROUP BY o.OrderID, o.CustomerName, o.Status
HAVING COUNT(i.ItemID) > 0
ORDER BY item_count DESC;

---

Large Dataset Patterns

Pagination

-- OFFSET-FETCH (SQL Server 2012+ / PostgreSQL)
SELECT OrderID, CustomerName, Status
FROM Orders
WHERE Status = 'Open'
ORDER BY CreatedAt DESC
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

-- Keyset pagination (faster for deep pages)
SELECT TOP 25 OrderID, CustomerName, CreatedAt
FROM Orders
WHERE CreatedAt < @lastSeenDate AND Status = 'Open'
ORDER BY CreatedAt DESC;

Batching Large Operations

DECLARE @BatchSize INT = 5000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    UPDATE TOP (@BatchSize) Orders
    SET ProcessedFlag = 1
    WHERE Status = 'Closed' AND ProcessedFlag = 0;
    SET @RowsAffected = @@ROWCOUNT;
    WAITFOR DELAY '00:00:00.100';
END

---

NULL Handling

-- Use IS NULL (not = NULL)
SELECT * FROM Orders WHERE Category IS NULL;

-- ISNULL (SQL Server) vs COALESCE (ANSI standard)
SELECT ISNULL(Category, 'Unknown') FROM Orders;
SELECT COALESCE(Category, SubCategory, 'Unknown') FROM Orders;

-- NULL in aggregations: COUNT(*) counts all, COUNT(col) excludes NULLs
SELECT COUNT(*) AS total, COUNT(Category) AS non_null FROM Orders;
SELECT ISNULL(SUM(Amount), 0) AS total FROM Orders;