Derived table and CTE in sql server - Part 48

In this video we will learn about, Derived tables and common table expressions (CTE's). We will also explore the differences between Views, Table Variable, Local and Global Temp Tables, Derived tables and common table expressions.







Let's create the required Employee and Department tables, that we will be using for this demo.

SQL Script to create tblEmployee table:
CREATE TABLE tblEmployee
(
Id int Primary Key,
Name nvarchar(30),
Gender nvarchar(10),
DepartmentId int
)

SQL Script to create tblDepartment table 
CREATE TABLE tblDepartment
(
DeptId int Primary Key,
DeptName nvarchar(20)
)

Insert data into tblDepartment table
Insert into tblDepartment values (1,'IT')
Insert into tblDepartment values (2,'Payroll')
Insert into tblDepartment values (3,'HR')
Insert into tblDepartment values (4,'Admin')

Insert data into tblEmployee table
Insert into tblEmployee values (1,'John', 'Male', 3)
Insert into tblEmployee values (2,'Mike', 'Male', 2)
Insert into tblEmployee values (3,'Pam', 'Female', 1)
Insert into tblEmployee values (4,'Todd', 'Male', 4)
Insert into tblEmployee values (5,'Sara', 'Female', 1)
Insert into tblEmployee values (6,'Ben', 'Male', 3)

Now, we want to write a query which would return the following output. The query should return, the Department Name and Total Number of employees, with in the department. The departments with greatar than or equal to 2 employee should only be returned.



Obviously, there are severl ways to do this. Let's see how to achieve this, with the help of a view
Script to create the View
Create view vWEmployeeCount
as
Select DeptName, DepartmentId, COUNT(*) as TotalEmployees
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
group by DeptName, DepartmentId

Query using the view:
Select DeptName, TotalEmployees 
from vWEmployeeCount
where  TotalEmployees >= 2

Note: Views get 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 Variable etc.

Now, let's see, how to achieve the same using, temporary tables. We are using local temporary tables here.
Select DeptName, DepartmentId, COUNT(*) as TotalEmployees
into #TempEmployeeCount
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
group by DeptName, DepartmentId

Select DeptName, TotalEmployees
From #TempEmployeeCount
where TotalEmployees >= 2

Drop Table #TempEmployeeCount

Note: 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 Table Variable:
Declare @tblEmployeeCount table
(DeptName nvarchar(20),DepartmentId int, TotalEmployees int)


Insert @tblEmployeeCount
Select DeptName, DepartmentId, COUNT(*) as TotalEmployees
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
group by DeptName, DepartmentId

Select DeptName, TotalEmployees
From @tblEmployeeCount
where  TotalEmployees >= 2

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 Derived Tables
Select DeptName, TotalEmployees
from 
(
Select DeptName, DepartmentId, COUNT(*) as TotalEmployees
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
group by DeptName, DepartmentId

as EmployeeCount
where TotalEmployees >= 2

Note: Derived tables are available only in the context of the current query.

Using CTE
With EmployeeCount(DeptName, DepartmentId, TotalEmployees)
as
(
Select DeptName, DepartmentId, COUNT(*) as TotalEmployees
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
group by DeptName, DepartmentId
)

Select DeptName, TotalEmployees
from EmployeeCount
where TotalEmployees >= 2

Note: A CTE can be thought of as a temporary result set that is defined within 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.

10 comments:

  1. Dear Venkat,
    First of all, thank you so much for all the tutorial videos. You are truly one of the best online.

    Can you tell me what scenarios I should consider using table variable, temp table, or CTE? They all seem to serve the same purpose, which is providing a temporary set of data, correct? What are the advantages and disadvantages of using each?

    ReplyDelete
    Replies
    1. Respected Venkat Sir,
      I also want to know about this question,
      They all seem to serve the same purpose, which is providing a temporary set of data, correct?

      Delete
    2. 1.Temp table Can have constraints where CTE cannot, 2.TT Can be referenced by other queries or subprocedures where CTE cannot. 3. A temp table is a table created on disk so you can use it more than once while the CTE only use within specific the Query as its not in the memory.

      Delete
  2. Yes Mr. Venkat You are trillionth one best online

    ReplyDelete
  3. Dear Venkat, Thanks for all the videos. I was practicing with the Table variable example given by you. When I am running the example I am getting the error to declare the Table Variable. My Code is as below. Can you please let me know, what mistake I am making here.

    Declare @Employeecount Table
    (DeptName varchar(15), Departmentid int, TotalEmployees int)
    Insert @Employeecount
    Select Department.Dept_name, example.Dept_id, count (*) As TotalEmployees
    from example
    Join Department
    on example.Dept_id = Department.Dept_id
    Group By Department.Dept_name, example.Dept_id

    Select Deptname, TotalEmployees from @Employeecount
    Where TotalEmployees>=2

    ReplyDelete
    Replies
    1. Hope you have got the answer by now! if not.

      - Table name example ? it should be tblemployee

      - you have to select Declare statement & select statement both and hit execute.

      Delete
    2. Just change the line:

      Insert @Employeecount => Insert Into @Employeecount

      Delete
    3. you need to select all the statements together before hitting execution otherwise you will see the error that you mentioned .So select everything like one single query you will get result thanks

      Delete
  4. Sir, how to implement a table with query to record monthly fee collection of N student and display the paid & unpaid months of each student and corresponding fee informations

    o/p interface like
    --------------


    user_info
    ------------------------------
    |user name|-----|fee info btn|
    ------------------------------

    when clicked on fee info btn shows below information
    fee_info
    --------------------------------
    |JAN|FEB|.................|DEC|
    -------------------------------
    | y | n | ----------------| y |
    -------------------------------

    ReplyDelete
    Replies
    1. @ Mathews: Need to use pivot function to convert column data row data for your requirement.

      Delete

It would be great if you can help share these free resources