RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) return the same results until your data has a tie. Day 12 shows how each ranking function handles tied values.
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.
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Advanced Aggregates with the Over Clause – Day 11 of 35
Advanced aggregates with the OVER clause from Joes 2 Pros: partition your data so each number is compared against its own category, not the whole set.
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.
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35
A Joes 2 Pros tip on aggregates with the OVER clause: show each grant next to the total of all grants and work out its share, just like market share.
SQL SERVER – Use INSERT INTO … SELECT instead of Cursor
The story of my old cursor code that got laughed at in code review, and why a single INSERT INTO SELECT instead of cursor loops does the same job better.
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.