Tuesday, August 16, 2011

Difference between Truncate and Delete in SQL

Posted by Rahul Kharde at 1:48 AM
Truncate and Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure. Both statements delete the data from the table not the structure of the table.


Delete
Truncate
Speed
Slower
Faster
WHERE
Can be applied
Cannot be applied
IDENTITY
Cannot reset
Will reset
TRIGGER
Will fire
Won’t fire
Transaction Log
Will be logged
Won’t be logged
REFERENTIAL INTEGRITY
If Child table doesn’t have corresponding record from master table then it will allow
Will never ever allow if any referential integrity exists; no matter child table has record or not
DATA COMMAND
DML (data manipulation language) command.
DDL (data definition language)
ROLLBACK
Can’t rollback
Will rollback
TABLE VARIABLE
Can be deleted
Cannot be truncated
CDC(Change Data Capture)
Will allow
Won’t allow if CDC is enabled on table




EXAMPLE


CREATE TABLE Student
(
      sid INT identity PRIMARY KEY,
      Name Varchar(255) NOT NULL
)

INSERT INTO Student(Name)VALUES('abc')
INSERT INTO Student(Name)VALUES('pqr')
INSERT INTO Student(Name)VALUES('xyz')


-- Syntax of Delete command
DELETE Student WHERE sid=2



-- Syntax of Truncate Command
TRUNCATE TABLE Student



If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top