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 can we delete Duplicate row in table?
What are the differences between decimal and float in ms sql server?
What is sqlcmd?
Define synonym?
How do I manually uninstall an instance of sql server 2016?
Is null vs coalesce?
What is the difference between char, varchar and nvarchar?
What are types of scd? : sql server analysis services, ssas
Do you know what is normalization of database? What are its benefits?
Explain what is public role in sql server?
what is spatial nonclustered index
how to define testing of network layers? : Sql server database administration
Is it possible for a stored procedure to call itself or recursive stored procedure?
What is the difference RDBMS and Graph Database?
What are the system database in sql server 2008?