SQL Common Table Expression (CTE)

SQL Server has a very powerful feature has been added for the programmers' benefit: Common Table Expression (CTE). Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query.

We have a simple table Employee in our database.

Employee-table-in-Sql-Server.jpg

Example

;WITH EmployeeCTE AS

( SELECT [EmpID]

,[EmpName]

,[EmpSalary]

FROM [master].[dbo].[Employee]

WHERE [EmpSalary]>4000

)

SELECT * FROM EmployeeCTE

Now press F5 to execute.

Output

CTE-in-Sql-Server.jpg