Temporary Tables in a Microsoft Fabric Warehouse

Problem

I was excited when Microsoft announced the ability to create session-scoped temporary tables in a Fabric warehouse. However, after using Microsoft Fabric temporary tables, I quickly felt disappointed. When will they be ready for prime time, and in the meantime, what other options are available?

Solution

This article will review how to create temporary tables in a Fabric warehouse. I’ll cover shortcomings and explain how these temporary tables are not the same ones you see in an operational database platform like SQL Server. How do we take advantage of temporary result sets without using temporary tables? By the end of this article, you’ll know how temporary tables behave in a Fabric warehouse.

Why Temporary Tables?

Experts have written countless articles about the benefits of temporary tables. For instance, imagine you need to transform a few hundred rows on a table with over 100 million rows. Putting those few hundred rows into a temporary table makes sense. Perhaps there isn’t a great index in place. Or you need to perform multiple passes on the table. In his article on temporary tables, Erik Darling says, “Temporary tables are like a second chance to get schema right.”

I like using temporary tables to break down long Common Table Expressions (CTEs) into manageable chunks. This method allows you to troubleshoot easily when issues inevitably arise.

Now that we know why temporary tables are useful, let’s walk through the demo.

Watch the Video

Watch the video version or follow the steps below.

Facing the Challenge

To follow along with this demo, you need two things:

A workspace with Fabric capacity. Microsoft is still offering a free trial.
A warehouse workload type.

Create a Numbers Table

With this demo, I want to highlight the issue in two steps. First, we’ll create a Numbers table with a single column called Number. Aaron Bertrand wrote several articles on number tables, starting with “The SQL Server Numbers Table, Explained – Part 1.”

/*
mssqltips.com
Create the Numbers table.
*/
CREATE TABLE dbo.Numbers
(
    Number INT
);
GO

I often don’t generate a permanent Numbers table in demos unless I want to apply additional logic with big datasets. However, SQL returns an error message when I run the code below.

-- mssqltips.com
INSERT INTO dbo.Numbers
(
    Number
)
SELECT TOP 1000000
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
FROM sys.all_columns s1
    CROSS JOIN sys.all_columns s2
    CROSS JOIN sys.all_columns s3
    CROSS JOIN sys.all_columns s4;
GO

Output:

Msg 15816, Level 16, State 6, Line 35
The query references an object that is not supported in distributed processing mode.

The error message is a sign of things to come with temporary tables. Instead of a system object, I’ll use a method first popularized by Itzik Ben-Gan and taken directly from Cathrine Wilhelmsen’s blog. This method involves several CTEs and performing a CROSS JOIN to generate a sequence. The main point of this method is that we do not use a system object like sys.all_columns.

/*
mssqltips.com
Source: https://www.cathrinewilhelmsen.net/using-a-numbers-table-in-sql-server-to-insert-test-data/
*/
;WITH
    L0   AS (SELECT 1 AS n UNION ALL SELECT 1),
    L1   AS (SELECT 1 AS n FROM L0 AS a CROSS JOIN L0 AS b),
    L2   AS (SELECT 1 AS n FROM L1 AS a CROSS JOIN L1 AS b),
    L3   AS (SELECT 1 AS n FROM L2 AS a CROSS JOIN L2 AS b),
    L4   AS (SELECT 1 AS n FROM L3 AS a CROSS JOIN L3 AS b),
    L5   AS (SELECT 1 AS n FROM L4 AS a CROSS JOIN L4 AS b),
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO dbo.Numbers (Number)
SELECT TOP (1000000) n FROM Nums ORDER BY n; /* Insert as many numbers as needed */
GO

After a few seconds, SQL populates our numbers table with one million rows.

Create a Products Table

Next, let’s create a Products table with a few columns and populate it with 100,000 rows. If you are wondering, I could have skipped the Numbers table altogether and used the CTE.

/*
mssqltips.com
Create the Products table.
*/
CREATE TABLE dbo.Products
(
    ProductId INT,
    ProductName VARCHAR(260),
    ProductCategory VARCHAR(260),
    Price DECIMAL(18,2)
);
GO
/*
Insert 100,000 rows into the Products table.
*/
INSERT INTO dbo.Products
(
    ProductId,
    ProductName,
    ProductCategory,
    Price
)
SELECT TOP 100000
    n.Number AS ProductId,
    CONCAT('Product', CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR)) AS ProductName, 
    CASE
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 3 = 1 THEN 'Video Games'
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 3 = 2 THEN 'Sports Cards'
        ELSE 'VHS'
    END AS ProductCategory,
    (ABS(CHECKSUM(NEWID())) % 5 + 1) * (10 + ABS(CHECKSUM(NEWID())) % 90) AS Price
FROM dbo.Numbers n;
GO

Create a Temporary Table

Imagine you want to store a subset of the Products table and apply transformations before updating the permanent table. First, let’s create a temporary table to hold some of the rows from our Products table.

-- mssqltips.com
DROP TABLE IF EXISTS #TemporaryProducts;
GO
CREATE TABLE #TemporaryProducts
(
    ProductId INT,
    ProductCategory VARCHAR(260),
    Price DECIMAL(18,2)
);
GO

With the script below, I’m inserting products with a price of less than $50 into my Products table and updating values based on a CASE expression. Finally, I’m increasing the cost of that product by 10% (due to inflation).

-- mssqltips.com
INSERT INTO #TemporaryProducts (ProductId, ProductCategory, Price)
SELECT 
    ProductId, 
    CASE 
        WHEN ProductCategory = 'Video Games' THEN 'Gaming'
        WHEN ProductCategory = 'Sports Cards' THEN 'Collectibles'
        ELSE 'Retro Media'
    END AS ProductCategory,
    Price * 1.10 AS Price
