Difference Between Temp Table, Temp Variable And CTE In SQL Server

 

ntroduction

In SQL Server, there are many options to store the data temporarily, which are Temp Table, Table variable, and CTE(Common Table Expressions). In this article, we are going to learn about Temp Table, Table variable, and CTE in SQL Server.

Temp Table (Temporary Table)

• Temp tables are created in the runtime and these tables are physically created in the tempdb database.
• Temp tables are similar to normal tables and also have constraints, keys, indexes, etc. We can perform all operations in the temp table like a normal table.
• The name of the temp table can have a maximum of 116 characters.
• The structure of temp table can be altered after creating it.
• There are below types of temp tables 
  • Local Temp Table
  • Global Temp Table

Local Temp Table

• Local temp tables are only available to the current session that created them. The local temp tables are automatically deleted when the session is closed.
  • Example: Let's consider we are creating the temp table using query window. The temp table will only be available till query window is opened. If you close the current query window, then the temp table will be deleted. We can't access that temp table with a new query window, it will throw the error.    
• If the session that we are working on has subsequent nested sessions then, the temp tables will be visible in the nested sessions.
• The local temp table name is started with a single hash ("#").

create table #Student
(
    Id int,
    Name nvarchar(50),
    Address nvarchar(150)
)
go
insert into #Student values ( 1, 'Test','Tamil Nadu');
go
select * from #Student;

SQL

When to use Local Temp Table?

• If the size of the temporary data is huge (more than 100 rows).
• When the user or connection which creates them alone can use it.
• If you need to create indexes or apply read lock, then go to temp table.
• You cannot use temp tables in User Defined Functions (UDF).

Global Temp Table

• Global temporary tables are temporary tables that are available for all sessions and all users. These can be created by any SQL Server connection user and these tables are automatically deleted when all the SQL Server connections are closed. 
• The global temp table name is started with double hash ("##").

create table ##Student
(
    Id int,
    Name nvarchar(50),
    Address nvarchar(150)
)
go
insert into ##Student values ( 1, 'Test','Tamil Nadu');
go
select * from ##Student;

SQL

When to use Global Temp Table?

• The global temp table has the same advantages over the local temp table with the feature of multiple users or sessions or connections should access to the same table.

Temp Variable

• Temp variable is similar to temp table to use holding the data temporarily. 
• Table variable is a special kind of data type and is used to store the result set. 
• The scope of temp variable is limited to the current batch and current Stored Procedure. It will delete once comes out the batch (Ex. Stored Procedure).
• We can create a primary key, identity in the temp variable.
• We cannot create non-clustered index. It can only have indexes that are automatically created with PRIMARY KEY and UNIQUE constraints as part of the creation.
• Temp Variable is also created in the tempdb database but not the memory.
• The Temp variable name is started with "@".
• Temp Variables are created using a "DECLARE" statement.

  Declare @Student table
  (
      Id int,
      Name nvarchar(50),
      Address nvarchar(150)
  );
  -- Method 1
  insert into @Student select Id, Name, Address from Student Where id > 150;
  -- Method 2
  insert into @Student values (1000, "Test", "Test Address");
  
  select * from @Student;

  SQL
• We can use "insert into" statement for temp variable to store the values (Refer above examples)
• We cannot use "Select * into"  statement for temp variable to store the values. First, we have to create a temp variable and insert the values into it.
• The name of the temp variable can have a maximum of 128 characters.
• Table variables can be passed as a parameter to functions and stored procedures.

When to use Table Variable?

• To store the temporary data in user-defined functions (UDF), stored procedures, and query batches.
• If the volume of data is less, say less than 100 rows. 
• If you don’t want to alter the table structure after creating it.

CTE (Common Table Expression)

• It is used to hold the temporary result set or result of complex sub-query.
• The scope of CTE is limited to the current query.
• CTE improves readability and ease in the maintenance of complex queries and sub-queries. 
• CTE is created by using "WITH" statement and begin with a semicolon (;).

  ;With StudentCTE
  AS
  (
      SELECT stu.Id, stu.Name, stu.Age, Addr.Address from Student stu
      INNER JOIN Address Addr ON stu.Id = Addr.Id
  )
  SELECT * FROM StudentCTE --Using CTE
  WHERE StudentCTE.Age > 50
  ORDER BY StudentCTE.Id

  SQL
• There are two types of Common Table Expression 
  • Non-Recursive CTE  - It does not have any reference to itself in the CTE definition.
  • Recursive CTE - When a CTE has reference in itself, then it’s called recursive CTE.

When to use CTE?

• This is used to store the result of a complex subquery for further use.
• This is also used to create a recursive query.