comman table expression(CTE) in sql server

What is comman table expression ?

1.Common Table Expression is the temporary result set, and it should be with in the execution
of the one SELECT, DELETE, UPDATE, INSERT or Create View statement.
2.It will work during query execution only.
3.This will store as an object in the SQL Server.
4.CTE is the self referencing and could be able to refer multiple time in the same query.

Syntax:

   :
WITH Expression_Name [ ( ColumnName [1,...n] ) ]
AS
( CTE query definition )

Usage of CTE:

•We can create a recursive query.
•CTE will be the substitute of VIEW. no need to store definition in the metadata.
•This is enable grouping by a column CTE will derived from the scalar subselect.
•Need to refer the same table multiple times in the same query.
•Readability will increase and maintenance of the complex queries very easily.

Examples of CTE:

WITH CTE AS
(
select id,city,SNAME, row_number() over(partition by SNAME order by id desc)
duplicate_id from#TEMP
)
delete CTE where duplicate_id>1
select * from #temp

In above expression I have write a comman table expression for delete duplicate data from
selected table .
In This article I have explain what is CTE (comman table expression ),uses of CTE and example
of CTE.I hope it is helpfull to you ,if it is then give me a comments ,advice ,your questions,and
suggestion.
,Thanks

Related Posts::

Rank(),dence_rank(),row_number() functions in sql server
cursor in sql server