A SQL derived table is created with a nested select statement, as in the following example:
select * from (select * from table_1) derived_table_1
An equivalent group of SQL statements that uses a view instead of a SQL derived table requires both create and drop statements:
create view view_1 as select * from table_1 select * from view_1 drop view view_1
The group of SQL statements using a view is more complicated, requiring three statements. Also, the benefits of creating a view for only one query are offset by the overhead of making a system catalog entry. The SQL derived tables feature eliminates this overhead by enabling queries to spontaneously create non-persistent tables without needing to drop the tables or make insertions into the system catalog. This makes a SQL derived table more desirable than a group of SQL statements using a view for ad hoc queries. For repeated queries, a SQL derived table used multiple times performs comparably to a SQL statement using a view with a cached definition. A SQL derived table differs from a temporary table in that a SQL derived table exists only for the duration of the query, while a temporary table exists until the server is brought down.
A system catalog entry occurs for a view that uses a SQL derived table:
create view view_1 as select column_1 from (select column_1 from table_1) derived_table_1
This is likewise true for a stored procedure that uses a SQL derived table:
create proc sp_foo @name varchar(40) as select column_1 from (select column_1 from table_1) derived_table_1
