CTE Common Table Expression
The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope. The syntax of the CTE is the following. WITH name (Alias name of the retrieve result set fields) AS ( //Write the sql query here ) SELECT * FROM name Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE. CTE 1: Simple CTE WITH ProductCTE AS ( SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price] FROM Products ) SELECT * FROM ProductCTE Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE. This result set can be retrieved like table or view. CTE2:Simple CTE with alias WITH ProductCTE(ID,Name,Category,Price) AS ( SELE