After listening to a talk from Ryan Duclos on last evening I decided to delve a little deeper about what is CTE and exactly what can be done with it. Good presentation by the way Ryan. 

Any how as a developer there will come a point  where we will need to use recursion or apply recursive methods to solve a problem when extracting data. In steps CTE aka Common Table Expressions. CTE was introduced in SQL Server 2005 and offers a more readable form of your derived table that can be declared once and referenced numerous times throughout the query. CTEs can be defined recursively allowing a recursive entity to be calculated without the need for recursive SP's(Stored Procedures).

CTE Example : (For the sake of this example we want users that are not in the Systems Department)

--*/Systems Employees = DepartmentID of 8

WITH EmployeesAndDepartmentIDLessThenEight(EmployeeFirstName,EmployeeLastName,Department_ID) as
(
    select e.employeeFirstname,
           e.employeeLastname,   
             e.department_id   

    From tbl_employee as e

WHERE  e.department_id <8

)



select * from EmployeesAndDepartmentIDLessThenEight
order by EmployeeFirstName,EmployeeLastName,Department_ID ASC

This query creates a CTE named EmployeesAndDepartmentIDLessThenEight that returns FirstName, LastName and the DepartmentID of the Employee whose departmentID is less than 8. Once the CTE is defined it must be used in a query. The query treats the CTE as if were a view or a table in the system, returning the fields that we requested.

A CTE allows us to define a temporary, view-like contruct. We start by specifying what columns we want it to return, then define the query. Once that has been done the CTE can be used in a SELECT,INSERT,UPDATE, or DELETE statement.

This concludes part 1 of the 2 part series about CTE.

Happy Coding