One of the uses mentioned for common table expressions (CTE) in SQL Server 2005 is recursive queries. A CTE allows you to define a virtual view that can be used in another statement. At first glance I thought this was awesome. In fact I had just recently implemented a user-defined function to calculate the “pieces per” based on a global trade item number (GTIN), because recursion is necessary to calculate the value.
The following article has more information: http://geekswithblogs.net/tonyt/articles/70279.aspx