Temporary tables, CTEs, TVPs and Views in SQL

Say I have the following tables:

Department Table

Dept ID	Dept Name
1	Java
2	DotNet

Employee Table

Emp ID	Employee Name	Dept ID	Gender
12	A	2	F
13	B	2	M
14	C	1	F

And I want a table that gives me the department in which total number of employees >=2 i.e. Dotnet :

Alternative 1: via Views

One way to accomplish this is via Views as

Create View Dep_Emp As (Select DeptName, Count(*) As TotalEmpCount from Employee join Department on Employee.DeptID= Department.DeptID group by DeptName)

 

Dep_Emp View would be created as follows:

DeptName	TotalEmpCount
Java	1
DotNet	2

Then when we run the query:

Select DeptName from Dep_Emp where TotalEmpCount >= 2

We would get the result DotNet, and the view would be created SQL Server under System Databases > tempdb >Views

If we are not going to use the view in multiple places we will have to drop the View as the view will be of no great use in the future.

Alternative 2: Insert into new Tables(Temporary)

I can run the following query with Select into, and then drop the table

Select DeptName, Count(*) As TotalEmpCount  into #tblEmp  from Employee join Department on  Employee.DeptID= Department.DeptID group by DeptName Select DeptName from #tblEmp where TotalEmpCount >= 2 Drop table #tblEmp

 

Select DeptName, Count(*) As TotalEmpCount

 into #tblEmp

 from Employee join Department on Employee.DeptID= Department.DeptID group by DeptName

Select DeptName from #tblEmp where TotalEmpCount >= 2

Drop table #tblEmp

Here #tblEmp is a temporary table and it exists as long as the current session of the user exists.

 The temporary table denoted with a single # symbol is stored in SQL Server under System Databases > tempdb >Temporary Tables and is deleted when the last connection to the db referencing the #tblEmp table is closed.

Alternative 3: Using Table Variable Parameters

We could create a TVP as shown below by declaring the type of table as:

	Declare @tblEmp table (DeptName nvarchar(20), empCount int(19,5)) Insert into @tblEmp Select DeptName, Count(*) As TotalEmpCount from  Employee join Department on Employee.DeptID= Department.DeptID group by DeptName Select DeptName from @tblEmp where TotalEmpCount >= 2

 

This would create a TVP under tempDb of SQL Server

The beauty of TVPs is that

i)                    We do not have to drop TVPs , their scope exists in the statement block, stored procedure or batch in which the TVP is declared

ii)                   We can pass these as parameters to functions

Alternative 4: Using Common Table Expressions/CTEs

Note:

i)                    The scope of the CTE exists as long as the statement following the CTE is executed (over here it is the statement “Select DeptName from Employee_Count where TotalEmpCount >= 2 “)

ii)                   This statement that follows the CTE can be an Insert, update or select statement.

iii)                 There can be multiple CTEs inside the With and all those CTEs together have the scope until the statement that follows the CTE creation ie duration of the query.

Sub queries vs Derived Tables in SQL

Sub queries are best defined as query within a query. An example of a sub query is as follows:

select employee_name

from employee

where employee_salary >

-- this is a subquery:

(select avg(employee_salary)

        from employee)

Here, the subquery is within the where clause. It could be put in the in clause as well as:

Select * from employee where employee in(Select employee from employee_details where salary > avg(salary)

Now consider the following set of queries:

Create view view1 as Select salary from employee (this is for the query in the from clause)

Select max(salary) from view1

Drop view1

This set of queries using view involves writing values to the system catalog tables and three statements have to be written. Instead we could use a derived table.

Derived table is a table which is temporary , and created when you write a query within the From Clause as in:

Select max(salary) from (select salary from employee) As Max_salary

The point to be noted here is that the use of As clause is a must , without which we would get the error : “Every derived TABLE must have its own alias”.

Benefit of Derived table over using views:

i)                    No entry is made to the system catalog table.

ii)                   The temporary table made for the query within the from clause exists until the query is executed i.e the duration of the query

iii)                 It is faster than queries with views.