WITH (common table expressions)

Common table expressions are a good way to reuse a piece of SQL.

;WITH Sales_CTE 
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

Bonus points: recursive CTEs.

The general form is:

Define a base case.
UNION ALL
Define a recursive case.

Example:

;WITH cte AS
(
    -- Define a base case
    SELECT CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID
    FROM dbo.YourTable
    WHERE parent_id IS NULL

    -- UNION ALL
    UNION ALL

    -- Define a recursive case
    SELECT CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID
    FROM dbo.YourTable t
    INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY ID