Temporary tables, often known as temp tables, are widely used by database managers and developers. They function similarly to ordinary tables and are kept in the tempDB database, enabling you to choose, add, and remove data as needed. In the event that they are produced during a stored procedure, they will be removed once the operation is finished.

What does a SQL Server temporary table mean?
In SQL, a temporary table is a database table that is only temporarily present on the database server. For a set amount of time, a temporary table retains some of the data from a regular table.

Temporary tables are useful when you need to regularly work with a small subset of the many records in a table. Sometimes it is not necessary to filter the data several times in order to obtain the subset; instead, you can filter the data only once and save it in a temporary table.

Temporary tables and permanent tables are almost the same. They are created in TempDB and deleted right away after the last connection to the query window that created the table is closed. Temporary Tables can be used to process and store interim findings. Temporary tables are used when it is necessary to store data temporarily.

How to Create a Temporary SQL Table?
CREATE TABLE #tmpEmp
(
  Id INT,
  Name NVARCHAR(50),
  Rank INT
)

Types of Temporary Tables in SQL
There are a couple of temporary tables in SQL.

• Local Temporary Tables
• Global Temporary Tables

Local Temporary Tables
TempDB contains local temporary table storage. They are destroyed automatically at the conclusion of the operation or session, and they are only available to the one who created them.

For instance, after a local temporary table called #tmpEmp is created, the user's ability to manipulate the table is limited until the query window's final connection is closed. They can be recognized by the prefix #, such as #table name, and the same temporary table can be created with the same name in many windows.

