Tuesday, November 29, 2011

1 CTE, VIEW and Derived Table


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

  1. use AdventureWorks;
  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

  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)
  11. SELECT * FROM cteStatistics



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


  1. Breitling,uk replica watches founded in 1884, replica breitling premier watches is a Swiss luxury watch brand. It is popular among aviators and piltos who often use the Breitling chronometers under difficult and challenging conditions. The brand mainly produces two in-house calibers, Breitling calibre B01 and Breitling calibre B04 (similar to B01 but with a GMT feature).



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates