Let us consider we have following table names employee in our SQL Server database having following records.
- Using Delete Command
- Using Truncate command
- Delete command
- DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
- If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
- DELETE Can be used with or without a WHERE clause
- DELETE Activates Triggers.
- DELETE can be rolled back.
- DELETE is DML Command.
- DELETE does not reset identity of the table.
To delete the specific record from table then we need to use where clause .
delete from TableName where ColumName=value
Lets us consider we are deleting records from above employee table which has id one as
delete from Employee where Id=1
To delete all records from table we need write query without where clause .
delete from TableName
delete from Employee
- Truncate command
- TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
- TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
- TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
- You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
- TRUNCATE cannot be rolled back.
- TRUNCATE is DDL Command.
- TRUNCATE Resets identity of the table.
truncate table TableName
truncate table Employee
I hope this article is useful for all readers . If you have a suggestion related to this article then please contact me.