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.
In this article, we will show you, How to write SQL Server Truncate table statement. Before we get into the practical example, let us see the syntax behind this Truncate table statement.
Sql Server Truncate Table Syntax
The basic syntax behind the Truncate table statement is:
TRUNCATE TABLE Database_Name.Schema_Name.Table_Name
- Database_Name: Please specify the database name that contains the table.
- Schema_name: Please specify the name of the schema to which our table belongs to.
- Table_Name: Please provide the Table name on which you want to perform Truncate operation
Truncate Table advantages
- The Truncate Table statement uses less transaction 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 table performs locking at the table level (which is faster) but not on each row.
- After you apply Truncate table statement, zero pages will be left in the table.
- If the table contains an identity column, the counter for that column will be reset to the first value. For example, we have ID INT IDENTITY(1, 1) which contains 100 rows/records, and we performed 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.
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]