.jpg)
What is Derived Tables
& CTE in Sql Server
Compare with – views, tables variables, local and global temp
tables
--*********************************************************
create table Department
(
DeptId int primary key,
Deptname
varchar(23)
)
create table Employee
(
Id int,
Name varchar(32),
Salary float,
Gender varchar(23),
DepartmentId
int foreign key references Department(DeptId) on delete cascade
)
insert into Department
values(1,'IT')
insert into Department
values(2,'HR')
insert into Department
values(3,'PAYROLL')
insert into Department
values(4,'ADMIN')
insert into Employee values(1,'Kiran',4500,'male',3)
insert into Employee values(2,'Sara',3400,'female',1)
insert into Employee values(3,'john',4000,'male',2)
insert into Employee values(4,'kenny',5200,'female',4)
SELECT * FROM Department
SELECT * FROM Employee
--*********************************************************
1. VIEWS:- Views gets saved in the database, and can be
available to other queries and stored procedures. However, if this view is only
used at this one place, it can be easily eliminated using other options, like
CTE,Derived tables, temp tables, table variables etc.
-- using View ...................
create view
VwCompareViewCount
as
select Deptname,deptId, COUNT(*) as TotalEmployee
from Employee
join Department
on Employee.DepartmentId=Department.DeptId
group by Deptname, DeptId
--****************
select * from VwCompareViewCount
--******************
select DeptId,Deptname,TotalEmployee from
VwCompareViewCount
where TotalEmployee>=2
Result:
2. TEMP TABLES:-Temporary tables are stored in TempDB. Local
temporary tables are visible only in the current session, and can be shared
between nested stored procedure calls. Global temporary tables are visible to
other sessions and are destroyed ,when the last connection referencing the
table is closed.
---USING TEMP TABLES---
Select Deptname,DepartmentId, COUNT(*) as totalEmployee
into #TempEmployeeCount
from Employee em
join Department dp
on em.DepartmentId=dp.DeptId
group by Deptname,DepartmentId
--******************
select * from #TempEmployeeCount
--****************
select Deptname,totalEmployee
from #TempEmployeeCount
where totalEmployee>=2
-- drop temp table
Drop table
#TempEmployeeCount
Result:
3. TABLE VARIABLES:-
Note:- Just like TempTables,
A table variable is also created in TempDB. The scope of a table variable is
the batch, stored procedure, or statement block in which it is declared.
They can be passed as parameters between procedures.
---using tables variables---
Declare @TableEmployeCount table
(
Deptname
varchar(32),
DepartmentId
int,
TotalEmployee
int
)
insert @TableEmployeCount
select Deptname,DepartmentId, COUNT(*) as Totalemployee
from dbo.Employee em
join dbo.Department
dp
on em.DepartmentId=dp.DeptId
group by Deptname,DepartmentId
select DepartmentId,Deptname,Totalemployee
from @TableEmployeCount
where Totalemployee>=2
Result:
Note-:if we execute then we
select all both created @TableEmployeCount
table and select query include to run this query but output is SAME
4. DERIVED TABLES:- A derived table is a table expression that appears in the
FROM clause of a query. You can apply derived tables when the use of column
aliases is not possible because another clause is processed by the SQL
translator before the alias name is known.
--USING DERIVED TABLES---
Select DepartmentId,Deptname,TotalEmployee
from
(
select
Deptname,DepartmentId,
COUNT(*) as Totalemployee
from
Employee em
join
Department dp
on
em.DepartmentId=dp.DeptId
group
by Deptname,DepartmentId
)
as EmployeeCount --(EmployeeCount is variable)
where Totalemployee>=2
Result
Note: if we execute then we select all
both created EmployeeCount table and select query include to run this query but
output is SAME
Derived tables are available only in the context of the
current query.
5. CTE (Comman Table Expression):- CTE is used as start WITH keyboard. We can
called as temporary table
---Using CTE..................
With EmployeeCountCTE(Deptname,DepartmentId,
TotalEmployee)
as
(
select
Deptname,DepartmentId,
COUNT(*) as TotalEmployee
from
Employee em
join
Department dp
on
em.DepartmentId=dp.DeptId
group
by Deptname,DepartmentId
)
select DepartmentId,
Deptname, TotalEmployee
from
EmployeeCountCTE
where Totalemployee>=2
--we executed select all data query output as
same
Result
Note:- A CTE can be thought of as a temporary result set that is defined with
in the execution scope of a single SELECT,INSERT,UPDATE,DELETE, OR CREATE VIEW
statement. A CTE is similar to a derived table in that it is not stored as an
object and lasts only for the duration of the query.
