Common Table Expresseion (CTE)

A common table expression (CTE) is a derived table that is defined and only stored for the duration of the query. CTE’s can be used for recursive queries, creating a view on demand and referencing the same table multiple times in the same query.  CTE’s make your code more readable and manageable by reducing the complexity into separate building blocks.  CTE’s can be used in queries, User Defined Functions, Stored Procedures, Triggers or Views.

The following Script calculates the years of service for employees in the adventure works database and returns the result set in descending order.

with Emp_CTE (LoginID , JobTitle , OrgLevel , Years_Employeed )

as

 (  SELECT

               LoginID, JobTitle, OrganizationLevel, datediff(year , HireDate , getdate())

   FROM [AdventureWorks2008R2].[HumanResources] .[Employee]

  )

  select *   from Emp_CTE

order by Years_Employeed desc ;

For complete information see: http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

The post Common Table Expressions (CTE) appeared first on Derek E Wilson - Blog.