New in Fabric Warehouse SQL: TRUNCATE

Microsoft Fabric Warehouse: TRUNCATE

One of the new additional functionality in Fabric Warehouse SQL is the arrival of the Truncate statement. The truncate statement is used primarily to delete all rows from a specified table but it doesn't delete the table itself. This simple means after we delete all the rows, we can still query the table and see the structure of the table - the columns, data types, indexes, constraints

Create and Insert Records into a Table

First, I want to create a table named my_transaction and insert 3 records into the new table.

The screenshot below shows how to create the table.

CREATE TABLE my_transaction 
(
    SalesID INT,
    SalesRep VARCHAR(50),
    Product VARCHAR(100),
    SalesAmount INT
);

The screenshot below shows how to insert records into the create table and query the table using select * from my_transaction.

INSERT INTO my_transaction 
    (SalesID, SalesRep, Product, SalesAmount) 
VALUES 
    (1, 'John Smith', 'Laptop', 2000),
    (2, 'Andy Cunliff', 'TV', 5400),
    (3, 'Abigail Reeve', 'Furnitures', 1800);

I proceeded to use TRUNCATE TABLE my_transaction to delete all the 3 rows in the table.

TRUNCATE TABLE my_transaction;

Finally, I queried the table using the select * from my_transaction and no rows are visible.