SQL TRUNCATE Table

The SQL TRUNCATE Table Statement removes all the records from a table, or specified partitions. The Truncate table in SQL Server is similar to the Delete command without Where Clause. Let me show you how to write this 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.
  • It 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