Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n) - by MichaelSmith

Status : 


Sign in
to vote
ID 483181 Comments
Status Active Workarounds
Type Suggestion Repros 1
Opened 8/15/2009 9:43:53 PM
Access Restriction Public
Primary Feedback Item 218968


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)
Sign in to post a comment.
Posted by crokusek3 on 4/4/2013 at 4:48 PM
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!
Posted by Microsoft on 8/27/2009 at 3:30 PM
Hello Mike,

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.

Boris Baryshnikov.
SQL Server Engine
Posted by Adam Machanic on 8/16/2009 at 6:38 PM