SQL Server Common Table Expression Hierarchical Example

SQL Server 2005 provides a mechanism called CTE or Common Table Expressions for pre-loading data or setting up for later use. It is really fast compared to pre-2005 which did not have such a feature. To illustrate I'll take some hierarchical data and load it into a CTE.