What are Table Variables and Temporary Tables in SQL ?

Introduction

There are two ways to create a temporary table to hold data.

1. Table Variables
2. Temporary Tables

While both can be used to store temporary data, there are still some key differences between them.

Table Variables in SQL

A table variable is a type of variable that can store a set of data like a table. It is similar to a temporary table; it is stored in memory, which makes it faster and more efficient for small to medium-sized data sets. Table variables are declared and used in a similar way to regular variables. This creates a table variable called @TableVariable with columns: ID, Name, Age, Email, and ContactNo with their respective data types.

You can then insert data into the table variable using the INSERT INTO statement.

DECLARE @TableVariable TABLE (
ID int,
Name varchar(50),
Age int,
Email varchar(50),
ContactNo varchar(50)
);

INSERT INTO @TableVariable (ID, Name, Age,Email,ContactNo)
VALUES (1, 'RAJ', 30,'[email protected]','2019160263'), (2, 'Dinesh', 25,'[email protected]','2019160263'), (3, 'Ramesh', 40,'[email protected]','2019160263');

select * from @TableVariable

Once the data is stored in the table variable, you can use it in various ways, such as joining it with other tables, filtering the data, or returning it from a stored procedure or function.

One important thing to note about table variables is that they have a limited scope and lifetime. They are only visible within the current batch, stored procedure or function in which it is declared, and they are destroyed automatically when the batch or process completes.

This means you cannot use a table variable outside of the scope where it was declared. They cannot be explicitly dropped, as they are automatically cleaned up when the batch, stored procedure or function ends.

Temporary Tables in SQL

Temporary tables are tables created and used for a specific session or transaction in a database. They are similar to regular tables in that they have columns and data types and can be populated with data using SQL commands. Temporary tables are stored in a temporary database and are automatically dropped when the session or transaction ends.

There are two types of temporary tables in SQL Server,

Local temporary tables in SQL

These tables are only visible within the current session and are automatically dropped when the session ends. Local temporary tables are created using the CREATE TABLE #tableName statement, where #tableName is the name of the temporary table.

CREATE TABLE #TempEmployees (
ID int,
Name varchar(50),
Age int,
Email varchar(50),
ContactNo varchar(50)
);

INSERT INTO #TempEmployees (ID, Name, Age,Email,ContactNo)
VALUES (1, 'RAJ', 30,'[email protected]','2019160263'), (2, 'Dinesh', 25,'[email protected]','2019160263'), (3, 'Ramesh', 40,'[email protected]','2019160263');

SELECT * FROM #TempEmployees;

This creates a local temporary table called #TempEmployees with three columns, inserts some data into the table, and then selects all the can. Once the session ends, the temporary table is automatically dropped.

Global temporary tables in SQL

These tables are visible to all sessions and are dropped when the last session referencing the table ends. Global temporary tables are created using the CREATE TABLE ##tableName statement, where ##tableName is the name of the global temporary table.

CREATE TABLE ##TempEmployees (
ID int,
Name varchar(50),
Age int,
Email varchar(50),
ContactNo varchar(50)
);

INSERT INTO ##TempEmployees (ID, Name, Age,Email,ContactNo)
VALUES (1, 'RAJ', 30,'[email protected]','2019160263'), (2, 'Dinesh', 25,'[email protected]','2019160263'), (3, 'Ramesh', 40,'[email protected]','2019160263');

SELECT * FROM ##TempEmployees;

Stored in the tempdb system database and can be accessed across sessions. This creates a global temporary table called ##TempEmployees, inserts some data into the table, and then selects all the rows. The table will be available to all sessions until the last session referencing the table ends. Once the previous session ends, the global temporary table is automatically dropped.

It must be explicitly dropped using the DROP TABLE statement when no longer needed. It can be indexed and optimized for better performance but may take longer to create and use than table variables. When deciding whether to use a table variable or a temporary table, you should consider the size of the data set, the complexity of the queries that will be run on the data, and the duration of the storage required.

Examples of Table Variables,

  CREATE TABLE Student (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT,
  grade VARCHAR(2)
);

 
INSERT INTO Student (id, name, age, grade)
VALUES 
  (1, 'Rahul', 18, 'A'),
  (2, 'Sandeep', 19, 'B'),
  (3, 'Vikas', 20, 'A'),
  (4, 'Pankaj', 19, 'C'),
  (5, 'Poonam', 18, 'B'),
  (6, 'Raj', 21, 'A'),
  (7, 'Tushar', 20, 'B'),
  (8, 'Ankit', 18, 'A'),
  (9, 'Daniel', 19, 'C'),
  (10, 'Jennifer', 20, 'A');


