Apply Operator

Very useful if you need to use a table-valued function (TVF) on each row returned by a query. Check out

http://msdn2.microsoft.com/en-US/library/
ms175156(SQL.90).aspx

One of the example shown was to make use of a TVF to recursively find the subordinate of a manager and displaying it.

Such queries is not straightforward if you make use of a naive cartesian product.