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.