Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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)

4 votes
Sign in
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 17

<=-=Aug 16 2009 6:38PM=-=>


<=-=Aug 27 2009 3:30PM=-=>

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

<=-=Apr 4 2013 4:48PM=-=>

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!


Sign in
Sign in with: Microsoft
Signed in as (Sign out)

Feedback and Knowledge Base