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)

3 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    Upvotes: 17

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

    Duplicate:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=218968

    <=-=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.

    Regards,
    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!

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base