Differences Among CTE, Derived Table, Temp Table, Sub Query And Temp Variable

Sometimes we're required to store the results of queries temporarily for further use in future queries. SQL Server provides CTE, Derived table, Temp table, subqueries and Temp variables for this. All of these can be used to store the data for a temporary time. We will learn the differences among all these temporary storage mechanisms and also in what type of scenario or condition which mechanism will be used.

Today I will explain all of these temporary storage mechanisms and also explain the conditions and queries where they can be used.

CTE:

The CTE is an abbreviation of “Common Table Expression.” CTE was introduced in SQL Server. It works as a temporary result set that is defined within the execution scope of a single select, insert, update, delete statements. CTE is typically the result of complex sub queries. Similar to temporary tables CTE doesn’t store as an object; the scope is limited to the current query. CTE improves readability and ease in maintenance of complex queries and sub-queries.

Example:

WITH CTE(Emp_ID, Emp_Name, Project_Name)
AS
    (
        SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e INNERJOIN dbo.Project p ON e.Emp_Id = p.Project_Id
    )
SELECT * FROM CTE

Output:



CTE with recursion query:

DECLARE @Min int;
DECLARE @Max int;
SET @Max = 10;
SET @Min = 1;

WITH Sequence_ AS

(
    SELECT @Min ASnum UNIONALL SELECT num + 1 FROM Sequence_ WHERE num + 1 <= @Max
)
SELECTnumFROM Sequence_

Output:



In the above example we used the recursive CTE and generated the sequence between 1 and 10.

When to use CTE

• Use for recursive query.
• CTE is easy to implement compared to complex queries which involves several sub-queries.

Temp Variables:

Temp Variables are also used for holding the data fora  temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.

Example:

Declare Temp variable:
Declare @My_var2TABLE
    (
        IIDint,
        NameNvarchar(50),
        SalaryInt,
        City_NameNvarchar(50)
    )

Insert result set of another query into temp variable,

Insert Into @My_var2
Select * from Employee
WHERE IID<8 AND Salary>20000

Retrieve data from temp variable: 

Select*from@My_var2

Output:



Limitations Of Temp Variables

1. Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter.
   
   
2. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.
   
   
3. We cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table. It can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. Indexes cannot be added after the table has been created.
   

When to use Temp Variables:

• When you are required to use the current result set in next queries then store result into temp variables.
  
  
• When your data set is smaller, approximately less than 1000 records, then use temp variables , if your result set is larger then use temp table instead of temp variables.

Temp Table:

Temporary tables are tables that are available only to the session that created them.

These tables are automatically destroyed at the termination of the procedure or session that created them. SQL Server provides the concept of temporary table which helps the developers in a great way. These tables can be created at runtime and can do all kinds of operations that one normal table can do. In SQL Server all temporary tables are present in tempdbdatabase.

Types of Temporary Tables:

SQL Server contain two types of Temporary tables:

1. Local Temporary Tables
2. Global Temporary Tables

