This is going to be the last post on the operators in an execution plan. I’m not going to cover all the other operators. There are a lot of operators, many quite specialised or only appearing in specific scenarios. Conor Cunningham’s writing a series covering the deep, dark details of some of the operations
So onto the operators. I’m going to cover Sort, Concatenate and Scalar Function.
Sort has three logical operators:
- Distinct Sort
- Top-N Sort
Sort is used to satisfy any ORDER BY that is specified in a query, if the index used does not naturally sort the rows in the desired way. It also may appear in the execution plan before a merge join or a stream aggregate. Sometimes SQL may decide that a sort followed by a merge join or stream aggregate is cheaper than a hash join or hash aggregate.
Distinct sort is used for DISTINCT, sometimes for UNION and sometimes for GROUP BY where no aggregates are specified.