With Common Table Expression and CREATE VIEW Expression

Question

I have several queries that use the WITH clause or the Common Table Expression clause, with the UNION ALL clause, to repeat a tree-like table on an SQL server, as described here . I would see a difference in performance if I were to CREATE the same VIEW, and not include it in the WITH clause and generate it every time I run the query? Would it be generally considered good practice to actually CREATE a view since it is used in several queries?

Answer 1

What you are looking at is Common Table Expression , not View . If you are doing recursion , you need to stick with CTE, and not try to flip this into a view.

Views in MS SQL do not give you any performance benefits unless you create clustered indexes on them. From the sounds of your question, this is not so. You will probably be better off encapsulating your CTE inside a stored procedure .