Common Table Expressions

Introduced in SQL 2005 was the Common Table Expression (CTE). These can act like subqueries or views within a SQL statement.

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.