Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n)
Sometimes queries would perform faster if one or more table expressions were evaluated individually & "materialized", rather than the optimizer/algebrizer transforming the query.
Providing a query hint which enumerates those CTEs we want materialized would provide a more elegant alternative to temp tables/variables and manually breaking-out the query into steps. Of course, there will be times when a temp table, and appropriate interim indexes, will still be needed. But materializing as a worktable, could improve performance, while keeping the query SQL-esqe. (as opposed to procedural)
Upvotes: 17<=-=Aug 16 2009 6:38PM=-=>
Thank you for the feedback and sorry for the delay in responding to you! I’m going to resolve this item as duplicate of the feedback item 218968 (see Adam’s comment). You can also vote on that item to increase its visibility internally.
SQL Server Engine
Please consider the ability to specify the materialized table’s PK with the materialization hint else I’m still out of luck for some cases. I am constantly using temp tables to work around the lack of materialization issue. Thanks!