Adding an INDEX to a CTE

Can I add an INDEX to a Common Table Expression (CTE)?


  • No.

    A CTE is a temporary, "inline" view - you cannot add an index to such a construct.

    If you need an index, create a regular view with the SELECT of your CTE, and make it an indexed view (by adding a clustered index to the view). You'll need to obey a set of rules outlined here: Creating an Indexed View.