Adding an INDEX to a CTE

sql-server-2008 common-table-expression
Neil Knight picture Neil Knight · Oct 22, 2010 · Viewed 51.3k times · Source

Should be a pretty straight forward question. Can I add an INDEX to a Common Table Expression (CTE)?

Answer

Richard Vivian picture Richard Vivian · Jul 30, 2012

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

Related questions

Which are more performant, CTE or temporary tables?

Which are more performant, CTE or Temporary Tables?

Read More
Use one CTE many times

I have this, and i get an error at set total. Why can't i access a cte many times? ALTER PROCEDURE [dbo].[GetLeaguePlayers] ( @idleague int, @pageNumber int, @pageSize int, @total int OUTPUT ) AS WITH CTEPlayers AS ( SELECT ROW_NUMBER() OVER (…

Read More
How can I have multiple common table expressions in a single SELECT statement?

I am in the process of simplifying a complicated select statement, so thought I would use common table expressions. Declaring a single cte works fine. WITH cte1 AS ( SELECT * from cdr.Location ) select * from cte1 Is it possible to declare …

Read More