View
View is the database object in the database. Once the view is being created, it can be access by different sessions.
- use AdventureWorks;
- CREATE VIEW vSalesCount(ProductID,CountProduct)
- AS
- SELECT ProductID,COUNT(ProductID) FROM sales.SalesOrderDetail GROUP BY ProductID
- GO
- 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
- SELECT * FROM
- (SELECT ProductID,Count(ProductID) FROM sales.SalesOrderDetail GROUP BY ProductID)
- 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
- WITH Sales_cte(ProductID,CountProduct)
- AS
- (SELECT ProductID,Count(ProductID) FROM sales.SalesOrderDetail GROUP BY ProductID)
- SELECT * FROM Sales_cte
- 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.
- WITH
- cteSales(ProductID,CountProduct)
- AS
- (SELECT ProductID,Count(ProductID) FROM sales.SalesOrderDetail GROUP BY ProductID)
- ,
- cteStatistics(MaxCount,MinCount,AvgCount,DiffCount)
- AS
- (SELECT Max(CountProduct),Min(CountProduct),Avg(CountProduct),Max(CountProduct)-Min(CountProduct)
- FROM cteSales)
- SELECT * FROM cteStatistics
Reference
Common Table Expressions http://msdn.microsoft.com/en-us/magazine/cc163346.aspx
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).
ReplyDelete