Adding an INDEX to a CTE

I have had the same requirement. Indexes can not be added to a CTE. However, in the CTE select adding an ORDER BY clause on the joined fields reduced the execution time from 20 minutes or more to under 10 seconds.

(You need to also ADD SELECT TOP 100 PERCENT to allow an ORDER BY in a CTE select.)

[edit to add paraphrased quote from a comment below]:
If you have DISTINCT in the CTE then TOP 100 PERCENT doesn't work. This cheater method is always available: without needing TOP at all in the select, alter the ORDER BY statement to read:
ORDER BY [Blah] OFFSET 0 ROWS

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.

You cannot index a CTE, but the approach is that the CTE can make use of the underlying indexes.

WITH cte AS (
    SELECT myname, SUM(Qty) FROM t GROUP BY myname
)
SELECT *
FROM t a JOIN cte b ON a.myname=b.myname 

In the above query, a JOIN b cannot make use of an index on t.myname because of the GROUP BY.

On the other hand,

WITH cte AS (
    SELECT myname, SUM(Qty) OVER (PARTITION BY myname) AS SumQty, 
                ROW_NUMBER() OVER (PARTITION BY myname ORDER BY myname, Qty) AS n
)
SELECT * FROM t a JOIN cte b ON a.myname=b.myname AND b.n=1 

In the latter query, a JOIN b can make use of an index on t.myname.

Another technique is to insert into a temp table instead of using a CTE You can then add an index to the temp table

I reduced a 9min query to a 3 sec query by doing this.

Some may be religiously opposed to temp tables. If this is you, feel free to click the downvote button!

for the rest of us trying to get things working... something to consider.

( I did try the top 100000 ... order by) I did not realize a time reduction.