When interviewing SQL Server candidates, I find that most don't know some of the basic differences between SQL Server's delete and truncate commands, as well as whether these operations can be undone, so in this tip we'll cover some of these aspects. Delete is a DML command, while truncate is a DDL command. Truncate can be used to delete all data from the table without maintaining the integrity of the table. On the other hand, the delete statement can be used to delete the specific data.
With the delete command we cannot bypass integrity enforcement mechanisms. I created a trigger called TRGDeleteStudent in tblStudent. When we run a DELETE statement on the tblstudent table, the trigger inserts a record into a tblDeleteStudent table. When we run the DELETE command, the identity values will not be reset to the initial values.
For execution of the TRUNCATE table statement, the identity value will be reset. To delete data using the DELETE statement, we must have the DELETE permission on the table. To delete the data using the TRUNCATE TABLE statement, we need the ALTER TABLE permission. If you want to quickly delete all rows in a table, and you're really sure you want to do it, and you don't have foreign keys in the tables, then a TRUNCATE is probably faster than a DELETE.
Therefore, DELETE operations can be undone (undone), while DROP and TRUNCATE operations cannot be undone. When you issue a TRUNCATE TABLE statement, you instruct SQL Server to delete all records in a table, without any log or transaction processing taking place. TRUNCATE always deletes all rows in a table, leaving the table empty and the table structure intact, while DELETE can conditionally delete if the where clause is used. Deletion can be granted in a table to another user or role, but it cannot be truncated without using a DROP ANY TABLE lease.
The DELETE statement can have a WHERE clause to delete specific records, while the TRUNCATE statement does not require any and deletes the entire table. I need to test your claim that TRUNCATE resets the IDENTITY seeds, I also need to test whether TRUNCATE will follow cascading deletions for foreign key RI. A TRUNCATE should never be used if a delete trigger is defined in the table to perform any automatic cleanup or logging action when rows are deleted. So I often start with a question like What is the difference between Delete and Truncate? , which I think is a very basic concept and that all database administrators should be aware of.
Delete does not affect the identifier of the data object, but truncate assigns a new data object identifier unless there has never been an insertion into the table since its creation. Even a single insertion that is reverted will cause a new data object identifier to be assigned when truncated. There are many ways to delete data in SQL, including the DELETE, TRUNCATE TABLE, and DROP TABLE commands. Rows deleted by the TRUNCATE TABLE statement cannot be restored, and you cannot specify the where clause in the TRUNCATE statement.
The main difference between them is that the delete statement deletes data without restoring the identity of a table, while the truncate command restores the identity of a particular table.
