For windowed functions, SQL Server introduces two new operators in the execution plan; Segment and Sequence Project. If you’ve tried looking them up in the documentation, you’ll know that it’s not exactly perfectly obvious how they work. Here’s my stab at clarifying what they actually do.
Tag: with ties
Using TOP n WITH TIES
Here’s an interesting feature to that may greatly simplifie a few queries. TOP n WITH TIES works pretty much like TOP, except it orders the top records “densely” to include tied values.