A local temporary table can be created with the CREATE TABLE command, where the table name is prefixed with a single number sign (#table name).

Syntax
The following is the syntax in SQL Server (Transact-SQL) for making a LOCAL TEMPORARY TABLE.
CREATE TABLE #tablename
(
  column1 datatype [ NULL | NOT NULL ],
    column2 datatype [ NULL | NOT NULL ],
    column3 datatype [ NULL | NOT NULL ],
    ...
    columnn datatype [ NULL | NOT NULL ]
);

Let’s insert some data entry in a temporary table.

INSERT INTO #tmpEmp ([Name], [Rank])
VALUES ('Peter Scott', 196)

Let’s check the results, whether it’s stored or not.
SELECT Id, [Name], [Rank] FROM #tmpEmp

Where do I store the temporary table on the SQL Server?

Another way to create a temporary table in SQL is with the SELECT INTO statement. For the illustration, go through the following things.
SELECT Id, [Name], [Rank]
INTO #tmpEmp1
FROM #tmpEmp

Temporary tables are useful when you need to regularly work with a small subset of the many records in a table. Sometimes it is not necessary to filter the data several times in order to obtain the subset; instead, you can filter the data only once and save it in a temporary table.

Temporary tables and permanent tables are almost the same. They are created in TempDB and deleted right away after the last connection to the query window that created the table is closed. Temporary Tables can be used to process and store interim findings. Temporary tables are used when it is necessary to store data temporarily.

Global Temporary Table in SQL Server
Additionally, they are kept in tempDB. These tables belong to the category of transient tables that are concurrently accessible to all users and sessions. When the final session using the temporary table concludes, they are automatically removed. These tables do not exist in the system catalogs and are not persistent.

A global temporary table is created with the CREATE TABLE command, and the table name is preceded by a double number sign (##table name).

Syntax
The following is the syntax in SQL Server (Transact-SQL) for making a global temporary table.
CREATE TABLE ##tablename
(
  column1 datatype [ NULL | NOT NULL ],
    column2 datatype [ NULL | NOT NULL ],
    column3 datatype [ NULL | NOT NULL ],
    ...
    columnn datatype [ NULL | NOT NULL ]
);

Create a global temporary table.

CREATE TABLE ##tmpEmployee
(
  Id INT NOT NULL IDENTITY (1, 1),
  [Name] NVARCHAR(50),
  [Rank] INT
)

Let’s insert some data entry in a global temporary table.

INSERT INTO ##tmpEmployee ([Name], [Rank])
VALUES ('Peter', 196)
, ('Daniel', 1211)
, ('Maria', 1250)
, ('Laura', 1280)

Let’s check the results, whether it’s stored or not.
SELECT Id, [Name], [Rank] FROM ##tmpEmployee

Where do I store the temporary table on the SQL Server?

Delete Temporary and Global Temporary SQL Table
When possible, we should directly remove temporary tables rather than waiting for them to be deleted automatically when the connection is closed. in order to expeditiously release the temp resources.

Syntax
DROP TABLE TableName

As an illustration,
DROP TABLE #tmpEmp, #tmpEmp1, ##tmpEmployee

Let's check in the Temporary Tables from SQL Server.

HostForLIFEASP.NET SQL Server 2022 Hosting

This succinct article will help you understand the fundamentals of three-tier architecture in database management systems (DBMS). Discover the layers: data management, application, and display. Each has a specific function in coordinating smooth data exchanges. Discover the benefits, tenets, and real-world applications of this architectural approach, which will enable you to create and implement dependable and expandable database solutions.

Outside Layer
In a database management system's three-tier architecture, the external level, sometimes referred to as the user interface layer, is the highest tier (DBMS). By presenting data in an approachable and comprehensible style, it acts as the entry point for end users to engage with the system. Forms, reports, dashboards, and graphical user interfaces that are customized for particular user roles and preferences are all included in this layer. Users may rapidly access, retrieve, update, and edit data thanks to the external level's streamlined and intuitive interface, which abstracts the intricacies of the underlying database structure.

Conceptual Depth
In the three-tier design of a database management system (DBMS), the conceptual level is the intermediary layer, situated between the external and physical levels. It abstracts the underlying physical storage information from the end users and applications that interact with the system, representing the logical perspective of the database. At this stage, implementation details are not as important as specifying the general structure, organization, and relationships of the data within the database. Entity-relationship diagrams (ERDs) and other conceptual data models, which depict the entities, characteristics, and relationships in the database schema, are commonly used to represent the conceptual level. Because of its capacity to support data independence, this abstraction improves flexibility and maintainability by enabling modifications to the database structure without impacting the external applications.

Physical Level
At the basis of a database management system's (DBMS) three-tier design, the physical level is in charge of overseeing the real data storage and retrieval on the physical storage devices. This tier communicates directly with the disks, RAM, and storage devices that make up the hardware (Data Store). Data is arranged and stored physically using techniques like indexing, segmentation, and storage optimization that are designed for effective access and retrieval. To maintain data integrity, security, and performance, this tier also includes features like disk management techniques, data compression, and encryption. By converting logical data structures into physical storage, the physical level works directly with low-level storage methods, in contrast to the conceptual and external levels, which abstract away from specifics of physical implementation.

These layers communicate with one another in an organized manner, with each layer carrying out particular duties and transferring information or requests to the subsequent layer as needed. Database systems can be designed and managed with modularity, scalability, and maintainability because to this division of responsibilities.

In summary
The conceptual layer establishes logical data structures, the physical layer oversees actual data storage, and the external layer offers user-friendly interfaces in a three-tier architecture. By working together, they produce a coherent framework for effective data processing and administration that improves database systems' scalability, maintainability, and security.

HostForLIFEASP.NET SQL Server 2022 Hosting

Table-valued parameters are similar to parameter arrays in that they eliminate the need to build a temporary table or employ numerous parameters by enabling the sending of multiple rows of data to a Transact-SQL statement or routine, such as a stored procedure or function. This article explains how to use a Microsoft SQL Server table-valued argument in a stored procedure.

User-Defined Table Types are tables designed to hold temporary data and are used to declare table-valued parameters. Thus, you can make a Type Table and send it as a parameter for a process that requires, for instance, to receive a list of items. For demonstration purposes, I created a table of products that will be used in the following examples. This is the structure of the Products table.

CREATE TABLE Products (
    Id INT NOT NULL,
    Name NVARCHAR(100) NULL,
    Description NVARCHAR(200) NULL,
    CreatedDate DATETIME2 NOT NULL CONSTRAINT [DF_Products_CreatedDate] DEFAULT GETUTCDATE(),
    CreatedBy NVARCHAR(150) NOT NULL,
    CONSTRAINT PK_Product PRIMARY KEY(Id)
 );

Adding Just One Product
Imagine a situation in which a user opens your app and needs to register just one item. And to do that, a process for adding this product to the database must exist. You must first develop a procedure to add a single product to the products database in order to accomplish that. The ID, Name, Description, and the user who created the product should be sent as parameters to this procedure.

CREATE PROCEDURE InsertProduct (
        @Id INT,
        @Name NVARCHAR(100),
        @Description NVARCHAR(200),
        @User NVARCHAR(150)
    )
AS
BEGIN
    INSERT INTO Products (
        Id,
        Name,
        Description,
        CreatedBy
    )
    VALUES (
          @Id,
          @Name,
          @Description,
          @User
    );
END

For testing this procedure, we can run some scripts adding aBEGIN TRANSACTIONwith aROLLBACKin the end (this is useful when testing to avoid needing to delete/change/revert the data on each test that is made), and inside of that, we can execute the statements to insert the products.

BEGIN TRANSACTION
SELECT * FROM Products;
EXEC InsertProduct 1, 'Galaxy S22', 'Smartphone Samsung Galaxy S22', 'Henrique';
EXEC InsertProduct 2, 'iPhone 14 Pro', 'Smartphone Apple iPhone 14 Pro', 'Henrique';
EXEC InsertProduct 3, 'iPhone 13 Pro', 'Smartphone Apple iPhone 13 Pro', 'Henrique';
SELECT * FROM Products;
ROLLBACK

On line 1, there is the BEGIN transaction statement, and this is to allow us to revert the changes at the end of the execution.
On line 3, we run aSELECTquery to check the data in the products table.
On lines 5 up to 7, we run the InsertProductprocedure to insert the products. Note that in order to insert three products, we needed to execute the procedure three times, once for each product.
On line 9, we run a new select query to check the data in the products table.
On line 11, there is the rollback statement, to revert the changes that were made.

This is the result.

Adding a Large Number of Products
Now imagine a situation where you receive a list of products to add to the product table, rather than just one product. In this instance, a Table Type parameter—which functions as a sort of array of products—should be included in the code. The properties that are listed in the products table should also be included in the type table's columns. For instance, the type table will include the columns Name and Description.

CREATE TYPE ProductType AS TABLE (
  Id INT NOT NULL,
  Name NVARCHAR(100) NULL,
  Description NVARCHAR(200) NULL,
  PRIMARY KEY(Id)
);

Once the Type Table is created, it’s possible to see it here.

The type table and the user entering the records will be the two arguments for the new method we'll be creating, InsertProducts (plural). The Insert Products process is as follows.

CREATE PROCEDURE InsertProducts (
    @Products ProductType READONLY,
    @User NVARCHAR(150)
  )
AS
BEGIN
  INSERT INTO Products (
    Id,
    Name,
    Description,
    CreatedBy
  )
  SELECT
    prd.Id,
    prd.Name,
    prd.Description,
    @User
  FROM @Products prd
END

• On line 2, there is the parameter@Productsof typeProductType, and it must have theREADONLYkeyword.
• On line 3, there is the parameter@CreatedByof typeNVARCHAR, which is for saving the name of the user who runs the procedure to insert products. Note: this second parameter is here only to demonstrate that even when a procedure has a type table as a parameter, is still possible to use more parameters of different types — in case you need to get the user who executed the SQL script, you can use theSYSTEM_USERin the SQL Script, instead of receiving the user as a parameter.
• On line 7, the INSERT statement begins.
• On line 13, there is the select query, which will read the data from the table type that was received as a parameter (@Products), and it will use the data to insert it into the product table.

Let’s test the procedure now. For that, let’s use the TRANSACTION with aROLLBACKin the end, as we did before, and for testing, we will add some data into the type table and execute the procedure by sending this type table as a parameter.

BEGIN TRANSACTION
  SELECT * FROM Products;
  DECLARE @Products ProductType;
  INSERT INTO @Products
  SELECT 1, 'Galaxy S22+', 'Smartphone Samsung Galaxy S22+'
  UNION ALL
  SELECT 2, 'iPhone 14 Pro', 'Smartphone Apple iPhone 14 Pro'
  UNION ALL
  SELECT 3, 'iPhone 13 Pro', 'Smartphone Apple iPhone 13 Pro';
  EXEC InsertProducts @Products, 'Henrique';
  SELECT * FROM Products;
ROLLBACK

• On line 1, a new transaction is started.
• On line 3, we first run select to check the data we have in the product table before running the procedure.
• On line 5, the variable of typeProductTypeis declared.
• On lines 7 up to 12, three records are inserted into the@Productsvariable.
• On line 14, the procedure to insert products is executed and receives as parameters the Type table variable (@Products) and a user ('Henrique').
• On line 16, a new selection, the Productstable is executed, and the three records are expected to be inserted into the table.
• On line 18, a rollback is executed to revert the changes.

This is the result.

Since this is a new table, it is to be expected that the initial select query returned no results. The three products were added to the product table using the second select query, which was run subsequent to the insert function. For the scenarios in which the product table contains records, let's run another test. Let's add additional data to the table to support that.

INSERT INTO Products (Id, Name, Description, CreatedBy)
  VALUES (1, 'Galaxy S21+', 'Smartphone Samsung Galaxy S21+', 'Henrique'),
          (2, 'Galaxy S22', 'Smartphone Samsung Galaxy S22', 'Henrique'),
    (3, 'Galaxy S22+', 'Smartphone Samsung Galaxy S22+', 'Henrique');

Now, let’s do another test, adding new records using the InsertProductsprocedure.

BEGIN TRANSACTION
  SELECT * FROM Products;
  DECLARE @Products ProductType;
  INSERT INTO @Products
  SELECT 4, 'iPhone 13 Pro', 'Smartphone Apple iPhone 13 Pro'
  UNION ALL
  SELECT 5, 'iPhone 14 Pro', 'Smartphone Apple iPhone 14 Pro';
  EXEC InsertProducts @Products, 'Henrique';
  SELECT * FROM Products;
ROLLBACK

• On line 3, the first SELECT will return the records that were previously added to the products table.
• On line 5, the variable of typeProductTypeis declared.
• On lines 7 up to 10, two products are added to theProductTypetable, which will be used as a parameter to the procedure.
• On line 12, the procedureInsertProductsis executed.
• On line 14, a second selects executed, to return the products.

This is the result.

The new records with Ids 4 and 5 were added to the product table as anticipated.

In summary
It is feasible to build a stored method or function that requires a list of data as a parameter by declaring User-Defined Table Types and employing Tabled-Valued Parameters. This makes it feasible to deliver a large amount of data with a single request rather than having to run the function numerous times (one for each data).

HostForLIFEASP.NET SQL Server 2022 Hosting

Data manipulation skills are critical for developers and database administrators in the realm of SQL Server databases. While there are many tools in SQL Server, temporary tables and table variables are particularly helpful for arranging and modifying data while the user is logged in. Temporary tables and table variables are examined in detail in this guide, along with examples that demonstrate how to utilize them and explain their differences.

SQL Temporary Tables
The TempDB system database is where temporary tables are created, and only the current session has access to them. Until they are specifically removed, they remain in place for the remainder of the session.

Table Properties within SQL Server
Conversely, table variables are only present for the length of the script or batch in which they are declared and are declared using the DECLARE statement. They are a lightweight choice for smaller datasets because they are kept in memory instead of TempDB.

Examples and Use Cases
Temporary Tables: To ensure data integrity and speed up processing when you need to store intermediate results during intricate data transformations or calculations within a session. Temporary tables are very useful for decomposing complicated searches into more manageable steps, which improves the readability and maintainability of the code.

As an illustration

CREATE TABLE #TempEmployees (
ID INT,
Name VARCHAR(50),
Department VARCHAR(50)
);

INSERT INTO #TempEmployees (ID, Name, Department)
SELECT ID, Name, Department
FROM Employees
WHERE Department = 'HumanResource';

Table Variables: When you need to hold a small set of data within a batch or script, providing a lightweight and temporary storage solution without the overhead of creating a physical table. They are particularly useful for passing small datasets as parameters to functions or stored procedures, enhancing performance, and simplifying code implementation.

Example
DECLARE @EmployeeTable TABLE (
ID INT,
Name VARCHAR(50),
Department VARCHAR(50)
);

INSERT INTO @EmployeeTable (ID, Name, Department)
SELECT ID, Name, Department
FROM Employees
WHERE Department = 'HumanResource';

Comparative Analysis
While both temporary tables and table variables serve similar purposes, they differ in several key aspects, including:

• Scope: Temporary tables are scoped to the session, while table variables are scoped to the batch or script.
• Storage: Temporary tables reside in the TempDB database, whereas table variables are stored in memory.
• Indexing: Temporary tables support indexing and statistics, whereas table variables do not.
• Transactions: Temporary tables support transactions, allowing for rollback operations, whereas table variables do not participate in transactions.

Best Practices
To make the most of temporary tables and table variables, consider the following best practices:

• Evaluate the size of your dataset: Table variables are more suitable for smaller datasets, while temporary tables are better suited for larger datasets.
• Properly index your temporary tables for improved performance, especially if dealing with large datasets and complex queries.
• Drop temporary objects when no longer needed to avoid cluttering TempDB and consuming unnecessary resources.
• Use table variables for lightweight operations such as small data manipulations or as parameters for functions and stored procedures.

Conclusion
An essential part of any SQL Server developer's toolbox, temporary tables and table variables provide versatile options for handling data while a session is open. Developers and database managers can fully utilize these constructs to maximize efficiency and speed data manipulation operations by knowing their distinctions, best practices, and useful applications. SQL Server experts can handle complicated data difficulties with confidence and speed when they use temporary tables and table variables, whether it's for storing intermediate findings, managing transactions, or performing iterative processing.