#SQL CTE(Common Table expressions)

SQL Server 2005 and on wards, a very powerful feather has been added for the programmers' benefit called CTE.

CTE is again a temporary result set derived from the underling definition. 

Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system.

CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Common Table Expression Syntax
A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

The query using the CTE must be the first query appearing after the CTE.

Syntax :

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition ) 

Example

(* table scheme is at end of this post) ;WITH myCTE(Name,Age,Designation,RCOUNT)
AS
(
select
Name, 
Age,
Designation,
ROW_NUMBER() over(partition by Name, Age, Designation order by Name) as rcount
From
DupEmployee
)

SELECT * FROM myCTE

Important point that needs a mention is the SELECT followed by the CTE definition, any operation SELECT, INSERT, UPDATE, DELETE or Merge can be performed immediately after the CTE and the CTE lasts only for a single such operation.

;WITH myCTE(Name,Age,Designation,RCOUNT)
AS
(
select
Name, 
Age,
Designation,
ROW_NUMBER() over(partition by Name, Age, Designation order by Name) as rcount
From
DupEmployee
)
update myCTE set Designation='Duplicate' where RCOUNT>1

SELECT * FROM DupEmployee order by Name

;WITH myCTE(Name,Age,Designation,RCOUNT)
AS
(
select
Name, 
Age,
Designation,
ROW_NUMBER() over(partition by Name, Age, Designation order by Name) as rcount
From
DupEmployee
)
delete FROM myCTE where RCOUNT>1

Table creation Code
CREATE TABLE [DupEmployee](
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[Designation] [varchar](50) NULL,
 )

 insert into [DupEmployee] values('Raj',32,'TL')
 insert into [DupEmployee] values('Deena',28,'SS')
 insert into [DupEmployee] values('Raj',32,'TL')
 insert into [DupEmployee] values('Gow',27,'TA')
 insert into [DupEmployee] values('Raj',32,'TL')
 insert into [DupEmployee] values('Siva',27,'TTL')

 insert into [DupEmployee] values('Gow',27,'TA')