SQL TRUNCATE Table

The SQL Server TRUNCATE Statement removes all the records from a table or specified partitions. The Truncate table is similar to the Delete command without the Where Clause. Let me show you how to write this SQL Truncate Table statement.

Before the example, let me show the syntax of this SQL Server Truncate Table statement.

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

SQL Server Truncate Table advantages

The SQL Truncate Table statement removes the data from all rows from the specified one, but the structure, constraints, columns, and indexes will remain the same. No need for the Where Clause.

  • This statement uses fewer transactions when compared to its counterpart DELETE Statement. Because this statement deallocates the data pages used to store the records/data. And the transaction log will store these page deallocations.
  • Truncate performs locking at the table level (which is faster) but not on each row.
  • After you apply this statement, zero pages will be left.
  • If the table contains an identity column, the counter for that column will reset to the first value. For instance, we have ID INT IDENTITY(1, 1), which contains 100 rows/records, and we performed the TRUNCATE table on ID. It will delete rows or all the records from ID and reset the IDENTITY to 1.

We operate on the below.

Employee Records 1

TIP: Please refer to the DELETE Statement and Where Clause articles in SQL Server to understand the delete approach.

Truncate Example

In this SQL Server Truncate example, We will remove the above-specified database table records.

TRUNCATE TABLE [SQLTruncateTable]
SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [SQLTruncateTable];

SELECT COUNT(*) AS Num_Columns FROM [SQLTruncateTable]
SQL Truncate Table Statement 2
Categories SQL