RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) return the same numbers until your data has a tie. This post ranks eight teas with all three functions and shows how each one handles tied scores. It also shows how to list the top three distinct scores and why ROW_NUMBER needs a tiebreaker.
geometry STLength: A Path Is Longer Than Its Chord
geometry STLength measures the segments of a line. The example contrasts that path length with the shortest distance between its endpoints, including a closed line.
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.
FOR XML XSINIL: Emit Missing Fields as Explicit Nil Elements
FOR XML XSINIL can represent SQL NULL with an explicit element carrying the xsi:nil attribute. I compare that output with ordinary element omission. An empty string stays a separate input in both versions.
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.
SQL Server: Use INSERT INTO SELECT Instead of a Cursor
Replace a row-by-row server inventory copy with INSERT INTO SELECT and test the results, including a loop that fails when IDs have gaps.
LIKE Character Ranges: [a-f], [^0-9] and Collation Traps
A bracket range sounds simple until the collation decides what sits between a and f. Test it once so a bad code never reaches your table.