View is the database object in the database. Once the view is being created, it can be access by different sessions.
It is also doable to create the index on the view to help out the performance.
Derived table is result of subquery. It is also call anonymous view.
We can rewrite the above view as derived table as below
CTE (Common Table Expressions)
From SQL Server 2005, add the SQL ANSI 99 defend CTE. We can rewrite the above query as below
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.
Common Table Expressions http://msdn.microsoft.com/en-us/magazine/cc163346.aspx