I have several queries which use a WITH clause, or Common Table Expression, with a UNION ALL statement to recur through a table with a tree like structure in SQL server as described here. Would I see a difference in performance if I were to CREATE that same VIEW instead of including it with the WITH clause and having it generated every time I run the query? Would it generally be considered good practice to actually CREATE the view since it is used in several queries?
+3
A:
What you're looking at is a Common Table Expression and not a View. If you're doing recursion, you need to stick with the CTE and not try to roll that into a view.
Views in MS SQL don't give you any performance benefits unless you are creating clustered indexes on them. From the sounds of your question, that is not the case. You'd likely be better off encapsulating your CTE inside of a stored procedure.
Scott Ivey
2009-08-03 21:09:55
http://msdn.microsoft.com/en-us/library/ms190766.aspxIt looks like a Common Table Expression is the WITH clause that I am currently using. Is there a performance difference between the CTE and CREAT-ing an actual VIEW?
Bryan Ward
2009-08-03 21:15:01
no, there should be no performance difference as long as they both use the same query execution plan.
Scott Ivey
2009-08-03 21:27:14
@Scott: If the same CTE is used in multiple places - function(s), proc(s), etc - would you agree that you create the view rather than have numerous separate instances of the same code? Assuming it wouldn't be an indexed (AKA materialized) view.
OMG Ponies
2009-08-04 04:07:53
Sure, I'd go ahead and do that - as long as your CTE code will work in the body of a view (which I assume it should). Putting it in a view will simplify all of your other queries that use the same code.
Scott Ivey
2009-08-04 04:11:23