What is difference between TRUNCATE and DELETE statement
Answer Posted / samba shiva reddy . m
DELETE TABLE is a logged operation, so the deletion of each
row gets logged in the transaction log, which makes it slow.
DELETE removes rows one at a time and records an entry in
the transaction log for each deleted row.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back.
DELETE is DDL command
example :
begin transaction deleteoncondition
select * from emp
Delete * from emp where empid = 1
rollback deleteoncondition
select * from emp
sp_help samba --Table structure
TRUNCATE TABLE deletes all the rows in a table, but it
won't log the deletion of each row, instead it logs the
De-allocation of the data pages of the table, which makes it
faster.
Because TRUNCATE TABLE is not logged, it cannot activate a
trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.
TRUNCATE wont delete the structure of the table.
DROP will delete the structure of the table also.
Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
How to define and use table alias names in ms sql server?
How to execute a stored procedure in ms sql server?
What are the advantages of mirroring?
How important do you consider cursors or while loops for a transactional database?
what is a check constraint?
What stored procedure can you use to display the current processes?
What is the difference between varchar and varchar(max) datatypes?
What is extent? Types of extents?
Explain how would you store your query in an SSRS report or a Database server?
System variable and temporary variables
Write a program to fetch first 10 records from a file?
How do I create an extended event in sql server?
What are the 10 characteristics of data quality?
What are different replication agents and what's their purpose? : sql server replication
What is a filestream?