Difference between CTE and Temp Table and Table Variable

Difference between CTE and Temp Table and Table Variable

14 Mar 2024
Intermediate
595K Views
13 min read

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.

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.

    Local Temp 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 Temp 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

FeatureCTETemp TableTable Variable
DefinitionNamed temporary result set defined within a query using WITH clauseThe physical table stored in tempdbAn in-memory structure defined within a batch or procedure
ScopeCurrent querySession or global (depending on the type)Current batch or stored procedure
PersistencyExists only for the duration of the queryPersistent until explicitly dropped or a session endsDeleted automatically when the batch or procedure completes
PerformanceCan be efficient for complex queries, especially with recursive logicOften faster for large datasets and multiple operationsCan be very fast for small datasets and simple operations
FeaturesImproves readability and maintainability, can be reused within a querySupports indexes, constraints, and statisticsCannot have indexes or SQL constraints, and no statistics are maintained
SuitabilityHierarchical or recursive queries, improving code readability, complex logic within a single queryLarge datasets, multiple operations, frequent use across queriesSmall datasets, simple operations within a batch or procedure, passing data between procedures
Additional NotesCannot be used in SQL functions, cannot be accessed directly outside the defining queryMay impact performance if heavily used or large datasets involved can be affected by transaction rollbacksLimited data type support, can be limited by tempdb size

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.

Read More:
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.

FAQs

Q1. What's the distinction between a CTE temp table and a table variable?

CTEs are temporary result sets defined within a query, temporary tables are real tables stored in the tempdb database, and table variables are variables with a table-like structure.

Q2. What is the distinction between table variable memory and temp table memory?

Temporary tables are physical objects with statistics and indexes that are kept in the tempdb database. Table variables are memory-stored logical objects with no statistics or indexes.

Q3. What is the distinction between temp table and variable table performance?

In terms of performance, table variables are useful with tiny quantities of data (such as a few rows). Otherwise, when combing through vast amounts of data, a SQL Server temp table comes in handy.

Q4. What is the difference between #temp and ## temp in SQL?

The only difference is that we must use the "#" sign for the names of the local temp tables. We use two such indications in a row for global temporary tables: "##". Temporary tables are classified into two types: Tables that are only visible to the session that generated them.

Q5. What is the distinction between a temp table temp variable and a CTE in SQL Server?

Temp Tables are physically formed in the Tempdb database. These tables function like normal tables and can also contain constraints, such as index-like normal tables. CTE is a named temporary result set that is used to manipulate data from complex sub-queries. This exists to define the scope of a statement.

Take our free sqlserver skill challenge to evaluate your skill

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

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Courses
  • 750+ Hands-On Labs
  • 300+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A
  • 10+ Real-world Projects
  • Career Coaching
  • Email Support
Upto 66% OFF
KNOW MORE..

To get full access to all courses

Accept cookies & close this