What is Derived Tables & CTE in Sql Server and Compare with – views, tables variables, local and global temp tables

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.