SQL TRUNCATE Table

The SQL TRUNCATE Table 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 Server Truncate Table statement. Before the example, let me show the syntax of this statement.

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

SQL Truncate Table advantages

The Sql Server Truncate Table statement removes the data from all rows from the specified one, but the structure, constraints, columns, indexes will remain the same. No need of 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.
  • SQL Server Truncate performs locking at the table level (which is faster) but not on each row.
  • After you apply the 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. This truncate statement will delete rows or all the records from ID, and reset the IDENTITY to 1.

We perform the operation on the below

SQL Truncate Table Statement 1

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

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

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