A guest post by Rick Morelan, author of the Joes 2 Pros series, on the few interview questions that come up again and again and separate good answers from great.
ROWS vs RANGE in Window Frames: Why Running Totals Differ
Compare ROWS vs RANGE for running totals, handle tied sort values, write explicit frames, and check worktable reads on your server.
OFFSET FETCH Paging and Its Deep Page Problem
Use OFFSET FETCH with a unique sort order, compare early and deep page reads, and decide when a cursor based approach fits better.
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.
Pagination With OFFSET and FETCH
OFFSET and FETCH make page-number queries readable, but deep pages scan past prior rows; keyset pagination keeps the next page focused.
Keyset Pagination: Next and Previous Pages Without OFFSET
Build keyset pagination with a matching index, unique tie breaker, next and previous queries, and a fair deep-page comparison with OFFSET.
SQL SERVER – Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used
“What will be the order of the result set of a SELECT statement on clustered indexed table when the ORDER BY clause is not used?”