When you TRUNCATE a table less information is logged. This means the TRUNCATE statement executes very fast; however, it does so at the expense of not logging each row deleted. This means, that you need to be very careful when using the command (actually be careful with DELETE as well!).
TRUNCATE TABLE UserMaster
- Truncate is a DDL command.
- Truncate table always lock the table and page but not each row.As it removes all the data.
- Cannot use Where condition.
- It Removes all the data.
- Truncate table cannot activate a trigger because the operation does not log individual row deletions.
- Faster in performance wise, because it doesn’t keep any logs.
The DELETE command is used to remove records from a database. It is the most common way to do so. In its simplest form you can remove all the rows from a database or you can add a WHERE clause to remove only those meeting the criteria.
When execute the DELETE command,the DBMS logs all removed rows. This means it is easier to recover from a mistake, than it would a mistaken TRUNCATE.
DELETE FROM UserMaster
- Delete is a DML command.
- Delete statement is executed using a row lock,each row in the table is locked for deletion.
- We can specify filters in where clause.
- It deletes specified data if where condition exists.
- Delete activities a trigger because the operation are logged individually.
- Slower than Truncate because it Keeps logs