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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- 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
Leave a Reply