Interested to Buy Any Domain ? << Click Here >> for more details...
What is difference between TRUNCATE and DELETE statement
- 12 Answers
- 23001 Views
- CTS, I also Faced
- E-Mail Answers
Answer Posted / shireen
TRUNCATE TABLE is functionally identical to DELETE
statement with no WHERE clause: both remove all rows in the
table. But TRUNCATE TABLE is faster and uses fewer system
and transaction log resources than DELETE.
The DELETE statement removes rows one at a time and records
an entry in the transaction log for each deleted row.
TRUNCATE TABLE 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 TABLE removes all rows from a table, but the table
structure and its columns, constraints, indexes and so on
remain. The counter used by an identity for new rows is
reset to the seed for the column. 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.
You cannot use TRUNCATE TABLE on a table referenced by a
FOREIGN KEY constraint; instead, use DELETE statement
without a WHERE clause. Because TRUNCATE TABLE is not
logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in
an indexed view.
| Is This Answer Correct ? | 3 Yes | 1 No |
Post New Answer View All Answers
what are statistics, under what circumstances they go out of date, how do you update them? : Sql server database administration
What you can do to delete a table without the delete trigger firing?
Differentiate sql server reporting services vs. Crystal reports?
What is a trigger? Why we need it?
what are the different types of replication you can set up in sql server? : Sql server database administration
How to start and end transact-sql statements?
What are acid properties of transaction?
What are the recovery models for a database?
What command would you use to create an index?
What are diverse clauses that form a part of sql?
Write a sql query to delete duplicate records from a table called table1
Explain different types of self contained sub query?
What is Federation and Federation Member?
Do you know what are the properties of the relational tables?
What is a transactions?
