SQL TRUNCATE Table

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

TRUNCATE TABLE Database_Name.Schema_Name.Table_Name
  • Database_Name: Database that contains the truncate table.
  • Schema_name: Schema of the table.
  • Table_Name: Table name to perform Truncate operation.

SQL Truncate Table advantages

The Sql Server Truncate Table statement removes all rows from the specified table, but the table structure, constraints, columns, indexes will remain the same. No need of Where Clause

  • The Truncate Table statement uses fewer transactions when compared to its counterpart DELETE Statement. Because the Truncate Table statement deallocates the data pages used to store the records/data. And the transaction log will store these page deallocations.
  • Truncate table performs locking at the table level (which is faster) but not on each row.
  • After you apply the Truncate table statement, zero pages will be left in the table.
  • 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. This truncate statement will delete all the records from ID, and reset the IDENTITY to 1.

We perform the truncate operation on this table

SQL Truncate Table Statement 1

TIP: Please refer to DELETE Statement and Where Clause articles in SQL Server.

Truncate Table Statement example

In this Sql Server truncate example, We will truncate the above-specified database Table.

-- SQL Server TRUNCATE TABLE Example
TRUNCATE TABLE [SQL Tutorial].[dbo].[SQL Truncate Table]

Let us see the Output

-- SQL Server TRUNCATE TABLE Example
SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [SQL Tutorial].[dbo].[SQL Truncate Table];

SELECT COUNT(*) AS Num_Columns FROM [SQL Tutorial].[dbo].[SQL Truncate Table]
SQL Truncate Table Statement 2