Difference between CTE and Temp Table and Table Variable

Difference between CTE and Temp Table and Table Variable: An Overview

In the realm of SQL Server, we have various options for storing data temporarily. Delve deeper into these choices through an insightful SQL Server Course to enhance your understanding. Temp Table, Table variable, and CTE have commonly used ways for storing temporary data. In this SQL Server Tutorial, you will learn about the main differences between the Temp Table, Table variable, and CTE.

CTE - Common Table Expressions

CTE stands for Common Table Expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of a complex sub-query. Unlike the temporary table, its life is limited to the current query. It is defined by using the WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with a semicolon.

A subquery without CTE is given below

SELECT * FROM (
 SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr
 Inner join Employee Emp on Emp.EID = Addr.EID) Temp
WHERE Temp.Age > 50
ORDER BY Temp.NAME

The query initially combines address information from the "Address" field with employee names and ages from the "Employee" table, resulting in a temporary result set named "Temp." It next reduces the results to only include employees over the age of 50. Finally, the remaining results are sorted alphabetically by employee name.

By using CTE above query can be re-written as follows

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME

The query begins by defining a "CTE1" Common Table Expression (CTE). A temporary table that keeps the combined address, name, & age information from the "Address" & "Employee" databases.It then pulls all data from "CTE1" but only saves records that are older than 50.Finally, it alphabetically sorts the filtered results by employee name.

Read More - Top 50 DBMS Interview Questions and Answers

When to use CTE?

1. This is used to save a complex subquery's result for later use.
2. Moreover, a recursive query can be made with this.

Advantages of CTE

• Complex query maintenance is made easier and readability is enhanced by the use of CTE.
• The query can be broken down into discrete, logical, and basic building parts that can be utilized to construct increasingly sophisticated CTEs until the desired result set is produced.
• Functions, stored procedures, triggers, and even views can define CTE.
• A CTE can be used as a Table or a View and can select, insert, update, and delete data after it has been defined.

Temporary Tables

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside the Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

1. Local Temp Table

   Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. The local temporary table name is started with a single hash ("#") sign.

   CREATE TABLE #LocalTemp
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into #LocalTemp values ( 1, 'Shailendra','Noida');
   GO
   Select * from #LocalTemp
   

   It initially creates a temporary table called "#LocalTemp" with three columns: UserID (for integer values), Name (for text names with up to 50 characters), and Address (for text addresses with up to 150 characters). It then adds a single row of data into the temporary table, with the values 1 for the UserID, 'Shailendra' for the Name, and 'Noida' for the Address columns. Finally, it fetches and shows all of the data saved in the temporary table.

   

   The scope of the Local temp table exists to the current session of the current user means to the current query window. If you close the current query window or open a new query window and try to find the above-created temp table, it will give you the error.

2. Global Temp Table

   Global temp tables are available to all SQL Server sessions or connections (which means all the users). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. The global temporary table name is started with a double hash ("##") sign.

   CREATE TABLE ##GlobalTemp
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
   GO
   Select * from ##GlobalTemp
    

   It starts by generating a global temporary table called "##GlobalTemp" that has three columns: UserID (integer), Name (text up to 50 characters), & Address (text up to 150 characters). Global temporary tables can be accessed from many sessions inside the same database instance. It then adds a single row of data into the table, with the values 1 for the UserID, 'Shailendra' for the Name, and 'Noida' for the Address columns. Finally, it fetches and shows all of the data contained in the global temporary table.

   

   Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connections.

Advantages of Temporary Tables

• By isolating temporary data, they prevent problems with other user sessions or permanent tables.
• Query performance is enhanced by temporarily storing intermediate results in tables.
• Complex queries are made simpler and easier to comprehend and manage with the use of temporary tables.
• Temporary tables allow for the performance of CRUD operations together with optimization options.
• Temporary tables, which are private to the session, speed up execution by lowering logging and locking overhead.
• They offer a secure setting for query and data transformation testing.
• While performance is improved by temporary tables, table variables might provide even better results.

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of the batch. This is also created in the tempdb database but not the memory. This also allows you to create a primary key, identity at the time of Table variable declaration but not a non-clustered index.

 GO
 DECLARE @TProduct TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 Qty INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID,Qty)
 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
 --Select data
 Select * from @TProduct
 
 --Next batch
 GO
 Select * from @TProduct --gives error in next batch 

It declares a table variable called "@TProduct" to store product data temporarily. It adds unique product IDs and amounts from the "ProductsSales" database, sorted by product ID, into the table variable. It successfully retrieves and displays the table variable's data. The next batch's attempt to pick from the table variable fails because table variables only exist within the batch in which they are declared. They are not persistent between batches.

Advantages of Table Variable

• Keeps naming conflicts to a minimum and enhances code readability within the given context.
• Improves concurrency and performance with less locking overhead than temporary tables.
• Decreases overhead by avoiding pointless statistics updates.
• Better query strategies result from treating them as compile-time constants.
• Stored in memory, suited for small to medium-sized result sets, with faster access times.
• Maintains consistency of data in the context of transactions.
• Deallocated automatically when out of scope, removing the need for specific cleanup code.
• Declared and utilized in T-SQL code with ease.

Difference between CTE and Temp Table and Table Variable

Note

1. Temp Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, an index like normal tables.

2. CTE is a named temporary result set that is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than the Tempdb database. You cannot create an index on CTE.

3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of a batch. This is also created in the tempdb database but not the memory.

• Top SQL Interview Questions and Answers

Summary

CTEs, temp tables, & table variables all have temporary data in SQL Server, but they differ in scope, performance, and utilization. CTEs are good for improving complex queries and recursion, whereas temp tables provide flexibility in the form of constraints and indexes. Table variables function similarly to batch-specific data containers. Choose the tool based on the complexity of your query, performance requirements, and data persistence requirements.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE