Relational databases are great for storing most forms of structured data. The most notable exception is recursive data. Tree-like structures, essential for menus, normally require awkward stored procedures to efficiently return. SQL Server 2005 does have an answer though.
Common Table Expressions, or CTEs, were introduced in SQL Server 2005, but they have not been getting the attention they deserve. In their simplest form, they are a named sub-query that appear before the main query. Since the CTE can be used multiple times, it can make complex queries look significantly cleaner.
The syntax is easy to use, but does have a few points to remember.
;WITH FirstCTE (column1, column2) AS
(SELECT column1, column2 FROM MyTable),
SecondCTE(column1, column2) AS
(SELECT column1, column2 FROM OtherTable)
Select * FROM FirstCTE UNION ALL Select * FROM SecondCTE
Multiple CTEs can be chained together, but they must be used immediately after being defined; no other code can appear between the last CTE and the final query. The semi-colon is only required if the CTE isn't the first statement in the batch, but it is easier to use it every time. CTEs cannot use ORDER BYor COMPUTE, as they are essentially sub-queries.
CTEs becomes really interesting when used recursively. An article in October's MSDN Magazine outlines the rules of recursion.
- Create the query that returns the top level (this is the anchor member).
- Write a recursive query (this is the recursive member).
- UNION the first query with the recursive query.
- Make sure you have a case where no rows will be returned (this is your termination check).
For example,
;WITH MenuCTE(MenuKey, ParentMenuKey, MenuName) AS
(
-- Anchor Query
SELECT MenuKey, ParentMenuKey, MenuName FROM Menu WHERE MenuKey = 1
UNION ALL
-- Recursive Query
SELECT m.MenuKey, m.ParentMenuKey, m.MenuName FROM Menu m
INNER JOIN MenuCTE r ON m.ParentMenuKey = r.MenuKey
)
SELECT MenuKey, ParentMenuKey, MenuName FROM MenuCTE
