1 CTE, VIEW and Derived Table

View

View is the database object in the database. Once the view is being created, it can be access by different sessions.

1. use AdventureWorks;
2.  
3. CREATE VIEW vSalesCount(ProductID,CountProduct)
4. AS
5. SELECT ProductID,COUNT(ProductID) FROM sales.SalesOrderDetail GROUP BY ProductID
6. GO
7. SELECT * FROM vSalesCount

It is also doable to create the index on the view to help out the performance.

Derived Table

Derived table is result of subquery. It is also call anonymous view.

We can rewrite the above view as derived table as below

1. SELECT * FROM
2. (SELECT ProductID,Count(ProductID) FROM  sales.SalesOrderDetail  GROUP BY ProductID)
3. AS Sales_cte(ProductID,CountProduct)

 

CTE (Common Table Expressions)

From SQL Server 2005, add the SQL ANSI 99 defend CTE. We can rewrite the above query as below

1. WITH Sales_cte(ProductID,CountProduct)
2. AS
3. (SELECT ProductID,Count(ProductID) FROM  sales.SalesOrderDetail GROUP BY ProductID)
4. SELECT * FROM Sales_cte
5. GO

CTE exist in same batch. Unlick derived table only exist in the one query. CTE can be reused during the same batch and can self join.

We can also defined multiple CTE in one batch.

1. WITH
2. cteSales(ProductID,CountProduct)
3. AS
4. (SELECT ProductID,Count(ProductID) FROM sales.SalesOrderDetail GROUP BY ProductID)
5. ,
6. cteStatistics(MaxCount,MinCount,AvgCount,DiffCount)
7. AS
8. (SELECT Max(CountProduct),Min(CountProduct),Avg(CountProduct),Max(CountProduct)-Min(CountProduct)
9. FROM cteSales)
10.  
11. SELECT * FROM cteStatistics

Reference

Common Table Expressions http://msdn.microsoft.com/en-us/magazine/cc163346.aspx