Hi all,

I noticed a lot of discussions on the cte feature of 2k5.

My experiemnation on this feature tend to show the following:

1. CTE is a powerful standalone tool to query a mix of heirarchical and flat data across multiple database tables. It can be enhanced to do more and more complex queries with ease.

2. The performance has been mostly less [about 70% of conceivable situations] than convetional queries. Only in a few situations that too in tree like data it beat conventional query

3. Main drawback for the cte is when it is used in a generic function/view. We loose most important feature of indexing for views when cte is a part of the view.

4. For obvious reasons left/right outer joins are not acceptable in a cte. These features are required in many business logics though.

5. Some of the experimentations I did make me beikeve that when cte view is called from out side with a parametric filter, query plan still shows all the rows in the view are first returned and then filtered in next step.

I may come with specific examples when free, but these are my first readinds.

I guess it is a new and powerful tool, but needs more features to accommodate most business logics.

Bhaskar