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. Before we get into the practical example, let us see the syntax behind this SQL server Truncate table statement.
SQL Truncate Table Syntax
The basic syntax behind the SQL Server 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
SQL 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.
- Truncate table performs locking at the table level (which is faster) but not on each row.
- After you apply SQL 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 will delete all the records from ID, and reset the IDENTITY to 1.
TIP: The SQL Truncate Table statement removes all rows from the specified table, but the table structure, constraints, columns, indexes will remain the same.
In this article, we will show you, How to write Truncate table in SQL Server. From the below screenshot you can observe that We have [SQL Truncate table] table in [SQL Tutorial] and it holds 14 records. Here, we are going to perform the truncate operation on this table
SQL TRUNCATE Table Statement
In this example, We are going to truncate the above-specified 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]
Thank You for Visiting Our Blog