Does the apply operator use row by row processing?

I'm working with 2 of the same datasets. However, one is derived from a temp table and the other is from a derived table (cross apply operator). The derived table takes much longer to process and it is a simple query. I've been told to avoid apply operator because they use row by row processing.

My question(s):

• Does the apply operator use row by row processing?

• Should I limit my use of apply operators like cursors and trigger statements? (Only use in specific situations)

The APPLY operator is a tabular operator in the T-SQL language. As the language itself, it is declarative and does not indicate in any way what is the physical implementation that the relational engine should be using to retrieve the data. This means that the correlation strategy between the input set and the applied function is not limited to nested loops. This highly depends on the type of tabular function that you are applying to the input set, as some functions support predicate pushdown and/or reordering and others don't.

The APPLY operator per se is not inherently good or bad, so it is not among the things that you should try to avoid in your code.

StackExchange Database Administrators Q#158048, answer score: 8