The effect of collation on result sets is easy to see once you run the same query against two collations. This post builds a small demo and shows how sorting, comparing and grouping change. It also explains collation conflicts, including the one temp tables cause.
CTE Referenced Twice: Why the Query Runs It Twice
Writing a CTE name two times can mean doing the work two times. Two small demos show when a temp table helps and when it only adds cost.
Triangular Joins: The Hidden Row Explosion in Self-Joins
A small grouped result can hide a huge pile of row pairs. Here is how to count them and what to do instead.
Partitioned Aggregates: Compare Each Row to Its Own Group
Partitioned aggregates let every row see a number for its own group, such as how many plants share its garden bed. This post uses thirteen seedlings in four beds to show PARTITION BY with COUNT and AVG. It also shows the integer division trap that turns every share into 0, and how to get one row per group.
Aggregates With the OVER Clause: A Total Beside Every Row
Aggregates with the OVER clause put a total, an average or a minimum next to every row, so the rows do not collapse into one. This post uses eight trip costs to show the error a plain SUM gives, the fix, and the percent of the total for each row. It also shows how WHERE changes the total and how to filter on the result.
Aggregate Before the Join: Shrinking Rows Early With GROUP BY
Aggregate before the join only when the grouping preserves the required customer result. This private lab checks NULLs, missing matches, duplicate dimensions, complete outputs, actual plans, and reads.
ISO Week Numbers vs DATEPART(week): Grouping Sales by Week
Group sales by ISO week and ISO year, compare DATEPART week behavior, and avoid New Year errors caused by session settings.