Derived tables is the most outstanding feature of SQL which provides a momentous help to the developers because they are always created on the fly with the help of SELECT and FROM statements in parenthesis and can also be used in queries such as Sub-Queries, Common Table Expressions, and Views. The amazing point is that no need to drop it because SQL Server is the responsible to create and populate the table in the memory which can be referenced by the outer Select query who created.
To simplify it, a derived table is best thought of as a table within a table that exists only as long as the outer query does. They can assumed as on the fly views also in SQL.
Drawback Points: A derived table only exists in the query in which it is created. So, derived tables are not actually part of the database schema because they are not real tables.
SQL server will eliminate it forever from the memory when the outer query is finished.
Reusable Options: If you want to re-use the output of the Derived table in the SQL query then you must need to be store its output into the table variable or temporary table because they are available till the end of the query session.
Due to reside in the memory, it would be faster the temporary tables which are created in the temp database but in case of if you are fetching few records from the database. SQL server will eliminate it when the outer query is finished.
Explanation of Derived tables: Use of Derived tables are depend on the requirements and we are explaining some of them here. An employee table is used to store all the necessary information of the employees and department master is used to store department related details
SET NOCOUNT ON
---- Create table employee
CREATE TABLE Employees
EmpId Int NOT NULL,
EmpName Varchar(20) NOT Null,
EmpSalary money not null,
DeptId Int Not Null,
EmpAge Int Not Null
----- Insert values into table
INSERT INTO Employees (EmpId, EmpName, EmpSalary, DeptId,EmpAge)
VALUES (101, 'Ryan Arjun', 50000, 51, 23),
(102, 'Kimmy Wang', 42000, 52, 29),
(103, 'Bill Smith', 45000, 52, 35),
(104, 'Rose White', 25000, 53, 21),
(105, 'Mac Johnson', 30000, 51, 32)
----- Output from the table
select * from Employees;
----- create another table department
CREATE TABLE Department
DeptID INT Not Null,
DeptName Varchar(20) Not Null
---- Insert Values into table
INSERT Department VALUES (51, 'Accounts'),
(52, 'Sales'), (53, 'Marketing'), (54, 'Admin')
----- pull values from department table
select * from Department;
---- pull values
select e1.EmpId, e1.EmpName, e1.EmpSalary, dpt.DeptName
from Department dpt
----- derived table
select EmpId, EmpName, DeptId, EmpSalary from Employees
and EmpName like 'R%'
)E1 on dpt.DeptID=E1.DeptId
---- DROP TABLES
DROP TABLE Employees, Department
Referencing Multiple Derived Tables- As we are working with the views, the same way, you need to pull the required columns to put them into SELECT and FROM statements in parenthesis and join them together on the basis of data key relations. In this case, they are best way to provide on the fly views like structures.
Derived tables are the best option to avoid to create views which are not going to be used again and again. They provide a simple way to improve the performance of the SQL Query and SQL Server will take care of the Derived Tables because they stored in the memory and refreshed by the itself.