SQL Server  – Common Table Expression (CTE)

• Common Table Expression (CTE) was implemented in SQL Server 2005
• CTE are not design to be a replacement of the Temp Table
• CTE declare the name at the beginning of code  with keyword “WITH EmpSort_CTE AS”
• CTE exampleWITH EmpSort AS
  

 (SELECT FirstName, LastName, EmpID, LocationID,
 ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS Position
 FROM Employee)
SELECT * FROM EmpSort
GO