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.
-- 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.
-- 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.
Practice what you learned
1. Why is DECIMAL(19,4) generally preferred over the MONEY data type for currency?
2. What is T-SQL's logical processing order for a SELECT statement?
3. Which window function never produces a tie, always assigning a unique sequential number?
4. What does STRING_AGG() do?
5. Which join type is rarely used in practice and usually rewritten as a LEFT JOIN with tables swapped?
Was this page helpful?
You May Also Like
T-SQL Best Practices
Practical, production-tested guidelines for writing T-SQL that is correct, performant, and maintainable in SQL Server.
Building a Reporting Query
A step-by-step walkthrough of designing a real-world T-SQL reporting query using joins, aggregation, window functions, and pivoting.
SQL Server Interview Questions
Commonly asked SQL Server and T-SQL interview questions with the reasoning behind strong answers, from indexing to isolation levels.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics