IS DISTINCT FROM compares nullable values without inventing a sentinel. Test two NULLs, one NULL and changed values, then use the predicate to find transitions that ordinary equality can miss.
Sorting Correctly: Collation and ORDER BY
Collation and ORDER BY decide case, accent, and text-number order; choose rules explicitly when one report needs different sorting.
Unpivoting Columns Into Rows With CROSS APPLY and VALUES
Use CROSS APPLY and VALUES for unpivoting columns, retain nulls deliberately, preserve labels, and turn paired fields into aligned rows.
Count Distinct Pairs: Keep the Columns Together
Count distinct pairs by keeping their columns together. A small T-SQL example shows why concatenated labels can collide. Choose whether incomplete pairs belong in the count.
Ordered Comma Lists With STRING_AGG and WITHIN GROUP
Build ordered comma lists with STRING_AGG and WITHIN GROUP, remove duplicates, handle nulls, and avoid fixed-length result limits.
Oracle to SQL Server: Translating NVL, ROWNUM and Sequences
Translate Oracle to SQL Server expressions for NULLs, row limits, dates, sequences and concatenation, and test where they differ.
SUM DISTINCT: Equal Amounts Are Not Duplicate Rows
SUM DISTINCT totals different amounts without checking order identity. I’d establish the row grain first. Compare equal-priced orders, repeated copies and missing amounts.