Truncate table query

  • Vimal Lohani

    SSCommitted

    Points: 1650

    Comments posted to this topic are about the item Truncate table query

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    Please don't say truncate table data isn't logged unless you understand the concepts involved.

    😉

    The Myth that DROP and TRUNCATE TABLE are Non-Logged

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • twin.devil

    SSC-Insane

    Points: 22208

    Koen Verbeeck (9/11/2014)


    Please don't say truncate table data isn't logged unless you understand the concepts involved.

    😉

    The Myth that DROP and TRUNCATE TABLE are Non-Logged

    +1, i remember in my earlier days when i busted this myth, the looks on my colleges faces were priceless 😛

  • Iulian -207023

    SSCertifiable

    Points: 7507

    Although I answered more based on the instinct that the data is safely recovered after a rollback. I am still wandering why have I thought that TRUNCATE would drop the content and that would not recover the content after rollback. I wander if there is a difference between 2005 and 2008+ ?

    Nice question , thanks for it.

    Cheers,

    Iulian

  • TomThomson

    SSC Guru

    Points: 104763

    Koen Verbeeck (9/11/2014)


    Please don't say truncate table data isn't logged unless you understand the concepts involved.

    😉

    The Myth that DROP and TRUNCATE TABLE are Non-Logged

    +1000000

    It's amazing, when even the reference quoted in the explanation states that the operation is in fact logged that teh explanation can say that it isn't.

    It's a useful queston, but the explanation is nonsense.

    Tom

  • TomThomson

    SSC Guru

    Points: 104763

    Iulian -207023 (9/11/2014)


    Although I answered more based on the instinct that the data is safely recovered after a rollback. I am still wandering why have I thought that TRUNCATE would drop the content and that would not recover the content after rollback. I wander if there is a difference between 2005 and 2008+ ?

    No, no difference. Truncate writes page deallocation information to the log file; if the transaction is rolled back, the page deallocations are rolled back and the data is available. If it is committed, the page deallocations are made permanent.

    edit: I guess I should mention that this has been true ever since the truncate statement has existed.

    Tom

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    TomThomson (9/11/2014)


    Koen Verbeeck (9/11/2014)


    Please don't say truncate table data isn't logged unless you understand the concepts involved.

    😉

    The Myth that DROP and TRUNCATE TABLE are Non-Logged

    +1000000

    It's amazing, when even the reference quoted in the explanation states that the operation is in fact logged that teh explanation can say that it isn't.

    It's a useful queston, but the explanation is nonsense.

    It's a pity, because how many people read only the explanation and not the reference? Let alone that they read this thread as well.

    This will keep the myth alive...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Iulian -207023

    SSCertifiable

    Points: 7507

    I see, thank you Tom

  • This was removed by the editor as SPAM

  • Vimal Lohani

    SSCommitted

    Points: 1650

    TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

    this means if page is recovered from the transaction log, data can be recovered.data or row directly not logged on transaction log, because of this it is too fast.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    Vimal Lohani (9/11/2014)


    TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

    this means if page is recovered from the transaction log, data can be recovered.data or row directly not logged on transaction log, because of this it is too fast.

    All fine and dandy, but the statement "TRUNCATE is DDL operation and it is not logged in log file." is incorrent and can lead to serious confusion.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • TomThomson

    SSC Guru

    Points: 104763

    Koen Verbeeck (9/11/2014)


    Vimal Lohani (9/11/2014)


    TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

    this means if page is recovered from the transaction log, data can be recovered.data or row directly not logged on transaction log, because of this it is too fast.

    All fine and dandy, but the statement "TRUNCATE is DDL operation and it is not logged in log file." is incorrent and can lead to serious confusion.

    It is a DDL statement, but off hand I can't think of any DDL statement that is unlogged. So yes, it creates serious confusion and will tend to permit the myth that TRUNCATE is not transactional.

    Tom

  • Vimal Lohani

    SSCommitted

    Points: 1650

    Sorry for that, but i can't correct this sentence as i have no admin right for question of the day.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • Iulian -207023

    SSCertifiable

    Points: 7507

    TomThomson (9/11/2014)


    Koen Verbeeck (9/11/2014)


    Vimal Lohani (9/11/2014)


    TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

    this means if page is recovered from the transaction log, data can be recovered.data or row directly not logged on transaction log, because of this it is too fast.

    All fine and dandy, but the statement "TRUNCATE is DDL operation and it is not logged in log file." is incorrent and can lead to serious confusion.

    It is a DDL statement, but off hand I can't think of any DDL statement that is unlogged. So yes, it creates serious confusion and will tend to permit the myth that TRUNCATE is not transactional.

    What happens if while the pages are deallocated , another task / process / txn ( or something ) allocates that space for other purposes , and I run the rollback after the pages were alocated by this second task / process / txn.

    Most probably there is a mechanism to resolve that but which and how ?

    Please ignore if it does not make sense.

  • Vimal Lohani

    SSCommitted

    Points: 1650

    Iulian -207023 (9/11/2014)


    TomThomson (9/11/2014)


    Koen Verbeeck (9/11/2014)


    Vimal Lohani (9/11/2014)


    TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

    this means if page is recovered from the transaction log, data can be recovered.data or row directly not logged on transaction log, because of this it is too fast.

    All fine and dandy, but the statement "TRUNCATE is DDL operation and it is not logged in log file." is incorrent and can lead to serious confusion.

    It is a DDL statement, but off hand I can't think of any DDL statement that is unlogged. So yes, it creates serious confusion and will tend to permit the myth that TRUNCATE is not transactional.

    What happens if while the pages are deallocated , another task / process / txn ( or something ) allocates that space for other purposes , and I run the rollback after the pages were alocated by this second task / process / txn.

    Most probably there is a mechanism to resolve that but which and how ?

    Please ignore if it does not make sense.

    See Iulian, you know data is saved in pages. when you use truncate, reference the page or pages removed from table and logged in transaction log. that space or page will active till the transaction commit or rollback.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Viewing 15 posts - 1 through 15 (of 47 total)

You must be logged in to reply to this topic. Login to reply