TRUNCATE Table Statement

TRUNCATE Table Statement

TRUNCATE table statement is used to remove all the records permanently.
Its a DDL statement, so we do not need to explicitly COMMIT the transaction.
Also a ROLLBACK command does not rolled back the transaction.

SELECT * FROM EMPLOYEES_TEST;
TRUNCATE TABLE EMPLOYEES_TEST;
ROLLBACK;
SELECT * FROM EMPLOYEES_TEST;

TRUNCATE Impact on DML Statements:

TRUNCATE is a DDL statement. So any other DML transaction executed before this statement are automatically committed post this statement execution.

DROP TABLE EMPLOYEES_TEST;
CREATE TABLE EMP_TEST
AS
SELECT * FROM EMPLOYEES;
CREATE TABLE EMPLOYEES_TEST
AS
SELECT * FROM EMPLOYEES;
SELECT * FROM EMP_TEST;
SELECT * FROM EMPLOYEES_TEST;
DELETE EMP_TEST
WHERE DEPARTMENT_ID = 30;
SELECT * FROM EMP_TEST;
TRUNCATE TABLE EMPLOYEES_TEST;
ROLLBACK;
SELECT * FROM EMP_TEST;
SELECT * FROM EMPLOYEES_TEST;

So ROLLBACK does not have any impact on TRUNCATE command. 7 Records from EMP_TEST table are permanently deleted.

RELATED TOPICS: