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
Sort has three logical operators:
- Sort
- 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.
Concatenate
Concatenate is used to append one rowset to another. It mainly appears in queries using UNION or UNION ALL.
Scalar Function
The scalar function operator is an innocuous loosing operator that can conceal a great deal of problems. It’s used for all forms of scalar functions, from UPPER, SUBSTRING, ROUND and the like, to complex user-defined scalar functions that affect a million of so records each time they’re called.
I’ll go into more detail on the problems of Scalar Function in another article, but in short, the relative cost displayed in the execution plan cannot be trusted, and from the exec plan there’s no way to see what the function is doing. It may be something simple, or it may be a custom function aggregating thousands of rows.
That’s pretty much that on the major operators of an execution plan. In the next article, I’ll discuss some of the common properties of the execution plan operators.