T-SQL Quick Reference

Analogy categoryAnalogies

Core T-SQL Syntax at a Glance

This quick reference collects the T-SQL syntax developers reach for constantly -- data type choices, join forms, common string and date functions, and the handful of statements that appear in nearly every stored procedure -- so it can be scanned instead of searched for each time. It intentionally favors breadth over depth: each item here links conceptually to a fuller explanation elsewhere (indexing, isolation levels, window functions), but the goal of this page is to be the page you keep open in a second tab while writing a query.

🏏

Cricket analogy: This quick reference is like a fielding-positions cheat sheet a captain like Rohit Sharma keeps handy -- not the full coaching manual, just the fast lookup needed mid-match.

Data Types and Common Statements

For numeric identity-style keys use INT or BIGINT; for money use DECIMAL(19,4) rather than the MONEY type, which has known rounding quirks in intermediate calculations; for variable text use NVARCHAR(n) to support Unicode rather than VARCHAR unless you are certain the column will only ever hold ASCII data; for dates use DATE, DATETIME2(precision) rather than the legacy DATETIME (which has lower precision and a smaller valid range), and use DATETIMEOFFSET when time zone awareness matters. The core DML statements -- SELECT, INSERT, UPDATE, DELETE, MERGE -- combine with clauses like WHERE, GROUP BY, HAVING, and ORDER BY in a fixed logical processing order that differs from the order you type them: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

🏏

Cricket analogy: Choosing DECIMAL(19,4) over MONEY for currency is like choosing a precise laser-measured boundary rope over an eyeballed one -- both roughly mark the same line, but only one avoids disputed edge cases.

sql

-- Data type choices
CREATE TABLE dbo.Payments (
    PaymentId       BIGINT IDENTITY(1,1) PRIMARY KEY,
    CustomerName    NVARCHAR(200) NOT NULL,
    Amount          DECIMAL(19,4) NOT NULL,
    PaymentDate     DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    ProcessedAtUtc  DATETIMEOFFSET NULL
);

-- MERGE upsert pattern
MERGE dbo.Customers AS target
USING (SELECT @CustomerId AS CustomerId, @Name AS Name) AS src
ON target.CustomerId = src.CustomerId
WHEN MATCHED THEN
    UPDATE SET target.Name = src.Name
WHEN NOT MATCHED THEN
    INSERT (CustomerId, Name) VALUES (src.CustomerId, src.Name);

Joins, Window Functions, and String/Date Functions

The join forms in order of common use are INNER JOIN (only matching rows), LEFT JOIN (all left rows, matched or not), RIGHT JOIN (rare in practice -- usually rewritten as a LEFT JOIN with tables swapped for readability), FULL OUTER JOIN (all rows from both sides), and CROSS JOIN (Cartesian product, used deliberately for generating combinations). The window function family -- ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and aggregate functions used with OVER() -- all require an OVER clause and most benefit from PARTITION BY to scope the calculation per group. Common string functions are LEFT(), RIGHT(), SUBSTRING(), CHARINDEX(), TRIM(), and STRING_AGG() for concatenating grouped values into one delimited string; common date functions are DATEADD(), DATEDIFF(), DATEPART(), and EOMONTH() for finding a month's last day.

🏏

Cricket analogy: RIGHT JOIN being rare and usually rewritten as a LEFT JOIN is like a captain rarely batting the strongest player at number 11 -- technically legal, but almost always restructured for a more readable, conventional lineup.

DENSE_RANK() and RANK() both handle ties by giving equal rows the same rank, but RANK() leaves a gap afterward (1, 1, 3) while DENSE_RANK() does not (1, 1, 2). ROW_NUMBER() never ties -- every row gets a unique sequential number even when values are identical, with the tie broken arbitrarily by physical/plan order unless ORDER BY fully disambiguates it.

sql

-- Common string/date functions in one query
SELECT
    TRIM(c.CustomerName)                                   AS CleanName,
    STRING_AGG(p.ProductName, ', ')                        AS ProductsOrdered,
    DATEDIFF(DAY, o.OrderDate, GETUTCDATE())               AS DaysSinceOrder,
    EOMONTH(o.OrderDate)                                    AS OrderMonthEnd,
    LAG(o.TotalAmount) OVER (
        PARTITION BY o.CustomerId ORDER BY o.OrderDate
    )                                                        AS PreviousOrderAmount
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c ON c.CustomerId = o.CustomerId
INNER JOIN Sales.OrderItems AS oi ON oi.OrderId = o.OrderId
INNER JOIN Sales.Products AS p ON p.ProductId = oi.ProductId
GROUP BY c.CustomerName, o.OrderId, o.CustomerId, o.OrderDate, o.TotalAmount;

Prefer DECIMAL(19,4) over MONEY, and DATETIME2 over legacy DATETIME, for precision and range.
Use NVARCHAR for text that may contain non-ASCII characters.
SQL's logical processing order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, regardless of typed order.
INNER JOIN and LEFT JOIN cover the vast majority of real queries; RIGHT JOIN is rarely used in practice.
Window functions require OVER() and typically use PARTITION BY to scope calculations per group.
RANK() leaves gaps after ties, DENSE_RANK() does not, and ROW_NUMBER() never ties.
STRING_AGG(), DATEDIFF(), and EOMONTH() are frequently reached-for string and date helpers.