CTE (Common Table Expression) in Sql Server

Hi Techies,

In this article i am going to introduce CTE (Common Table Expression).

Common Table Expression (CTE): 
It is used for create temporary named result set with calculation or conditional result within the query or tables. 
This clause is a simple query and defined within the execution scope of a single statement like SELECT, INSERT, UPDATE, or DELETE. 
This clause can also be used in a CREATE VIEW statement. This is referred to as a recursive common table expression in SQL Server.

EX:

--EXEC [TestProcedure]
Create PROC TestProcedure
AS 
BEGIN
 ------------------------------------------------------------
;with
CTE_Data as
(
    SELECT Top 10 * FROM Employee 
    union all 
    SELECT Top 10 * FROM Employee 
)
----------------------------------------------------------------
select * from CTE_Data 
END