SQL #8: Temporary tables

2 minute read

This is the eighth in a series of SQL notes I made during the Kubrick Data Engineering training course. The others are:
#1: Database Design
#2: SQL Design
#3: DQL
#4: DDL
#5: DML
#6: Sorting
#7: Joining
#9: Programmable Objects
#10: Indexing
#11: Operation & Optimization

All the posts in this series are my personal note taking and may be updated as the course progresses.

Common Table Expressions

A CTE is a type of temporary table that can be used to help with complex multi-select queries and joins. The syntax to create one is:

WITH cte(ctePrice)
AS
(
    SELECT ListPrice 
    FROM Production.Product
)

To use it:

SELECT ctePrice
FROM cte

The CTE can have its own column names and acts just like a normally defined table.

Recursive CTEs

Recursive CTEs are able to reference themselves and are particularly good at dealing with hierarchical data.

A recursive routine has three main parts:

  • Invocation: Calls the recursive routine
  • Recursive Invocation of the Routine: Calls itself
  • Termination Check: Logic that to terminal routine at some point

The following script will output a column n with numbers counting from 1-50 in the rows 1-50.

WITH cte
AS     
(SELECT 1 AS n -- Anchor member
    UNION ALL
    SELECT n + 1 -- Recursive member
    FROM   cte
    WHERE  n < 50 -- Terminator
)

SELECT n
FROM   cte -- Invocation

The Anchor starts off the loop, it is joined row-wise by UNION ALL to the Recursive which is n + 1 where n is from the cte. It continues to add 1 into each new row until the Terminator enforces n < 50.

Every recursive CTE must have all anchors before recursives, as well as the anchor and recursive members all having the same columns to allow UNION ALL. Additionally, the FROM expression may only refer to the CTE once and the following expressions are not allowed GROUP BY, HAVING, LEFT, RIGHT, OUTER JOIN, Scalar aggregation, SELECT DISTINCT, Subqueries, TOP.

Temporary Tables

A temporary table can be created just like normal tables in SQL. They are logged and stored in physical memory in tempdb. They are distinguished using #.
The difference from a normal table is that the scope of any particular temporary table is the session in which it is created. Also if they are created inside a stored procedure they are destroyed upon completion of the stored procedure. The syntax to create and use one is the same.

CREATE TABLE dbo.#bigSales
(
     saleID INT NOT NULL
    ,storeID INT
    ,category VARCHAR(10)
)

Updated: