Post
Ask Question

Table(s): Temp Table, Table Variable and CTE (Common Table Expression)

Note: this article is published on 12/26/2023.

This series of articles is about SQL Tables.

Introduction

In SQL Server, as I know, we have the following table type-related concepts:

  • Table
  • Temp Table
    • Local
    • Global
  • Table Variable
  • Common Table Express (CTE)
  • Table as an Input Parameters

Table is an SQL Data Type that has been used and discussed a lot and will not be discussed in this article, while Table as an Input Parameters has been discussed in the article, Table As Input Parameters For Stored Procedure, and will not be discussed in this article.

We will give the definitions of each table-like item and discuss the major features.

SQL Data Types

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.

  • System data types --- define all the types of data that can be used with SQL Server
  • User-defined data types ---

We can make a user-defined type, such as a Table type, like this.

CREATE TYPE Employee AS TABLE
(
    Emp_Id int NOT NULL,
    EmployeeName nvarchar(MAX),
    EmpSalary varchar(50),
    StateId varchar(50),
    CityId varchar(50)
)

Temp Table

Temporary Tables: are most likely Permanent Tables. Temporary Tables are Created in TempDB and are automatically deleted as soon as the last connection is terminated. Temporary Tables help us to store and process intermediate results. Temporary tables are very useful when we need to store temporary data. The Syntax to create a Temporary Table is given below:

Local Temporary Table: A Local Temp Table is available only for the session that has created it. It is automatically dropped (deleted) when the connection that has been created is closed. To create a Local Temporary Table, a Single “#” is used as the prefix of a table name.

Also, the user can drop this temporary table by using the “DROP TABLE #EmpDetails” query. There will be Random Numbers are appended to the Name of the Table Name. If the Temporary Table is created inside the stored procedure, it gets dropped automatically upon the completion of the stored procedure execution.

CREATE TABLE #Employee
(
    Emp_Id int NOT NULL,
    EmployeeName nvarchar(MAX),
    EmpSalary varchar(50),
    StateId varchar(50),
    CityId varchar(50)
)

Global Temporary Table: To create a Global Temporary Table, add the “##” symbol before the table name.

Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. Global Table Name must have a Unique Table Name. There will be no random Numbers suffixed at the end of the Table Name.

CREATE TABLE ##Employee
(
    Emp_Id int NOT NULL,
    EmployeeName nvarchar(MAX),
    EmpSalary varchar(50),
    StateId varchar(50),
    CityId varchar(50)
)

SELECT INTO TEMP TABLE Syntax: A temporary Table can be created by syntax. Select Into

SELECT * INTO #Employee 
FROM Employee

Note. When trying to refresh a temp table, or say, insert records into an empty tem table, you can use Drop Statement

DROP TABLE IF EXISTS #Employee

Main Features of Temp Variable [reg]:

  1. The temp tables are created in the tempdb database;
  2. Drop temp tables quickly: As possible, we need to drop temporary tables explicitly, and we should not wait for automatic deletion when the connection is ended. So that we can free up the tempdb resources more quickly
  3. you can use indexes for the Temp Table.

Temporary tables are subject to the following restrictions [ref];

  1. A temporary table cannot be used within a database procedure. It can, however, be used as an input parameter to a database procedure if it is the only parameter and the procedure is not row-producing --- not true, the true statement should be "both temp table and Table Variable are created in tempdb database", see D.2: Space Scale in Table As Input Parameters For Stored Procedure (08/01/2024).
  2. A temporary table cannot be used in a view definition.
  3. Integrities, constraints, or user-defined defaults cannot be created for temporary tables. (The with|not null and with|not default can be specified.)
  4. A temporary table cannot be modified to use a different page size.
  5. Repeat queries referencing temporary tables cannot be shared between sessions.
  6. A temporary table cannot be partitioned.

Table Variable

A table variable is a type of local variable that is used to store data temporarily, similar to the temp table in SQL Server. Tempdb database is used to store table variables.

To declare a table variable, start the DECLARE statement. The name of the table variable must start with at(@) sign. The TABLE keyword defines the used variable as a table variable. After the TABLE keyword, define column names and datatypes of the table variable in SQL Server.

DECLARE @Employee TABLE
(
    Emp_Id int NOT NULL,
    EmployeeName nvarchar(MAX),
    EmpSalary varchar(50),
    StateId varchar(50),
    CityId varchar(50)
)

Note. When trying to refresh a temp table or, say, insert records into an empty tem table, you can use Delete Statement.

Delete @Employee

Main Features of Table Variable [reg]

  1. Table variables allow us to create the following constraints:
    • Primary Key
    • Unique
    • Null
    • Check
  2. The structure of a table variable is always created in TempDB instead of in memory. [ref];
  3. TRUNCATE statement does not work for table variables;
  4. The table variable structure cannot be changed after it has been declared;
  5. The table variable does not allow the creation of an explicit index;
  6. The table variable in SQL Server should use an alias with the join statements --- not true (07/30/2024), such as
DECLARE @Department TABLE
(DepartmentID INT PRIMARY KEY,
DepName VARCHAR(40) UNIQUE)

INSERT INTO @Department VALUES(1,'Marketing')
INSERT INTO @Department VALUES(2,'Finance')
INSERT INTO @Department VALUES(3,'Operations ')

DECLARE @Employee TABLE
(EmployeeID INT PRIMARY KEY IDENTITY(1,1),
EmployeeName VARCHAR(40),
DepartmentID VARCHAR(40))

INSERT INTO @Employee VALUES('Jodie Holloway','1')
INSERT INTO @Employee VALUES('Victoria Lyons','2')
INSERT INTO @Employee VALUES('Callum Lee','3')

select * from @Department Dep inner join @Employee Emp
on Dep.DepartmentID = Emp.DepartmentID

select * from @Department inner join @Employee
on [@Department].DepartmentID = [@Employee].DepartmentID

Both with alias or without are working well

Common Table Expressions (CTE)

The Common Table Expressions (CTE) were introduced into standard SQL in order to simplify various classes of SQL Queries for which a derived table was just unsuitable. CTE was introduced in SQL Server 2005. The common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE view as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

WITH Employee_CTE 
(
    Emp_Id,
    EmployeeName,
    EmpSalary,
    StateId,
    CityId
)
AS
(SELECT
    Emp_Id,
    EmployeeName,
    EmpSalary,
    StateId,
    CityId
 FROM Employee)

Table as an Input Parameters (Table Valued Parameters)

Table-valued parameters were introduced to SQL Server in 2008. Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data.

CREATE PROCEDUREUpdateEmployees
    (@Employee Employee READONLY)
AS
BEGIN
	SET NOCOUNT ON
	UPDATE dbo.Employees
    SET Employees.EmployeeName = emp.EmployeeName 
    FROM dbo.Employees INNER JOIN @Employee AS emp
    ON dbo.Employees.Emp_id = emp.Emp_id ;
END

where Employee is a Table Type defined in the SQL Data Types section above.

Note. A detailed discussion about this topic can be found in my other article, Table As Input Parameters For Stored Procedure.

References


Similar Articles