how to push sort operator
here in this query sort is spilling to tempdb due to bad cardinality estimations, can some how we can push sort predicate before merge join? any trick
The simplest answer seems to be to force a nested loops join with OPTION (LOOP JOIN). It's difficult to give a comprehensive answer without details.
ALTER DATABASE yourdatabasename SET COMPATIBILITY_LEVEL=120