FROM dbo.Products
WHERE Price < 50;
GO

Output:

Msg 15816, Level 16, State 3, Line 125
The query references an object that is not supported in distributed processing mode.

Oh no! That error message is back. It’s strange because the Microsoft documentation suggests that temporary tables are in the delta parquet format.

How about we manually insert a row into the temporary table?

-- mssqltips.com
INSERT INTO #TemporaryProducts (ProductId, ProductCategory, Price)
VALUES (1, 'Gaming', 42.42);
GO

That seems to work. Let’s see if we can insert the value from my temporary table into Products.

-- mssqltips.com
INSERT INTO dbo.Products (ProductId, ProductCategory, Price)
SELECT ProductId, ProductCategory, Price
FROM #TemporaryProducts;
GO

No, that didn’t work either.

One last attempt: Can we at least JOIN the two tables?

-- mssqltips.com
SELECT p.ProductName, tp.ProductCategory
FROM dbo.Products p
INNER JOIN #TemporaryProducts tp ON p.ProductId = tp.ProductId;
GO

No luck again.

That’s a big disappointment. Temporary tables in a Fabric warehouse don’t seem to do anything. I hope Microsoft fixes this or stops saying you can create temporary tables in a warehouse. Microsoft keeps a list of known issues, and this one is included. However, the issue’s status is marked as fixed. Does that mean they plan on fixing it, or is it already fixed?

Maybe by the time you read this, Microsoft will have fixed the issue, and you’ll wonder why this guy is going on about a non-issue. Until then, I’d like to offer two workarounds.

Dynamic SQL

The first solution that comes to mind is using dynamic SQL to create a unique table name that no other session is likely to access. Dynamic SQL enables you to construct SQL statements dynamically during runtime. Let’s create a table in the code block below and interact with it. Please remember that my example below is merely a starting point.

/*
OPTION 1: Dynamic SQL
 - This method gets complicated quickly, so use it at your own risk.
*/
DECLARE @tableName NVARCHAR(256);
DECLARE @sqlStatement NVARCHAR(MAX);
-- Generate a unique table name using a GUID (replacing dashes with underscores)
SELECT @tableName = REPLACE(CONCAT('TemporaryTable_', NEWID()), '-', '_');
-- Create the table dynamically
SET @sqlStatement
    = CONCAT(
                'CREATE TABLE ',
                QUOTENAME(@tableName),
                ' (
        ProductId INT,
        ProductCategory VARCHAR(260),
        Price DECIMAL(18,2)
    );'
            );
EXEC sp_executesql @sqlStatement;
-- Insert sample data dynamically
SET @sqlStatement
    = CONCAT(
                'INSERT INTO ',
                QUOTENAME(@tableName),
                ' (ProductId, ProductCategory, Price)
     VALUES (1, ''Gaming'', 42.42);'
            );
EXEC sp_executesql @sqlStatement;
-- Select data dynamically
SET @sqlStatement = CONCAT('SELECT * FROM ', QUOTENAME(@tableName), ';');
EXEC sp_executesql @sqlStatement;
-- Drop the table dynamically
SET @sqlStatement = CONCAT('DROP TABLE ', QUOTENAME(@tableName), ';');
EXEC sp_executesql @sqlStatement;
GO

The biggest downside is that you must use dynamic SQL each time you reference the table, making the code harder to maintain and troubleshoot. While I’m not the biggest fan of this approach, it could work for your situation. If, for some reason, you don’t want to delete these tables as part of the script or stored procedure, you could also create a pipeline that performs this task nightly.

What about a different approach?

Common Table Expressions (CTEs)

I’ll admit that using a CTE doesn’t solve the problem of why you are creating temporary tables in the first place. However, CTEs are a viable option if you have a stored procedure performing several transformation steps. The code block below generates the same results as our temporary table. Then, I use the CTE to perform an UPDATE.

-- mssqltips.com
;WITH CTE_Products
AS (SELECT ProductId,
           CASE
               WHEN ProductCategory = 'Video Games' THEN
                   'Gaming'
               WHEN ProductCategory = 'Sports Cards' THEN
                   'Collectibles'
               ELSE
                   'Retro Media'
           END AS NewProductCategory,
           Price * 1.10 AS NewPrice
    FROM dbo.Products
    WHERE Price < 50
   )
UPDATE p
SET p.ProductCategory = c.NewProductCategory,
    p.Price = c.NewPrice
FROM dbo.Products p
    JOIN CTE_Products c
        ON p.ProductId = c.ProductId;
GO

I’m not a huge fan of overly complex CTEs. I find them hard to read and troubleshoot. At this point, I might recommend using temporary tables. However, as the saying goes, “When life gives you lemons, make lemonade.”

Wrapping Up

As mentioned in the article, I hope Microsoft releases functional temporary tables soon. What we currently have feels like a remnant of something left over from Azure Synapse that someone forgot to clean up. However, until that day, give the two suggestions above a try. I’m interested to hear if you’ve developed other workarounds.

Next Steps

Do you want to learn more about dynamic SQL? One of the best resources available is Erland Sommarskog’s article “The Curse and Blessings of Dynamic SQL.” The article is long, but if the topic piques your interest, it’s worth the time investment.
Erik Darling has an informative series of articles that examine various use cases for temporary tables, starting with “Making The Most Of Temporary Tables In SQL Server Part 1: Fully Parallel Inserts.”

Resources

Community

Subscribe