Inside Microsoft® SQL Server™ 2005: T-SQL Querying

Tiebreakers

In this section, I want to introduce a new technique based on aggregates to solve tiebreaker problems, which I started discussing in Chapter 4. I’ll use the same example as I used there—returning the most recent order for each employee—using different combinations of tiebreaker attributes that uniquely identify an order for each employee. Keep in mind that the performance of the solutions that use subqueries very strongly depends on indexing. That is, you need an index on the partitioning column, sort column, and tiebreaker attributes. But in practice, you don’t always have the option to add as many indexes as you like. The subquery-based solutions will greatly suffer in performance from a lack of appropriate indexes. Using aggregation ...