SQL TRUNCATE TABLE

In MySQL, the TRUNCATE TABLE query removes all records from a table. The TRUNCATE TABLE statement cannot be rolled back if you truncate a table.

Although TRUNCATE is a logged operation, SQL Server does not report every single row as the table is TRUNCATE. TRUNCATE, unlike DELETE, deletes the pages on which the data is stored rather than the specific rows. It also keeps track of the pages and extents that have been deallocated. However, because the deletion of those pages is not committed, there is enough information to roll back those pages.

The procedure is simply reapplied to restore the TRUNCATE TABLE. Unlike a true "minimally logged" operation like a BULK INSERT, the data contained is not required upon RESTORE. The locks will be removed only when you ROLLBACK the transaction, and you should see all the rows and pages restored on the table.

As long as the pages are locked with an exclusive lock, SQL Server knows they belong to the table, and they are retained until the transaction ends, just like all X locks. As a result, pages or extents cannot be extracted and stored and, more importantly, cannot be used again.

Truncating a table appears to be definitive, but it's actually a completely logged operation that can be rolled back, much like most things in SQL Server.

Example 1: Wrap the TRUNCATE table statement in the transaction:

ROLLBACK TRAN;
GO
SELECT COUNT(*) FROM dbo.Products;
DBCC IND('AdventureWorks', 'Products', -1);
GO

Example 2: The following example demonstrates the behavior of DELETE and TRUNCATE when the transaction is rolled back.

DROP TABLE IF EXISTS [dbo].[customer]
CREATE TABLE [dbo].[customer] (CustomerId INT, PrimaryPhone VARCHAR (12))
GO
INSERT INTO [dbo].[customer] (CustomerId, PrimaryPhone) 
VALUES (1,'214-456-3450'), (2,'914-585-5896'), (3,'234-758-2536')
GO
 
BEGIN TRANSACTION
    DELETE [dbo].[customer]
ROLLBACK TRANSACTION
SELECT * FROM [dbo].[customer]
GO
 
BEGIN TRANSACTION
    TRUNCATE TABLE [dbo].[customer]
ROLLBACK TRANSACTION
SELECT * FROM [dbo].[customer]

The ROLLBACK of the two transactions were successful.

Example 3: BEGIN TRAN -- Open a transaction

SELECT * FROM [HumanResources].[JobCandidate]	-- 13 Job candidates for AdventureWorks

TRUNCATE TABLE [HumanResources].[JobCandidate]	-- remove all data! Eeeek!

SELECT * FROM [HumanResources].[JobCandidate]	-- no rows, its all GONE

SELECT COUNT(1) AS [JobCandidate ROW Count] FROM [HumanResources].[JobCandidate] --GONE

ROLLBACK	-- I've changed my mind, I do want the JobCandidates after all...

SELECT * FROM [HumanResources].[JobCandidate]	-- and we're all back like magic!

The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.

The SQL TRUNCATE Table Statement removes all the records from a table, or specified partitions. It performs the same function as a DELETE statement without a WHERE clause.

Warning: If you truncate a table, the TRUNCATE TABLE statement can not be rolled back in some databases.

In SQL Server, the Truncate table is equivalent to the Delete command without the Where Clause. The DELETE statement in SQL can be used. However, the DELETE command is inefficient when dealing with a table with a high number of rows.

Syntax:

The syntax of the TRUNCATE TABLE statement is as follows:

TRUNCATE TABLE table_name

Parameters:

• TRUNCATE TABLE Database_Name.Schema_Name.Table_Name
• Database_Name: Database that contains it.
• Schema_name: Schema of it.
• Table_Name: Name.

Example 1:

TRUNCATE TABLE [SQLTruncateTable]
SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [SQLTruncateTable];
SELECT COUNT(*) AS Num_Columns FROM [SQLTruncateTable]

Example 2: Use the SQL TRUNCATE TABLE statement to remove all rows in the tmp table:

TRUNCATE TABLE tmp

We won't get any results if we query the tmp table again because it's now empty.

The SQL TRUNCATE statement (also known as TRUNCATE TABLE) deletes all data from a table.

The TRUNCATE TABLE statement is logically equivalent to a DELETE statement without a WHERE clause, which deletes all entries from a table, or a series of DROP TABLE and CREATE TABLE statements.

TRUNCATE TABLE is more efficient than DELETE since it drops and reconstructs the table rather than removing rows one by one.

