What is the difference between DELETE and TRUNCATE in SQL?
Thread Tags
Adobe Illustrator Adobe InDesign Adobe Photoshop Android Development Android Programming Android Studio Automation Testing C / C++ Programming Career Options College Lounge css digital marketing Digital Marketing / SEO Fireworks Graphic Design Graphic designing Graphics Design Graphics Designing HTML? Illustrator Internship Training logo Design logo designing Magento Photoshop PHP Programming QA react-native Scroll Website SEO Single Page Website Stock Managment System Testing Typography UI UI/UX Development UI Design UI Designing UI development User Interface UX Design Web Designing Website Design Wordpress wpRegister for free!
Registration at Smart Mentors is completely free and takes only a few seconds. By registering you’ll gain:
- Full Posting Privileges.
- Access to Private Messaging.
- Optional Email Notification.
- Ability to Fully Participate.
Register Now, or check out the Site Tour and find out everything Smart Mentors has to offer.
Tagged: PHP Programming
This thread contains 1 reply, has 2 voices, and was last updated by Nitesh User Do not delete 1 year, 12 months ago.
- Author Replies
- January 23, 2019 at 10:32 am #52285
Hello,
The difference between Delete and Truncate command is the most basic concept in SQL yet it confuses many people.
DELETE:
1. Removes Some or All rows from a table.2. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.
3. Causes all DELETE triggers on the table to fire.
4. It removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE.
5. Every deleted row is locked, thus it requires more number of locks and database resources.
TRUNCATE:
1. Removes All rows from a table.2. Does not require a WHERE clause, so you can not filter rows while Truncating.
3. With SQL Server 2016 you can Truncate a Table Partition, for more details check [here].
4. IDENTITY columns are re-seeded on this operation if no seed was defined then the default value 1 is used.
5. No Triggers are fired on this operation because it does not operate on individual rows.
6. It de-allocates Data Pages instead of Rows and records Data Pages instead of Rows in Transaction logs, this is faster than DELETE.
7. While de-allocating Pages it locks Pages and not Rows, thus it requires less number of locks and few resources.
Regards,
Nitesh Bavishiya - AuthorPosts
You must be logged in to reply to this thread.Please login or register. Registration is 100% free.