I know you're fan of using CTE's, but to be frankly, I don't a huge application in them anyways. First off all CTEs can be easily circumvented with either temporary tables, or with calling recursive functions. It only makes things a bit easier and more compact, but it does not create a whole new opportunity that outlines the strenght. BTW, as I outlined before SQL servers (whether oracle or MS SQL server) are not efficient at solving hierarchical problems anyways, since there is a record oriented component to the problem. (as we discussed before).

Walter, CTE's are a big hit with SQL developers. One area (of many areas) is vendors who build e-commerce application solutions. CTEs provide for much better hierarchical queries against any differnt product hierarchy.

Front-end tools like crystal reports have their own pivot (cross tab) functionality that is far more flexible in usability.

Actually, that's not true (and I'm a big fan/heavy user of Crystal). While Crystal and SSRS have cross-tab/matrix capabilities, you are more "locked" in to their interface (though that will partly change in SSRS2008 with the new tablix control). If one has access to a PIVOT statement, one generally has more flexibility doing it in the SQL statement.

Until today, I've never really felt the need to use them, and I can't see when I want to use them. Maybe you can give me an example where a subselect or even executing an SP or seperate SQL select in a temp table cannot do the job.


They "can" - however if you have a reusable TVF, APPLY is cleaner, requires less code, and doesn't require a temp table. APPLY is certainly not a "deal-breaker", but I've found it to be a cleaner way to train developers on how to "apply" the results of a TVF to another query.


Tough I never had to use that, I think that RANKing is a handy function in SQL2005. But again nothing shocking that makes me want to jump SQL server right away.

RANK is a great feature for implementing server-side paging of result sets. Certainly better than approaches that return a full result set to the application layer and handle the paging from there.

I agree and that is not its intention. It is to provide a free database platform that is able to do the basic tasks you expect from a database. If you're writing straightforward web apps, I can't see why you would need much beyond the capabilities of MySQL, but then again, I'm not writing web apps.

I'm not trying to be mean or anything, but those last five words are critical to this conversation. Even before I got down to this sentence, I referred to e-commerce vendors who were ecstatic about CTEs, and many web-developers who jumped at the chance to use the RANK function for custom paging. While I won't make a blanket statement, I think the odds are a bit higher that a web developer will appreciate SQL '05 (and SQL '08 when it comes out) maybe slightly more than a developer working on desktop apps, LAN apps, etc.

Currently in our strategic product, we use SQL2000 as a base, because that is what clients at minimum will have running. I've had to crack pretty tough queries due to the complexity of the product, but I seldomly had the feeling that SQL2005 could make this easier. And yes, I'm familiar with the new features in SQL2005.

Well, all I can say is that when I first looked at the new features in T-SQL '05, I thought they were nice....but didn't gain a full appreciation until I started working with them.