Common Table Expression in SQL Server

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

Ebook Download
View all
Learn
View all