SQL TRUNCATE TABLE statement is used to delete complete data from an existing table. It is similar to a delete statement but without a Where Clause.
- It is a DDL command.
- It does not support WHERE clause , Removes all the data all the time.
- Identity column in a table is reset to seed value.
- It removes the records by deallocating the data pages used to store the table’s data, and only the page deallocations are kept in the transaction log.
- It is Faster than DELETE as it locks entire table.
- It does not support Triggers.
SYNTAX
TRUNCATE TABLE table_name;
Let’s look at an example of TRUNCATE table statement in SQL Server.
First we create a table named as SALES then insert some records in table.
CREATE TABLE dbo.SALES
(SALES_ID INT IDENTITY(1,1) ,
ITEM VARCHAR(50),
ITEM_CODE VARCHAR(15),
ITEM_QTY INT,
PURCHASED_DATE DATE
)
INSERT INTO dbo.SALES
(ITEM, ITEM_CODE, ITEM_QTY, PURCHASED_DATE)
VALUES('LAPTOP- HP','PAVILLION-XCV89', 500,'2019-04-05'),
('KEYBOARD','KEYB-567', 100,'2019-05-19'),
('MOUSE','MM-ER56', 250,'2019-05-15'),
('PEN DRIVE','PD-20ET', 400,'2019-07-25'),
('RADEON GRAPHIC CARD',null, 478,'2019-09-19')
Now we have a SALES table as given below .
SELECT * FROM dbo.SALES
Truncate command to delete all records from table
Following statement deletes all records from SALES table.
TRUNCATE TABLE dbo.SALES
Lets select the records from a table SALES.
As you can see it returns blank data, that means all the records from table has been deleted by truncate command.
Truncate command with resetting an identity value
If table has an identity column then truncate command reset the seed value to its original value for Identity column, It is just an opposite of DELETE command which does not reset a seed value for an identity column.
Means, when you delete all the records from the table, then insert new records into a table, an identity column values will be started again with its original seed value.
In our case, as we have just deleted all the records from a SALES table using truncate command. Now when we insert any new record in table the value for an identity column is started again with 1, not incrementing by 1 to the last value of an identity cloumn which was in the table before truncating a table records means 5+1 =6 .
As we have already deleted all records from SALES table, Lets insert some fresh records in table to see the what value is given to an indentity column for new records.
INSERT INTO dbo.SALES
(ITEM, ITEM_CODE, ITEM_QTY, PURCHASED_DATE)
VALUES( 'LAPTOP- DELL','DELL-YU678', 900,'2019-04-05')
Lets select records from a SALES table.
SELECT * FROM dbo.SALES
As you can see, identity value for new records is 1, that means truncate command reset the value for an identity to its orignal seed value.
TRUNCATE Command does not support WHERE Clause
You can not delete specific records from table using Truncate command.
Lets try to delete records from SALES table for SALES_ID 1.
TRUNCATE TABLE dbo.SALES WHERE SALES_ID =1
As you can see, It gives an error.
TRUNCATE command can be rolled back
Follwing T-SQL Code, truncates the table records under transaction block, you can see truncated records are get backed as just roll back command is executed.
BEGIN TRAN
SELECT * FROM dbo.SALES
TRUNCATE TABLE dbo.SALES
SELECT * FROM dbo.SALES
ROLLBACK TRAN
SELECT * FROM dbo.SALES
If you want to delete records based on specific condition then you should use Delete Command only and if you want to delete all records from a table then you should use truncate command.
Also Read..
