Guides

Workplace SQL Server pages. Search a function, or pick a topic.

73 guides

At work Why SELECT * fails Extra columns break the paste. Name what they asked for. At work Find duplicate customers Same email twice. GROUP BY and HAVING. At work CTEs for reports WITH name AS (…): write the query in the order you think. At work Run a query in SSMS Highlight the batch. Check the database dropdown. Then F5. At work Email the numbers Range in the subject. Columns they asked for. One total line. At work Second highest The interview one. OFFSET, ROW_NUMBER, or a subquery. Joins LEFT JOIN … IS NULL Customers who never ordered. Products that never sold. Joins JOIN doubled my totals One order, many lines. SUM after the join is wrong. Joins INNER JOIN Keep rows that match on both sides. The everyday join. Joins LEFT JOIN All customers, and their orders if they have any. Joins RIGHT JOIN Same as LEFT JOIN from the other side. Teams write LEFT. Joins FULL OUTER JOIN Rows from either side that do not match, plus the matches. Joins CROSS JOIN Every staff × every day. Easy to explode. Joins Self-join Join a table to itself. Staff.manager_id → Staff. Joins UPDATE with JOIN Set a column from another table. Preview with SELECT first. Dates GETDATE() Today, yesterday, last week. And why “last 7 days” keeps including today. Dates DATEADD Move a date backwards or forwards by any unit. Dates DATEDIFF Days, months or years between two dates, and the boundary rule. Dates Last week’s orders Monday–Sunday last week, not a rolling 7 days ending now. Dates Yesterday’s sales Midnight to midnight. Today’s orders stay out. Dates Datetime to date CAST to date for the value. Do not wrap the column in the WHERE. Dates BETWEEN dates BETWEEN includes both ends. A datetime end includes 00:00 that day only. Dates date vs datetime date is a day. datetime has a time. datetime2 is the modern one. Dates Age from a date Birthday to today. Do not use years crossed. Check the birthday has happened. Dates EOMONTH Month-end close. First day is EOMONTH plus one, last month. Dates DATEPART Weekday, month number, week number. DATEFIRST changes weekday. Dates FORMAT dates Pretty for a slide. Slow for a GROUP BY on a big table. Window ROW_NUMBER Latest order per customer, the whole row. Window RANK Ties. RANK skips. DENSE_RANK does not. ROW_NUMBER never ties. Window LAG and LEAD This month versus last month, without a self-join. Window Running total Daily revenue and the year-to-date next to it. Window OFFSET FETCH Page 2 of a grid. Always with ORDER BY. TOP is simpler for “first 5”. Aggregates Group by month Monthly totals from daily rows, three ways. Aggregates GROUP BY error 8120 Msg 8120, explained and fixed. Aggregates WHERE vs HAVING WHERE is before the group. HAVING is after the total. Aggregates COUNT vs COUNT DISTINCT Orders vs customers. Easy to mix up in a stand-up number. Aggregates GROUP BY Orders by status. Revenue by customer. Every non-total column goes in the GROUP BY. Aggregates DISTINCT Unique values. Use it to peek. Prefer GROUP BY when you also want a count. Aggregates HAVING Customers over £1,000. WHERE cannot see a SUM. Aggregates COUNT COUNT(*) is rows. COUNT(column) skips NULL. COUNT(DISTINCT) is people. Aggregates SUM and AVG Totals and averages. AVG of an int is an int unless you cast. Types varchar vs nvarchar Plain text vs Unicode. Names and email are usually nvarchar. Types int, decimal, money Counts are int. Money is decimal. Do not divide two ints. Types bit A yes/no flag. Not quite a boolean. NULL is the third state. Types uniqueidentifier A wide random id. Fine as a key, clumsy as a clustered index. Types CAST and CONVERT Change a type. CAST for readability. CONVERT when you need a style. Types TRY_CAST Bad text becomes NULL instead of an error that stops the report. Types NULL vs blank NULL means unknown. A blank means someone typed nothing. They are not equal. Types Collation CI = ignore case. CS = case sensitive. Accents too. Functions ISNULL Swap NULL for a word or a zero. ISNULL or COALESCE? Functions TOP The first N rows. Meaningless without ORDER BY. Functions CASE WHEN Small, Medium, Large. If-then-else inside a query. Functions LIKE % for any length. _ for one character. Watch the leading %. Functions SUBSTRING, LEFT, RIGHT Pull a piece of a string. First 4 of a postcode, last 4 of a card we should not store. Functions TRIM Knock spaces off a name before you compare or de-duplicate. Functions CONCAT Stick first and last name together. + dies on NULL. CONCAT does not. Functions REPLACE Swap text. Hyphens out of a phone number. Not a regex. Functions CHARINDEX Position of @ in an email. Zero means not found. Functions IN Status in a short list. NOT IN dies if the list contains NULL. Functions EXISTS Customers with a cancelled order. Stops at the first match. Functions AND and OR OR without brackets is how a filter quietly goes wide. Functions UNION Stack two result sets. ALL keeps duplicates and is faster. Functions Subquery Orders above average. Or a scalar in the SELECT. Easy to hide a slow extra hit. Functions COALESCE First non-NULL. Several fallbacks. Prefer this over nested ISNULL if you know other databases. Functions Temp tables #scratch for a step you need twice. CTE if it is only this statement. Functions Variables Put today’s midnight in a name so the WHERE is readable. Functions INSERT SELECT Copy rows into a table. Name the columns. Do not SELECT *. Functions Foreign keys Orders.customer_id must exist in Customers. That is the join you already write. Functions NOLOCK Dirty reads. You can see a row that gets rolled back. Functions IIF Two-way only. CASE when there are three bands. Functions ORDER BY The only way a result has an order. TOP without it is random. Functions WHERE Filter before you total. Quotes around text. No quotes around numbers. Functions Aliases AS name. o.order_id. Give every function a header.