Results 1 to 8 of 8

Thread: Truncate Vs Delete

  1. #1

    Thread Starter
    Lively Member VB_client's Avatar
    Join Date
    Feb 2004
    Location
    India
    Posts
    87

    Truncate Vs Delete

    Is there any wway to roll back the data after executing Truncate statement...?

    Why can't we Delete the records instead of Truncating. What are the advantages over the other...?

    Thanks in Advance
    In order to be successful all you need is a little faith within yourself

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709

    Re: Truncate Vs Delete

    Truncate:
    http://msdn2.microsoft.com/en-us/library/ms177570.aspx

    Delete:
    http://msdn2.microsoft.com/en-us/lib...5(SQL.90).aspx

    Truncate: Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3
    Frenzied Member d3gerald's Avatar
    Join Date
    Jan 2006
    Posts
    1,348

    Re: Truncate Vs Delete

    if you are using ado, it has a rollbacktrans method, try to study it and use it
    On error goto Trap

    Trap:
    in case of emergency, drop the case...

    ****************************************
    If this post has been resolved. Please mark it as "Resolved" by going through the "Thread Tools" above and clicking on the "Mark Thread Resolved " option.
    if a post is helpful to you, Please Rate it by clicking on the Rate link right below the avatar

  4. #4
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709

    Re: Truncate Vs Delete

    You can not rollback a TRUNCATE call as there is no transaction logging performed.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    Lively Member VB_client's Avatar
    Join Date
    Feb 2004
    Location
    India
    Posts
    87

    Re: Truncate Vs Delete

    Thanks to all people who cleared my doubt
    In order to be successful all you need is a little faith within yourself

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,169

    Re: Truncate Vs Delete

    Quote Originally Posted by VB_client
    What are the advantages over the other...?
    Even though this thread is basically resolved, I thought it would be helpful to summarize for people who find this thread in the future...

    TRUNCATE is specifically designed to delete rows from a table when the desire is to have the delete be fast and to have the delete not be logged.

    DELETE of a large table will effectively copy all the deleted rows into the transaction log - until they are all captured - then when the transaction is "committed" the physical delete occurs. This can be very expensive on the server and the log.

    Thus if the goal is to truly remove all rows from a table - and not experience the expense of logging - then TRUNCATE can be used.

    It is a rare technique - I've never used TRUNCATE myself!

    edit: I can imagine times I could have used it - basically during test loading of BULK data into tables...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Truncate Vs Delete

    I have used the Truncate table command when I'm loading bulk data into a temporary holding table to validate the data before appling it the the database. Just much quicker way to empty the table out.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  8. #8

    Re: Truncate Vs Delete

    Here is a summary of some important differences between these sql commands:


    sql truncate command:


    1) It is a DDL (Data Definition Language) command, therefore commands such as COMMIT and ROLLBACK do not apply to this command (the exceptions here are PostgreSQL and MSSQL, whose implementation of the TRUNCATE command allows the command to be used in a transaction)

    2) You cannot undo the operation of deleting records, it occurs automatically and is irreversible (except for the above exceptions - provided, however, that the operation is included in the TRANSACTION block and the session is not closed). In case of Oracle - Includes two implicit commits, one before and one after the statement is executed. Therefore, the command cannot be withdrawn while a runtime error will result in commit anyway

    3) Deletes all records from the table, records cannot be limited to deletion. For Oracle, when the table is split per partition, individual partitions can be truncated (TRUNCATE) in isolation, making it possible to partially remove all data from the table

    4) Frees up the space occupied by the data in the table (in the TABLESPACE - on disk). For Oracle - if you use the REUSE STORAGE clause, the data segments will not be rolled back, i.e. you will keep space from the deleted rows allocated to the table, which can be a bit more efficient if the table is to be reloaded with data. The high mark will be reset

    5) TRUNCATE works much faster than DELETE

    6) Oracle Flashback in the case of TRUNCATE prevents going back to pre-operative states

    7) Oracle - TRUNCATE cannot be granted (GRANT) without using DROP ANY TABLE

    8) The TRUNCATE operation makes unusable indexes usable again

    9) TRUNCATE cannot be used when the enabled foreign key refers to another table, then you can:
    - execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
    - execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;





    sql delete command:

    1) It is a DML (Data Manipulation Language) command, therefore the following commands are used for this command: COMMIT and ROLLBACK

    2) You can undo the operation of removing records by using the ROLLBACK command

    3) Deletes all or some records from the table, you can limit the records to be deleted by using the WHERE clause

    4) Does not free the space occupied by the data in the table (in the TABLESPACE - on the disk)

    5) DELETE works much slower than TRUNCATE

    6) Oracle Flashback works for DELETE

    7) Oracle - For DELETE, you can use the GRANT command

    8) The DELETE operation does not make unusable indexes usable again

    9) DELETE in case foreign key enabled refers to another table, can (or not) be applied depending on foreign key configuration (if not), please:
    - execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
    - execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;


    more details: https://rozwoj-oprogramowania.pl/en/...vs-delete.html
    Last edited by softwaremansql; Aug 22nd, 2022 at 07:43 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width