SQL Server Optimizer is full of surprises… Optimizer uses many transformations to find the best possible path of execution. One of those happen in cases where you do a query that looks like the following;
— displays the duration, cpu, reads, writes and database id
— for the query with the max duration from a profiler trace table
select duration,cpu,reads,writes, databaseid
from dbo.trace_data a
where duration = (select max(duration) from dbo.trace_data b)
Looking at the query many may think we scan the table twice; first to find the max(Duration) then to locate the CPU, Reads, Writes for this instance of Duration. However the optimizer in this case chooses to use a single table scan and “TOP 1” to locate the max(Duration) and to pull the rest of the columns out of the row. The Parallelism (Logical Operator Gather Stream) allows parallel Table Scan and Parallel Sort operations to be merged into a single stream before Top operator is processed.
Here is the execution plan;
By the way depending on the size of your data and underlying indexing SQL Server may still choose to do multiple table scans with a join on the primary key.
Optimizer also will take care of more complex cases with groupings like the following query that outputs the same information but for every database in the trace;
In this instance you see even the TOP parallelized. Also Parallelism (Reparatition Streams) allows randomized streams from the Table Scan to be partitioned over databaseid do sorting can happen within each stream. Parallelism Gather Streams reduces the multiple results into s single stream once we locate the top out of every segment (in this case; databaseid).
This is a simple example of a transform we are doing in the sql engine. SQL Server optimizer is constantly evolving to add new transforms to get better. Keep checking those execution plans and I am quite certain you will find other interesting transforms you did not know the optimizer was applying to your queries.