Menu
Can I add an INDEX to a Common Table Expression (CTE)?
701
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.)
No you can not create an index on parts of a query, during the query. CTE (common table expressions), is also called Subquery Factoring.
If your CTE is based on a single table then you can update using CTE, which in turn updates the underlying table.
CTE can be used for both selects and DML (Insert, Update, and Delete) statements.
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
72
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.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
