The daily query rarely needs an exotic feature. A T-SQL cheat sheet is useful when it puts familiar statements in one place with the mistake each one avoids.
Read Only What You Need
Start with SELECT and name the columns a consumer needs. SELECT * can pull large values, change result shape after schema edits, and make a report depend on fields nobody reviewed. Add a WHERE clause that reflects the business question.
I put a bounded date range in diagnostic queries instead of opening all history. A half open interval handles datetime values cleanly. Include ORDER BY when presentation order matters, and use a unique tie breaker for paging.
The compact pattern is simple: columns, source, filter, order. The query should still say what one result row represents. A T-SQL cheat sheet is a reminder, not permission to skip grain.
SELECT OrderId, CustomerId, OrderDate
FROM dbo.Orders
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2025-02-01'
ORDER BY OrderDate, OrderId;T-SQL Cheat Sheet Joins: Match on Keys, Then Check Grain
JOIN connects related sets through keys. An INNER JOIN keeps matches. A LEFT JOIN keeps left-side rows even when no right-side row exists. Put the correct key in ON and decide whether a right-side filter belongs in ON or WHERE, because WHERE can remove unmatched rows.
I count rows before and after a new join during development. A supposed lookup table with duplicate keys can multiply facts. GROUP BY later can hide the problem inside an inflated total.
Use table aliases that are short and clear. A query with five one-letter aliases can become a puzzle. The key relationship should remain visible when someone reviews the code months later.
SELECT o.OrderId, c.CustomerName
FROM dbo.Orders AS o
JOIN dbo.Customer AS c ON c.CustomerId = o.CustomerId;Group Rows and Filter Groups
GROUP BY gives one result row per group key. WHERE filters input rows first. HAVING filters groups after aggregates are computed. Use COUNT_BIG for counts that can exceed int range and SUM for additive measures at the correct grain.
I write the output grain beside any aggregate with several joins. One row per customer per month needs both keys. A line-item join can multiply an order-level amount if the grain is ignored.
NULL values form one group. COUNT(*) includes all rows, while COUNT(ColumnName) excludes NULL in that column. These details explain totals that look close but do not reconcile.
SELECT CustomerId, COUNT_BIG(*) AS OrdersPlaced
FROM dbo.Orders
WHERE OrderStatus = 'Paid'
GROUP BY CustomerId
HAVING COUNT_BIG(*) > 1;
Rank Within a Group
ROW_NUMBER gives each row a position within a partition. It is useful for latest record per customer or top items per region. ORDER BY inside OVER defines the rank, and a unique tie breaker makes the result repeatable.
I use a CTE to name the ranked set, then filter rn in the outer query. A window function result cannot be used in the same SELECT’s WHERE directly. The extra step makes the logical order clear.
If ties should all be returned, ROW_NUMBER is not the right choice by itself. RANK or a join to the maximum value can express that requirement. Decide before copying the pattern.
WITH ranked AS
(
SELECT CustomerId, OrderId, OrderDate,
ROW_NUMBER() OVER
(PARTITION BY CustomerId ORDER BY OrderDate DESC, OrderId DESC) AS rn
FROM dbo.Orders
)
SELECT CustomerId, OrderId, OrderDate
FROM ranked
WHERE rn = 1;Convert Untrusted Text Safely
TRY_CONVERT returns NULL when text cannot be converted to a requested type. It helps identify rejected rows without aborting an entire read. Keep the original text so a source owner can see the bad value.
I use an explicit date style when parsing a known source format. A string like 03/04/2025 is ambiguous without a contract. A successful conversion under one session setting is not proof the source meant that date.
Separate source NULL from an invalid nonblank value. They can both produce NULL after conversion but need different correction paths. The reject query should show source row ID and raw text.
SELECT SourceRowId, RawDate
FROM dbo.StageOrder
WHERE NULLIF(TRIM(RawDate), N'') IS NOT NULL
AND TRY_CONVERT(date, RawDate, 23) IS NULL;T-SQL Cheat Sheet Writes: Change Data Inside a Transaction
A transaction groups related writes into one commit or rollback boundary. Keep it as short as practical. Check the target predicate before UPDATE or DELETE, and use a tested rollback path for high-impact changes.
I capture @@ROWCOUNT immediately after a data operation. Another statement changes it. A row count is useful evidence but not enough to prove values are right. Inspect keys or compare output under the business rule.
Use TRY…CATCH and XACT_STATE() when error handling must clean up a transaction. Rethrow unexpected errors so a job does not report success after rollback. A transaction is a safety boundary, not a substitute for correct WHERE.
BEGIN TRANSACTION;
UPDATE dbo.Customer
SET IsActive = 0
WHERE CustomerId = 42;
SELECT @@ROWCOUNT AS RowsChanged;
ROLLBACK TRANSACTION;Keep Your T-SQL Cheat Sheet and Daily Scripts Reviewable
Use clear names, predictable indentation, and comments that explain a decision. Do not comment every keyword. Keep a script’s assumptions near its top: target database, expected grain, parameters, and whether it writes data.
I ask what could go wrong if the script runs twice. A read query is straightforward. A data correction should be idempotent or explicitly guarded. Save the before state and write a verification query for any permanent change.
A T-SQL cheat sheet helps when it reminds you of reliable patterns and their limits. Copy a pattern, adapt it to the table’s keys and types, then validate the result on your own server. The shortest example is only the beginning of the work.
A cheat sheet should remind you of safe habits, not only syntax. Before UPDATE or DELETE, run the predicate as SELECT and count the intended rows. In a transaction, check @@ROWCOUNT and inspect a sample before committing. Which database is the query window connected to? Put that check at the top of any script copied between environments.
Keep examples small enough to paste, but include the uncomfortable cases. A JOIN example should show duplicate keys. A date filter should use a half-open interval. A NULL example should use IS NULL rather than equality. I revise my own quick notes when a production edge case proves that a neat one-liner hides a rule. The best cheat sheet helps a reader avoid the second mistake, not just type the first query faster.
Keep a transaction example with both commit and rollback paths in the team’s notes. A copied rollback-only example protects a test, but a production repair needs an explicit approval point and a post-change verification query.
Related reading on this blog: UPDATE Without WHERE Clause: The Day Everyone Got a Raise and Optimize DATE in WHERE Clause: SQL in Sixty Seconds #189.
A cheat sheet is not a substitute for thinking, it is a reminder of sound query habits.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
