Designing SQL Server's Common Table Expressions (CTE's)

My October 2007 Data Points column has been posted to the MSDN Magazine web site. This edition is titled "SQL Server Common Table Expressions".

In this article I focus on using CTEs to address common development scenarios.

- describing how CTEs work

- scenarios they can be used to address

- advantages of using CTEs versus using traditional T-SQL constructs, such as derived tables, views, and custom procedures

- several examples

- how and where they can be used (where not to)

- how CTEs handle recursive logic

- define how a recursive CTE operates

As always, all the code I discuss in the column is available for download from the MSDN^® Magazine Web site and uses the Northwind and the AdventureWorks databases that come with SQL Server 2005.