Difference between CTE, Temp Tables, Derived tables

In SQL Server, while working with the large data sets (or huge records), we might require to store the intermediate results in temporary query. So that we can access them further. SQL Server provides multiple options to achieve the same: CTE, Temporary Tables, Derived tables, and Table Variables. In this article we will show you the Difference between CTE, Temp Tables, Derived tables and Table variables in SQL Server with practical examples. For this, We are going to use two tables (Employee Details and Department) present in our [SQL Test] Database.

Data present in the Employee Details Table is:

Data present in the Department Table is:

Difference between CTE, Temp Tables, Derived tables and Table Variable

In this article our object is to display the total number of employees present in each department, their sales amount, and their total annual salaries. The basic structure of the SQL query that we are going to use in all the examples contains:

• We are going to use Inner Join to get column from both Employees and Department tables.
• And, we use GROUP BY Clause to aggregate the columns. I suggest you to refer Aggregate Functions article.

Last we use CTE, Table variable, Temporary Tables, Derived tables to filter the data.

Derived Table Example

We are going to achieve our object using the Derived Column. I suggest you to refer SQL Derived Table article to understand the query.

SQL CODE

-- Difference between CTE, Temp Tables, Derived tables , and Table variable 
USE [SQLTEST]
GO
SELECT * FROM
(
	SELECT  [DepartmentName]
		,COUNT(EmpID) AS [Total Employees in this Department]
		,SUM([YearlyIncome]) AS [Total Income]
		,SUM([Sales]) AS [Total Sale]
	FROM [EmployeeDetails]
	INNER JOIN [Department]
	ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
	GROUP BY [DepartmentName]
) AS [Derived Employee Details]
WHERE [Total Income] > 100000

OUTPUT

SQL Temporary Table Example

We are going to achieve our object using the Temporary table. First we are using the Insert Into Statement to insert records into Local Temporary Table. Next, we are selecting all the records from that temporary table whose Total Income is greater than 100000. I suggest you to refer SQL Temp Table article to understand the query.

SQL CODE

-- Difference between CTE, Temp Tables, Derived tables , and Table variable 
USE [SQLTEST]
GO
--Inserting records into temp table #TempEmployeeDetails
SELECT  [DepartmentName]
	,COUNT(EmpID) AS [Total Employees in this Department]
	,SUM([YearlyIncome]) AS [Total Income]
	,SUM([Sales]) AS [Total Sale]
INTO #TempEmployeeDetails
FROM [EmployeeDetails]
	INNER JOIN [Department]
	ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
GROUP BY [DepartmentName]

-- Selecting Columns from Temp Table
SELECT [DepartmentName]
	  ,[Total Employees in this Department]
	  ,[Total Income]
	  ,[Total Sale]
FROM #TempEmployeeDetails
WHERE [Total Income] > 100000

OUTPUT

SQL Table Variable Example

This time we are going to use Table variable to achieve our object. First we declared a Table Variable, next we are using the Insert Into Statement to insert records into that Table variable. Lastly, we are selecting all the records from Table Variable whose Total Income is greater than 100000. I suggest you to refer SQL Table Variable article to understand the query.

SQL CODE

-- Difference between CTE, Temp Tables, Derived tables , and Table variable 
-- Creating Table Variable
DECLARE @EmployeeTableVariable TABLE
(
	[DepartmentName] VARCHAR(255) NULL,
	[Total Employees in this Department] INT NULL,
	[Yearly Income] FLOAT NULL,
	[Sales Amount] FLOAT NULL
)

-- Inserting Data into Table Variable
INSERT INTO @EmployeeTableVariable
    SELECT  [DepartmentName]
	   ,COUNT(EmpID) AS [Total Employees in this Department]
	   ,SUM([YearlyIncome]) AS [Yearly Income]
	   ,SUM([Sales]) AS [Sales Amount]
    FROM [SQLTEST].[dbo].[EmployeeDetails]
	INNER JOIN [SQLTEST].[dbo].[Department]
	ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
    GROUP BY [DepartmentName]

-- Selecting records from the Table variable
SELECT [DepartmentName], [Total Employees in this Department], [Yearly Income], [Sales Amount]
FROM @EmployeeTableVariable
WHERE [Yearly Income] > 100000

OUTPUT

SQL Common Table Expresion Example

This time we are going to use Common table expression (or CTE) to achieve our object. First we create a CTE. Next, we are selecting all the records from that CTE whose Total Income is greater than 100000. I suggest you to refer SQL Server CTE article to understand the query.

SQL CODE

-- Difference between CTE, Temp Tables, Derived tables , and Table variable 
-- Creating CTE (Common Table Expression)
WITH EmployeeExpression ( [DepartmentName], 
			  [Total Employees in this Department], 
			  [Yearly Income], 
			  [Sales Amount]
			) 
AS
(
    SELECT  [DepartmentName]
	   ,COUNT(EmpID) AS [Total Employees in this Department]
	   ,SUM([YearlyIncome]) AS [Yearly Income]
	   ,SUM([Sales]) AS [Sales Amount]
	FROM [SQLTEST].[dbo].[EmployeeDetails]
	INNER JOIN [SQLTEST].[dbo].[Department]
		ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
	GROUP BY [DepartmentName]
)

-- Selecting records from the EmployeeExpression CTE
SELECT [DepartmentName], 
       [Total Employees in this Department], 
       [Yearly Income], 
       [Sales Amount]
FROM EmployeeExpression
WHERE [Yearly Income] > 100000

OUTPUT

SQL View Example

You can also use Views to achieve our object. First, we create a view that is selecting all the matching records from both the tables along with aggregations. Next, we are selecting all the records from View whose Total Income is greater than 100000. I suggest you to refer SQL View article to understand the query.

SQL CODE

-- Difference between CTE, Temp Tables, Derived tables , and Table variable 
USE [SQLTEST]
GO
--Creating a View
CREATE VIEW vNumberofEmployees
AS
	SELECT  [DepartmentName]
		,COUNT(EmpID) AS [Total Employees in this Department]
		,SUM([YearlyIncome]) AS [Total Income]
		,SUM([Sales]) AS [Total Sale]
	FROM [EmployeeDetails]
	INNER JOIN [Department]
		ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
	GROUP BY [DepartmentName]

-- Selecting Records from vNumberofEmployees View
SELECT * FROM vNumberofEmployees
WHERE [Total Income] > 100000

OUTPUT

Thank You for Visiting Our Blog