Use of SQL Server CTEs

SQL Server 2005 introduced Common Table Expression concept.
CTE can be used as alternative to complex sub queries.
CTEs replace traditional Group By condition to remove duplicate records from db tables having or not having primary keys.

Syntax:
;WITH CTE1(Id,Name) as
(
   SELECT EMPLOYEE_ID, EMPLOYEE_NAME FROM EMP
)
SELECT Id, Name FROM CTE1

CTE can be defined with or without parameters; when CTE is defined with parameters, its definition should contain same number of parameters.

CTEs can be used in SQL JOIN conditions similar to db tables or views.
Also we can perform DML operations on the CTEs.

For more information on CTEs, please refer to the link here.

Please add your valueble comments on this post.

Community Tech Days is Back

Hi guys,
Community Tech Days is back in Hyderabad on 18th Dec , 2010.
Registration for inperson and online partiicipation is open now.

For more details, please visit http://www.communitytechdays.com/

Regards,
DP.