Good definition on 4GuysFromRolla and MSDN.
To use them you use the 'WITH' keyword, for example if you were using the following Subquery:
SELECT
*
FROM
(
SELECT
p.Forename
, p.Surname
, COUNT(m.MessageId) AS MessageCount
FROM
[Pupil] p
INNER JOIN [Message] m ON (p.PupilId = m.PupilId)
GROUP BY
p.Forename
, p.Surname
) AS PupilsWithMessages
to get all pupils with a message you could now use a CTE,
WITH PupilsWithMessages (Forename, Surname, MessageCount) AS (
SELECT
p.Forename
, p.Surname
, COUNT(m.MessageId) AS MessageCount
FROM
[Pupil] p
INNER JOIN [Message] m ON (p.PupilId = m.PupilId)
GROUP BY
p.Forename
, p.Surname
)
SELECT
*
FROM
PupilsWithMessages
This is a simple example but it looks a lot cleaner than subqueries and you can use them in the same way as you would a view. Better examples on the above links.
Recursive CTE's
You can also use them for recursing, a simple example I came up with was to loop through a comma delimited list of weekdays and return a seperate row for each one. Whereas previously you would need to use a Cursor you can now use a CTE.
DECLARE @String varchar(100)
SET @String = 'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,';
WITH List (Val, Rest, HierarchyLevel) AS (
SELECT
SUBSTRING(@String, 1, CHARINDEX(',', @String)-1) AS Val
, SUBSTRING(@String, CHARINDEX(',', @String)+1,200) AS Rest
, 1 AS HierarchyLevel
UNION ALL
SELECT
SUBSTRING(l.Rest, 1, CHARINDEX(',',l.Rest)-1) AS Val
, SUBSTRING(l.Rest, CHARINDEX(',', l.Rest)+1,200) AS Rest
, l.HierarchyLevel + 1 AS HierarchyLevel
FROM
List l
WHERE
CHARINDEX(',', l.Rest) <> 0
--AND HierarchyLevel < 5 /*You can limit how 'deep' the recursion goes*/
)
SELECT
*
FROM
List
In the 2nd query the CTE references itself (List) to continue splitting up the string until the conditions specified in the WHERE statement are true.