CREATE PROCEDURE GetStudentsWithGradeA
AS
BEGIN
  DECLARE @students TABLE (
    id INT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(2)
  );

  INSERT INTO @students (id, name, age, grade)
  SELECT id, name, age, grade FROM Student WHERE grade = 'A';

  SELECT * FROM @students;
END


EXEC GetStudentsWithGradeA;


CREATE PROCEDURE UPDATESTUDENTWITHNEWAGE
@AGE INT 
AS
BEGIN
   DECLARE @STUDENTS TABLE (
    ID INT,
    NAME VARCHAR(50),
    AGE INT,
    GRADE VARCHAR(2),
	ELIGIBLEFORVOTING VARCHAR(50),
	PERCENTAGEGRADE VARCHAR(50)
  );

  INSERT INTO @STUDENTS (ID, NAME, AGE, GRADE)
  SELECT ID, NAME, AGE, GRADE FROM STUDENT;
  UPDATE @STUDENTS
  SET 
  
  ELIGIBLEFORVOTING = 'Y'
  WHERE @AGE > 18;

  UPDATE @STUDENTS
  SET 
  PERCENTAGEGRADE = 
    CASE
        WHEN GRADE='A' THEN '70%'
		WHEN GRADE='B' THEN '60%'

        ELSE '30%'
    END


  SELECT * FROM @STUDENTS
END

  CREATE TABLE Student (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT,
  grade VARCHAR(2)
);

 
INSERT INTO Student (id, name, age, grade)
VALUES 
  (1, 'Rahul', 18, 'A'),
  (2, 'Sandeep', 19, 'B'),
  (3, 'Vikas', 20, 'A'),
  (4, 'Pankaj', 19, 'C'),
  (5, 'Poonam', 18, 'B'),
  (6, 'Raj', 21, 'A'),
  (7, 'Tushar', 20, 'B'),
  (8, 'Ankit', 18, 'A'),
  (9, 'Daniel', 19, 'C'),
  (10, 'Jennifer', 20, 'A');


CREATE PROCEDURE TmpGetStudentsWithGradeA
AS
BEGIN

  CREATE  TABLE #students (
    id INT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(2)
  );

  INSERT INTO #students (id, name, age, grade)
  SELECT id, name, age, grade FROM Student WHERE grade = 'A';

  SELECT * FROM #students;
END


EXEC TmpGetStudentsWithGradeA;


CREATE PROCEDURE TmpUPDATESTUDENTWITHNEWAGE
@AGE INT 
AS
BEGIN
   CREATE  TABLE #students (
    ID INT,
    NAME VARCHAR(50),
    AGE INT,
    GRADE VARCHAR(2),
	ELIGIBLEFORVOTING VARCHAR(50),
	PERCENTAGEGRADE VARCHAR(50)
  );

  INSERT INTO #students (ID, NAME, AGE, GRADE)
  SELECT ID, NAME, AGE, GRADE FROM STUDENT;
  UPDATE #students
  SET 
  
  ELIGIBLEFORVOTING = 'Y'
  WHERE @AGE > 18;

  UPDATE #students
  SET 
  PERCENTAGEGRADE = 
    CASE
        WHEN GRADE='A' THEN '70%'
		WHEN GRADE='B' THEN '60%'

        ELSE '30%'
    END


  SELECT * FROM #students
END



Exec TmpUPDATESTUDENTWITHNEWAGE 40

while temporary tables and table variables can produce the same output, they differ in scope, storage location, capabilities, and performance characteristics. They should be chosen based on the specific requirements of the task at hand.

Some differences between Temporary Tables and Table Variables

• Temporary tables are created in the TempDB database and stored until explicitly dropped until the session that created them is closed. They can be created using the CREATE TABLE st and accessed by multiple sessions. Temporary tables can also have indexes and constraints added to them.
• Table variables, on the other hand, are created in memory and are only available within the scope of the batch, stored procedure, or function that created them. They are automatically dropped when the batch, stored procedure or function ends. Table variables cannot have indexes or constraints addRegardingn terms of performance; table variables are generally faster for smaller amounts of data. In contrast, temporary tables are better for larger amounts of data. This is because table variables are created in memory and do not require disk I/O. Still, they also do not have the benefit of index and constraint optimization that temporary tables have.

--Two ways to check Temporary table
-- To check all Temporary table
USE tempdb;
EXEC sp_tables '#%';



-- To check all Temporary table
SELECT name
FROM tempdb.sys.tables
WHERE name LIKE '#%';


-- Note Temporaray Table will Show only session if the Session is not Expired

Note

if the data set is small and the queries are not too complex, a table variable may be a good choice. If the data set is larger and more complex, or if you need to store the data across multiple sessions, a temporary table may be better.

Summary

This article explains what temporary tables are in SQL Server and the different ways to create them in SQL Server with code examples. Thank you for reading, and I hope this blog post has helped provide you with a better understanding of  Table Variables and Temporary Tables.