The answer is
All good answers, to which I must add:
Since TRUNCATE TABLE is a DDL (Data Defination Language), not a DML (Data Manipulation Langauge) command, the Delete Triggers do not run.
Summary of Delete Vs Truncate in SQL server
For Complete Article follow this link : http://codaffection.com/sql-server-article/delete-vs-truncate-in-sql-server/
Taken from dotnet mob article :Delete Vs Truncate in SQL Server
TRUNCATE can be rolled back if wrapped in a transaction.
Please see the two references below and test yourself:-
The TRUNCATE vs. DELETE is one of the infamous questions during SQL interviews. Just make sure you explain it properly to the Interviewer or it might cost you the job. The problem is that not many are aware so most likely they will consider the answer as wrong if you tell them that YES Truncate can be rolled back.
One more difference specific to microsoft sql server is with delete you can use output statement to track what records have been deleted, e.g.:
delete from [SomeTable]
output deleted.Id, deleted.Name
You cannot do this with truncate.
DELETE statement can have a WHERE clause to delete specific records whereas TRUNCATE statement does not require any and wipes the entire table. Importantly, the DELETE statement logs the deleted date whereas the TRUNCATE statement does not.
One further difference of the two operations is that if the table contains an identity column, the counter for that column is reset 1 (or to the seed value defined for the column) under TRUNCATE. DELETE does not have this affect.
If accidentally you removed all the data from table using Delete/Truncate. You can rollback committed transaction. Restore the last backup and run transaction log till the time when Delete/Truncate is about to happen.
The related information below is from a blog post:
While working on database, we are using Delete and Truncate without knowing the differences between them. In this article we will discuss the difference between Delete and Truncate in Sql.
Delete:
- 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
Truncate
- 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.
Note: Delete and Truncate both can be rolled back when used with Transaction. If Transaction is done, means committed then we can not rollback Truncate command, but we can still rollback Delete command from Log files, as delete write records them in Log file in case it is needed to rollback in future from log files.
If you have a Foreign key constraint referring to the table you are trying to truncate, this won't work even if the referring table has no data in it. This is because the foreign key checking is done with DDL rather than DML. This can be got around by temporarily disabling the foreign key constraint(s) to the table.
Delete table is a logged operation. So the deletion of each row gets logged in the transaction log, which makes it slow. Truncate table also deletes all the rows in a table, but it won't log the deletion of each row instead it logs the deallocation of the data pages of the table, which makes it faster.
~ If accidentally you removed all the data from table using Delete/Truncate. You can rollback committed transaction. Restore the last backup and run transaction log till the time when Delete/Truncate is about to happen.
In short, truncate doesn't log anything (so is much faster but can't be undone) whereas delete is logged (and can be part of a larger transaction, will rollback etc). If you have data that you don't want in a table in dev it is normally better to truncate as you don't run the risk of filling up the transaction log
Here is my detailed answer on the difference between DELETE and TRUNCATE in SQL Server
• Remove Data : First thing first, both can be used to remove the rows from table.
But a DELETE can be used to remove the rows not only from a Table but also from a VIEW or the result of an OPENROWSET or OPENQUERY subject to provider capabilities.
• FROM Clause : With DELETE you can also delete rows from one table/view/rowset_function_limited based on rows from another table by using another FROM clause. In that FROM clause you can also write normal JOIN conditions. Actually you can create a DELETE statement from a SELECT statement that doesn’t contain any aggregate functions by replacing SELECT with DELETE and removing column names.
With TRUNCATE you can’t do that.
• WHERE : A TRUNCATE cannot have WHERE Conditions, but a DELETE can. That means with TRUNCATE you can’t delete a specific row or specific group of rows. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause.
• Performance : TRUNCATE TABLE is faster and uses fewer system and transaction log resources. And one of the reason is locks used by either statements. The DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
• Transaction log : DELETE statement removes rows one at a time and makes individual entries in the transaction log for each row.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
• Pages : After a DELETE statement is executed, the table can still contain empty pages. TRUNCATE removes the data by deallocating the data pages used to store the table data.
• Trigger : TRUNCATE does not activate the delete triggers on the table. So you must be very careful while using TRUNCATE. One should never use a TRUNCATE if delete Trigger is defined on the table to do some automatic cleanup or logging action when rows are deleted.
• Identity Column : With TRUNCATE if the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. DELETE doesn’t reset the identity counter. So if you want to retain the identity counter, use DELETE instead.
• Replication : DELETE can be used against table used in transactional replication or merge replication.
While TRUNCATE cannot be used against the tables involved in transactional replication or merge replication.
• Rollback : DELETE statement can be rolled back.
TRUNCATE can also be rolled back provided it is enclosed in a TRANSACTION block and session is not closed. Once session is closed you won't be able to Rollback TRUNCATE.
• Restrictions : The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.
And if DELETE is used against View, that View must be an Updatable view.
TRUNCATE cannot be used against the table used in Indexed view.
TRUNCATE cannot be used against the table referenced by a FOREIGN KEY constraint, unless a table that has a foreign key that references itself.
Yes, DELETE is slower, TRUNCATE is faster. Why?
DELETE must read the records, check constraints, update the block, update indexes, and generate redo/undo. All of that takes time.
TRUNCATE simply adjusts a pointer in the database for the table (the High Water Mark) and poof! the data is gone.
This is Oracle specific, AFAIK.
Truncate can also be Rollbacked here the exapmle
begin Tran
delete from Employee
select * from Employee
Rollback
select * from Employee
A small correction to the original answer - delete also generates significant amounts of redo (as undo is itself protected by redo). This can be seen from autotrace output:
SQL> delete from t1;
10918 rows deleted.
Elapsed: 00:00:00.58
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=1)
1 0 DELETE OF 'T1'
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=43 Card=1)
Statistics
----------------------------------------------------------
30 recursive calls
12118 db block gets
213 consistent gets
142 physical reads
3975328 redo size
441 bytes sent via SQL*Net to client
537 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10918 rows processed
The biggest difference is that truncate is non logged operation while delete is.
Simply it means that in case of a database crash , you cannot recover the data operated upon by truncate but with delete you can.
More details here
I'd comment on matthieu's post, but I don't have the rep yet...
In MySQL, the auto increment counter gets reset with truncate, but not with delete.
DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.
The Syntax of a SQL DELETE statement is:
DELETE FROM table_name [WHERE condition];
TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
From: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands
DELETE
DELETE is a DML command DELETE you can rollback Delete = Only Delete- so it can be rolled back In DELETE you can write conditions using WHERE clause Syntax – Delete from [Table] where [Condition]
TRUNCATE
TRUNCATE is a DDL command You can't rollback in TRUNCATE, TRUNCATE removes the record permanently Truncate = Delete+Commit -so we can't roll back You can't use conditions(WHERE clause) in TRUNCATE Syntax – Truncate table [Table]
For more details visit
http://www.zilckh.com/what-is-the-difference-between-truncate-and-delete/
TRUNCATE is the DDL statement whereas DELETE is a DML statement. Below are the differences between the two:
As
TRUNCATEis a DDL (Data definition language) statement it does not require a commit to make the changes permanent. And this is the reason why rows deleted by truncate could not be rollbacked. On the other handDELETEis a DML (Data manipulation language) statement hence requires explicit commit to make its effect permanent.TRUNCATEalways removes all the rows from a table, leaving the table empty and the table structure intact whereasDELETEmay remove conditionally if the where clause is used.The rows deleted by
TRUNCATE TABLEstatement cannot be restored and you can not specify the where clause in theTRUNCATEstatement.TRUNCATEstatements does not fire triggers as opposed of on delete trigger onDELETEstatement
Here is the very good link relevant to the topic.
DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE. Table level lock will be added when Truncating.
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire. Row level lock will be added when deleting.
From: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands
By issuing a TRUNCATE TABLE statement, you are instructing SQL Server to delete every record within a table, without any logging or transaction processing taking place.
"Truncate doesn't log anything" is correct. I'd go further:
Truncate is not executed in the context of a transaction.
The speed advantage of truncate over delete should be obvious. That advantage ranges from trivial to enormous, depending on your situation.
However, I've seen truncate unintentionally break referential integrity, and violate other constraints. The power that you gain by modifying data outside a transaction has to be balanced against the responsibility that you inherit when you walk the tightrope without a net.
It is not that truncate does not log anything in SQL Server. truncate does not log any information but it log the deallocation of data page for the table on which you fired TRUNCATE.
and truncated record can be rollback if we define transaction at beginning and we can recover the truncated record after rollback it. But can not recover truncated records from the transaction log backup after committed truncated transaction.
The difference between truncate and delete is listed below:
+----------------------------------------+----------------------------------------------+
| Truncate | Delete |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing | We can Rollback after delete. |
| Truncate. | |
| | |
| Example: | Example: |
| BEGIN TRAN | BEGIN TRAN |
| TRUNCATE TABLE tranTest | DELETE FROM tranTest |
| SELECT * FROM tranTest | SELECT * FROM tranTest |
| ROLLBACK | ROLLBACK |
| SELECT * FROM tranTest | SELECT * FROM tranTest |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table. | Delete does not reset identity of table. |
+----------------------------------------+----------------------------------------------+
| It locks the entire table. | It locks the table row. |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language) | Its DML(Data Manipulation Language) |
| command. | command. |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it. | We can use WHERE to filter data to delete. |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate. | Trigger is fired. |
+----------------------------------------+----------------------------------------------+
| Syntax : | Syntax : |
| 1) TRUNCATE TABLE table_name | 1) DELETE FROM table_name |
| | 2) DELETE FROM table_name WHERE |
| | example_column_id IN (1,2,3) |
+----------------------------------------+----------------------------------------------+
With SQL Server or MySQL, if there is a PK with auto increment, truncate will reset the counter.
A big reason it is handy, is when you need to refresh the data in a multi-million row table, but don't want to rebuild it. "Delete *" would take forever, whereas the perfomance impact of Truncate would be negligible.
In SQL Server 2005 I believe that you can rollback a truncate
TRUNCATE
TRUNCATE SQL query removes all rows from a table, without logging the individual row deletions.
- TRUNCATE is a DDL command.
- TRUNCATE is executed using a table lock and whole table is locked for remove all records.
- We cannot use WHERE clause with TRUNCATE.
- TRUNCATE removes all rows from a table.
- Minimal logging in transaction log, so it is faster performance wise.
- TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
- To use Truncate on a table you need at least ALTER permission on the table.
- Truncate uses less transaction space than the Delete statement.
- Truncate cannot be used with indexed views.
- TRUNCATE is faster than DELETE.
DELETE
To execute a DELETE queue, delete permissions are required on the target table. If you need to use a WHERE clause in a DELETE, select permissions are required as well.
- DELETE is a DML command.
- DELETE is executed using a row lock, each row in the table is locked for deletion.
- We can use where clause with DELETE to filter & delete specific records.
- The DELETE command is used to remove rows from a table based on WHERE condition.
- It maintain the log, so it slower than TRUNCATE.
- The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
- Identity of column keep DELETE retains the identity.
- To use Delete you need DELETE permission on the table.
- Delete uses the more transaction space than Truncate statement.
- Delete can be used with indexed views.
Truncate and Delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both Sql commands can create lot of trouble until you are familiar with details before using it. An Incorrect choice of command can result is either very slow process or can even blew up log segment, if too much data needs to be removed and log segment is not enough. That's why it's critical to know when to use truncate and delete command in SQL but before using these you should be aware of the Differences between Truncate and Delete, and based upon them, we should be able to find out when DELETE is better option for removing data or TRUNCATE should be used to purge tables.
Refer check click here
TRUNCATE is fast, DELETE is slow.
Although, TRUNCATE has no accountability.
Truncate command is used to re-initialize the table, it is a DDL command which delete all the rows of table.Whereas DELETE is a DML command which is used to delete row or set of rows according to some condition, if condition is not specified then this command will delete all the rows from the table.
Can't do DDL over a dblink.
Source: Stackoverflow.com