Local Temporary Tables:

Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables start with(“#”) hash sign.

Example:

Create local temp table.
CREATE TABLE# TEMP_SAVE
    (
        Emp_IdINT,
        Emp_NameVARCHAR(30),
        Project_NameVARCHAR(30)
    )

Insert data into temp table.

INSERT INTO # TEMP_SAVE
SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e
INNERJOIN
dbo.Project p
ON
e.Emp_Id = p.Project_Id

Select data from temp table.



Global Temporary Tables:

Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table, then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of Global temp tables start with (“##”) double hash sign.

Example:

Create a Global temp table

Create Table## TEmp
    (
        IIDintidentity(1, 1),
        Namenvarchar(50),
        Salaryint
    )

Insert Data into Global Temp table

Insert Into##TEmp
Select 'Pankaj', 25000 UnionAll
Select 'Rahul', 24000 UnionAll
Select 'Sanjeev', 23000 UnionAll
Select 'Sandeep', 22000 UnionAll
Select 'Naru', 27000

Fetch Data From Global temp Table



When to Use Temporary Tables

1. We required to hold data from further query.
2. When we have a complex Join operation.
3. In some cases it may be a replacement of cursors.

Derived Table:

Derived tables are the tables which are created on the fly with the help of the Select statement. Derived table expression appears in the FROM clause of a query. In derived table server create and populate the table in the memory, we can directly use it and we also don’t require to drop the table. But scope of derived table is limited to the outer Select query who created it. Derived table can’t be used further outside the scope of outer select query.

Example:

SELECT * FROM
(SELECT e.Emp_Id,e.EmployeeName,p.Project_Name FROM dbo.Employee e
INNERJOIN
dbo.Project p
ON
e.Emp_Id=p.Project_Id)Tab
WHERE Tab.Emp_Id%2=1

Output:



Example:



Consider the above table, now we have the sum the salary of employees for each group of age.

SELECT SUM(Tab.Salary)AS Total_Salary,Tab.Age
FROM
(SELECT e.Salary,e.AgeFROMdbo.Emp e
WHERE e.Age>20)Tab
GROUPBY Tab.Age

Output:



When to Use:

• When you are required to perform some complex query and result of inner query work as table for outer query.

Subquery:

A subquery is a SELECT statement that is nested within another statement. Subquery is also known as nested query. Result of subquery is used in the main query as a condition to further restrict the data to be retrieved. The subquery used in select, update, delete and insert command.

Example:

SELECT * FROM dbo.Emp e
WHERE e.Id IN(SELECT e2.Emp_Id fromdbo.Employee e2);

Output:



Example:

SELECT * FROM dbo.Emp e
WHERE e.Id>(SELECT max(e2.Emp_Id) from dbo.Employee e2);

Output:



When to use:

• When you require that inner query or condition must be fulfilled before generating any result.
  
• Use subquery when the result that you want requires more than one query and each subquery provides a subset of the table involved in the query.
  
• If the query requires a NOT EXISTS condition, then you must use a subquery because NOT EXISTS operator only work with subquery.

After the basic introduction of temp table, temp variable, subquery, derived table and CTE now we read the difference between all these.

Difference between subquery and derived table:

Subquery	Derived
Subqueries must be enclosed within parentheses.	Derived table must be enclosed within parentheses and table name must be provided.
Subquery can have only one column.	Derived table can have one or more column.
Subquery mainly use in where clause.	Derived table used in from clause.

Difference b/w Temp table and Temp variable:

Temp Table	TempVariable
Scope of Temp Table is wider the temp variables of. Local temporary tables are temporary tables that are available only to the session that created them and Global temporary tables are temporary tables that are available to all sessions and all users.	Scope of the Temp variables are limited up to current batch and current stored procedure.
Temp tables can be create using Create Table and Select Into commands	Temp variables only through Declare command can’t be created using select into command.
Temp tables can be drop through Drop Command.	We can not drop a Temp variables but we can use truncate command for temp variables.
Name of Temp table can have maximum 116 characters.	Name of a temp variables can have MAXIMUM 128 characters.
Temp table support foreign key concept.	Temp variable doesn’t support foreign key.
Temp tables support transaction management.	Temp variables doesn’t support transaction management. Rollback not work for temp variables.
Temp tables can easily handle large amount of data.	Temp variables are suitable with small amount of data.

Difference b/w CTE and Derived table:

CTE	Derived Table
A CTE can be referenced multiple times in the same query. So CTE can use in recursive query.	Derived table can’t referenced multiple times. Derived table can’t use in recursive queries.
CTE are better structured compare to Derived table.	Derived table’s structure is not good as CTE.

Today we read about the temporary storage mechanisms in SQL Server and also read the difference. I hope you liked this article.

Thanks for reading the article.

Read more articles on SQL Server:

• SQL Server Query Optimization - Day 1 (Basics)
• Basics Of Database Administration In SQL Server: Part 7