Temporary Tables in SQL Server

This article will cover all possible type of Temp Table - Local Temp table, Global Temp table, Table Variable and CTEs

Temporary tables store data in the same format as original tables for quick access. TempDB holds temporary tables. You can choose, insert, and delete like a standard table. In procedure, you can deletes them.

Two types of temporary tables exist: local and global.

• "Local Temporary Tables" Tempdb stores local temporary tables. Local temporary tables are only accessible to the session that created them. These tables are automatically deleted after the procedure or session. The prefix #, such as #table_name,  can be created with the same name in multiple windows. stored inside the Temporary Folder of TempDB

CREATE TABLE #Company (
id bigint,
commpanyName VARCHAR(MAX),
IsActive bit
);

NSERT INTO #Company
SELECT id, ComanyName, IsActive
FROM Company
WHERE IsActive = 1;

 

Temporary table can be created Automatically

SELECT countryName,countryValue
INTO #country
FROM country
where countryName like 'A%'

A temporary table named #country was created, with two columns derived from the SELECT statement's select list.

 

• “Global Temporary Tables” Tempdb stores global temporary tables. Global temporary tables are available to all sessions and users. They dropped automatically after the last temporary table session. ##table_name is an example.  stored inside the Temporary Folder of TempDB

create table ##GlobalTemporaryTable  (
column_name varchar(20),
…
)

Drop temporary table (s)

DROP TABLE #table_name1,#table_name2

When should temporary tables be utilised in SQL Server?

• A temporary table is utilised to hold data from various tables during a session, employing complex queries and necessitating server-side data manipulation prior to data retrieval. For instance, when selecting data from multiple tables, it is necessary to apply business logic to certain columns, and the selection may vary based on the calculated results. Upon completion of the temporary data, the table is removed from the server. Temporary tables do not have permanent storage. 
• An additional example is a stored procedure that entails data manipulation through complex SQL queries.

 

 

Table variable
SQL table variables are local variables creatd with a DECLARE statement and assigned values with SET or SELECT statements. The TempDB stores variables with their values until they are out of scope, then drops them. 

 

Table Variable vs. Temp Table: What's the Difference?

• A temporary table is simple to establish and facilitates data backup. The table variable necessitates effort when one typically constructs standard tables. 
• Temporary table results can be utilised by numerous users. The table variable is accessible just to the current user. 
• The temporary table will be kept in the tempdb. It will generate network traffic. When substantial data resides in the temporary table, it must operate across the database. A performance issue will persist. The table variable will initially be in physical memory for a portion of the data, but as its size expands, it will subsequently be relocated to the tempdb. 
• The temporary table may do all DDL operations. It facilitates the creation, deletion, alteration, and other modifications of indexes. The table variable does not permit DDL operations. The table variable permits the creation of only the clustered index. 
• A temporary table can be utilised for the current session or globally. To enable a multi-user session to access the results in the table. A table variable may be utilised throughout the duration of that program. 
• Stored procedure When doing DML operations with the temporary table, transactions can be rolled back or committed. The temporary variable cannot utilise the transactions. However, we cannot revert or commit for table variables. 
• The function enables the utilisation of the table variable. Functions are unable to utilise the temporary variable. DML operations cannot be performed within functions; nevertheless, they can be executed using table variables. The table variable is not functioning in that manner. 

Database developers and DBAs use SQL Server temp tables to store and operate on data for better performance.

A Common Table Expression (CTE) 
A Common Table Expression (CTE) is a short-lived/transient named result set derived from a simple SELECT operation, utilised in a future SELECT statement. 

Each SQL CTE functions as a named query, the outcome of which is stored in a virtual table (a CTE) that is subsequently referenced in the main query.

 

WITH cte_expression_name[ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

To access the CTE results, employ a select query utilising the name of the CTE expression.

Select [Column1,Column2,Column3,Column4...] from cte_expression_name

OR

SELECT * FROM cte_expression_name

 

Common Table Expression types 

A recursive common table expression (CTE) 

• that references itself. The CTE executes and returns subsets of information until it returns the entire result set. 
• Recursive CTEs are useful for querying ranking data like organisation charts where one worker reports to a manager or multi-level bill of materials when a product has many components and each component has numerous components. 

Declare @RowNo int =1;
;with ROW_CTE as
  (
     SELECT @RowNo as ROWNO
     UNION ALL
     SELECT  ROWNO+1
 FROM  ROW_CTE
 WHERE RowNo < 10
   )

SELECT * FROM ROW_CTE

A CTE will be used to generate a basic recursive query that displays row numbers 1–10. First, declared an integer variable 'RowNo', set its default value to 1, and generated our first CTE query, 'ROW_CTE'. After displaying the default row number, this CTE uses union ALL to increment it by 1 until it reaches 10. Use your preferred query to display CTE results.

 

Non-Recursive CTE
They don't repeatedly process subroutines. 

We used one column as ROWNO. Next, we write our CTE pick-out question in Query. After creating a CTE question, the CTE Expression function selects an announcement.

;with ROW_CTE(ROWNO) as
  (
    SELECT
     ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
    FROM table_name
    WHERE id <= 10
   )

SELECT * FROM ROW_CTE

CTEs are usually stored in memory and can be quickly changed. Temporary tables are written to disc, which can create performance overhead.

In general, CTEs are more efficient than temporary tables for small data sets. CTEs are memory-based and do not need disc access. Temporary tables can be indexed and optimised for queries, making them more efficient for bigger data sets.