With Clause In Sql Server

Before learning about "With" clause and it's usage ,you must know some thing about "Common Table Expression"

Before Sql Server 2005 ,there is no concept of "With" Clause and now may get a doubt that then what is the use of it from Sql server 2005 .In order to simplify your work with temporary tables the Microsoft has introduced "With" Clause from Sql server 2005,but it is not the replacement of temporary tables .

In SQL, Common Table Expression (CTE) is created using the WITH statement followed by common table expression (CTE) name.

What is Common Table Expression?

A Common Table Expression is an expression that returns a temporary result set from the Select statement which we written in "With clause" .

What is Syntax of Common Table Expression?

WITH  Common _Table_expression_name &nbsp [ ( column_name [,...n] ) ] AS ( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query
definition.

A very basic, self-explanatory example:

WITH Dept(Deptid, DeptName) AS ( SELECT Deptid, DeptName FROM Department ) SELECT EmpName ,DeptName FROM Employe Emp join Dept on Emp.deptid= Dept.Deptid

If a Temporary Table is used, first we have to be create, and then use it and it could be called over and over again
but a Common Table Expression must be used immediately after creating it.

With clause helps in providing the re-usability of the code and gives good performance. There are cases where we use the same piece of sub sql query multiple times in complex query. In such cases we can use the with clause and improve the performance of the query. The with clause is just like a table but it stores the data in a temporary location. This can be used in the main query.