sql-server-2008common-table-expression

Adding an INDEX to a CTE


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


Solution

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