Introduced in SQL Server 2005 , a Common Table Expressions (CTE) is a "temporary result set" that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temporary tables, and cursors.

The table defined in the CTE can be referenced in the CTE itself to give a recursive expression using UNION ALL:

WITH CteExample(x)
AS
(
SELECT x = CONVERT(VARCHAR(1000),1)
UNION ALL
SELECT CONVERT(VARCHAR(1000),x + '1') FROM CteExample  
WHERE LEN(x) < 10
)
SELECT x FROM CteExample
ORDER BY x

The query SELECT x = CONVERT(VARCHAR(1000),1) is generally referred to as the anchor member.  This query is executed during the first pass and populates the CTE with the results, in this situation "1".  This CTE is executed until the complete result set is returned.

The line SELECT CONVERT(VARCHAR(1000),x + '1') FROM CteExample is a recursive member as it references the Common Table Expression CteExample.  The recursive member is executed with the anchor member output to give "11".  The next pass takes "11" as input and returns "111", this continues to occur until the CTE is populated fully based on the data and constraints in the WHERE clause.  The recursion will terminate when the recurisive member produces no rows, in the example above this would be when x has a length of 10.  In this particular example the aforementioned CTE would create the following result set:

1
11
111
1111
11111
111111
1111111
11111111
111111111
1111111111

The CTE is display via the following statement:

SELECT x FROM CteExample
ORDER BY x

This concept can be extended for various uses one such application would be the determination of page hierarchy in a database driven content management system.  Take for example the case of DotNetNuke (http://www.dotnetnuke.com).  This system utilizes a table named "Tabs" to manage the individual pages within any particular portal including but not limited to their names, an page identifier (tabid) and a parent page identifier (parentid).  In the event that a user wanted to determine based on a particular page what hierarchy it belonged to up to the root level, CTE and recursion could be utilized. 

Example:

WITH RecursiveQuery(parentid, tabid) AS
(SELECT tabID, parentID FROM [dnn_tabs] WHERE tabid = 702
UNION ALL
SELECT tabs.tabId, tabs.parentId FROM [dnn_tabs] tabs
INNER JOIN RecursiveQuery ON RecursiveQuery.tabid = tabs.tabid)
SELECT * FROM RecursiveQuery ORDER BY tabid

In the previous example simply replacing the tabid of 702 with tabid of the page in question would result in a record set made up of all the parents of the page specified, thus link a particular page back to the root level.  The following is the result of this query in my test environment based on pages created within, results when running this query within a DotNetNuke installation will vary from installation to installation and by tabid specified depending on the particular pages hierarchal level within the portal.