Temporary tables

Emulation context

In Fabric, temporary tables can be standard (non-distributed) or Parquet-based (distributed) — and distributed tables are more performant. Hence, we will be emulating temporary tables as Parquet-based whenever possible.

The exception is going to be when your source temporary tables are using identity attribute.

---

Code example (distributed table)

Synapse Analytics:

CREATE TABLE #Products (
  ProductID INT,
  ProductName NVARCHAR(100),
  Category NVARCHAR(50)
);

INSERT INTO #Products (ProductID, ProductName, Category)
VALUES (101, 'Keyboard', 'Electronics'),
       (102, 'Desk', 'Furniture');

Fabric Warehouse (generated by SQL Tran):

CREATE TABLE #Products (
    ProductID INT,
    ProductName VARCHAR(200) /* SQLTRAN FIX: NVARCHAR(100) */,
    Category VARCHAR(100) /* SQLTRAN FIX: NVARCHAR(50) */
) WITH (DISTRIBUTION=ROUND_ROBIN);

INSERT INTO #Products (ProductID, ProductName, Category)
VALUES (101, 'Keyboard', 'Electronics'),
       (102, 'Desk', 'Furniture');

---

Code example (non-distributed table)

Synapse Analytics:

CREATE TABLE #Products (
    ProductID INT IDENTITY,
    ProductName NVARCHAR(100),
    Category NVARCHAR(50)
);

INSERT INTO #Products (ProductID, ProductName, Category)
VALUES (101, 'Keyboard', 'Electronics'),
       (102, 'Desk', 'Furniture');

Fabric Warehouse (generated by SQL Tran):

CREATE TABLE #Products (
    ProductID INT IDENTITY,
    ProductName VARCHAR(200) /* SQLTRAN FIX: NVARCHAR(100) */,
    Category VARCHAR(100) /* SQLTRAN FIX: NVARCHAR(50) */
);

INSERT INTO #Products (ProductID, ProductName, Category)
VALUES (101, 'Keyboard', 'Electronics'),
       (102, 'Desk', 'Furniture');