The SQL TRUNCATE Table Statement is used to remove 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 SQL Server Truncate table statement. Before we get into the practical example, let us see the syntax behind this Truncate table statement.
TRUNCATE TABLE Database_Name.Schema_Name.Table_Name
- Database_Name: Please specify the database name that contains the table you want to truncate.
- Schema_name: Please specify the name of the schema to which our table belongs.
- Table_Name: Please provide the Table name on which you want to perform Truncate operation
SQL Truncate Table advantages
- 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.
- SQL Server 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.
TIP: The 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.
From the below screenshot, you can observe that we have [SQL Truncate table] table in the [SQL Tutorial], and the table holds 14 records. Here, we are going to perform the truncate operation on this table
Truncate Table Statement example
In this Sql Server truncate example, We are going to 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]