Mainly The TRUNCATE TABLE command removes data from a table but not the table itself.

Syntax:

Here is the basic syntax of theTRUNCATE TABLE statement:

TRUNCATE [TABLE] table_name;

Following the TRUNCATE TABLE keywords, you specify the table name from which you want to remove all data.

The basic TRUNCATE statement looks the same:

TRUNCATE TABLE [schema_name.]table_name

Parameters:

schema_name: This is optional. If your table belongs to a different schema, you specify it here.

table_name: The name of the table to remove the data from.

In MySQL, the TRUNCATE statement deletes just one table at a time. If we want to delete many tables, we must use the TRUNCATE statement separately.

Syntax:

The below example shows how to truncate multiple tables in MySQL:

TRUNCATE TABLE table_name1;  
TRUNCATE TABLE table_name2;  
TRUNCATE TABLE table_name3; 

Example 1: Using the table names in our database, execute the SQL query underneath to construct numerous TRUNCATE TABLE commands at once:

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME)  
FROM INFORMATION_SCHEMA.TABLES  
WHERE table_schema = 'database_name'; 

Example 2: First, you have to temporarily disable the foreign key constraints in order for the truncate statement to work:

SET FOREIGN_KEY_CHECKS=0;

List out all the tables in the target database:

SELECT
    TABLE_NAME
FROM
    information_schema.tables
WHERE
    table_schema = 'db_name';

Then truncate all tables, on by one:

TRUNCATE TABLE table1;
TRUNCATE TABLE table2;
TRUNCATE TABLE table3;

Example 3: If we need to TRUNCATE multiple tables from different databases in MySQL which could include more than 1000 tables:

This is possible because to database metadata. This data is stored in the INFORMATION SCHEMA database, which contain details about all of the databases that the MySQL server manages, as well as information about all of the users who can contact the MySQL server.

Use the following query to get the list of tables from multiple databases:

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES 
where table_schema in (db1_name,db2_name);

Now execute the result of this query to truncate all the required tables.

By inserting the RESTART IDENTITY clause in the TRUNCATE statement, all identity columns are automatically rebuilt when truncating a table.

If the table has an identity column, the counter for that column is reset to its seed value. If no seed is specified, the number 1 is used. The truncate command demands extra transaction log space.

Optional If this option is provided, all occurrences in the truncated tables are reset.

When you truncate a table, the identification columns will pick up where they left off in terms of numbering.

Syntax:

TRUNCATE < table_name> RESTART IDENTITY CASCADE;
--RESTART will reset the identity sequence
--CASCADE all tables that have a foreign key reference to 
--table_name will be truncated as well

Example 1:

TRUNCATE ONLY inventory
RESTART IDENTITY;

The identification columns in the inventory table will be restored to their default values in this case. If you have a primary key field that you wish to reset to 1, this is useful.

Example 2:

TRUNCATE sch.mytable RESTART IDENTITY CASCADE;

If CASCADE is defined, then the sequences of all affected tables are reset.

TRUNCATE TABLE deletes all rows from a table, but it leaves the table structure, including columns, constraints, and indexes, intact. Use the DROP TABLE statement to delete the table description as well as its data.

You can't TRUNCATE a table with FK constraints (TRUNCATE isn't the same as DELETE). Both have the potential to ruin data integrity.

If you have a Foreign Key relationship, you must first drop the foreign key constraint before executing the truncate table statement.

Example 1:

• Remove constraints
• Perform TRUNCATE
• Delete manually the rows that now have references to nowhere
• Create constraints

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

Example 2: Let's demo this example, I am going to create two table dbo.Customer and dbo.Orders and then create Foreign Key Constraint on one of the column of Dbo.Orders to dbo.Customer table.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )

CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT,
    CustomerId int
    )

--Create Foreign Key Constraint 
    Alter table dbo.Orders with Nocheck
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)

If I attempt to truncate dbo. There will be no errors in the order table. However, when I try to truncate dbo. The customer fails. the table will generate a dbo error. A Foreign Key Constraint is used to start references.

For the time being, we can remove the constraint, truncate the table, and then reinstate the foreign key constraint.

Perform the Drop Foreign Key Constraint result, then run the truncate table statement to truncate the table. It should finish without errors.

The primary distinction is that the delete statement deletes data without resetting a table's identification, whereas the truncate command restores the identity of a specific table.

Both the delete and truncate commands can be used to remove table data.