locked
Heap and forward pointers RRS feed

  • Question

  • The great writer Thomas describes that 

    Heaps have the great disadvantage of needing forward pointers when rows are deleted

    Please here I have heard that forwarded records are only generated when there is Update in variable columns. 

    https://kejserbi.wordpress.com/2014/01/12/clustered-indexes-vs-heaps/

    Please let me know how delete/Insert in normal heap can cause forwarded records.


    Thursday, May 18, 2017 11:33 AM

All replies

  • Hi XtendedLearner,

     

    You can refer to the How Forwarded Records Are Created part in this article: http://sqlmag.com/stored-procedures/removing-forwarded-records-heap-tables

     

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Shanky_621MVP Monday, June 19, 2017 5:14 AM
    Tuesday, May 23, 2017 11:55 AM
  • Thanks for the response.

    My question is "How delete/Insert in normal heap can cause forwarded records."

    Sunday, June 18, 2017 10:29 PM
  • Both should not. A forwarded record is caused when a column value is updated in a row where the column will not longer fit on the page, and there are no pages with space for it, a pointer will be added to point to the column contents in a new page.
    Sunday, June 18, 2017 11:12 PM
  • Well, that is, when a column is updated and the row no longer fits in the space of the previous row, then the row is deleted, inserted elsewhere, with a forward pointer.

    That and three bucks will get you a small cold brew at Starbucks.

    Josh

    Monday, June 19, 2017 3:22 AM
  • Thanks for the response.

    My question is "How delete/Insert in normal heap can cause forwarded records."

    Can i request you to read the links shared, please read the link Teige shared I will quote from that

    When a larger value is updated in a variable length field in a record, SQL Server will first try to expand the row of the existing page on the chance that there’s enough room to continue to use the original page. If that fails and SQL Server can’t find an existing page with enough room for the larger value, a new page has to be created for the record. The data is moved to the new page and is assigned a new Relative Identifier (RID). The old page now contains a forwarding pointer (forwarded record) that tells SQL Server to jump to the new RID. The new record location also has a back pointer to the old record. 

    Now tell me what are you not able to understand. For further understanding please read This Blog


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Monday, June 19, 2017 5:13 AM
  • Hi XtendedLearner,

    an INSERT or a DELETE will NOT generate forwarded records!

    Inserting records in a HEAP will scan the PFS for free space. A record will be placed in any page of the heap where enough place is available.

    If you delete a record from a heap it will update PFS and the Slot Array - that's it.

    Heaps are not affected by the GHOST_CLEANUP process!

    https://www.sqlskills.com/blogs/paul/ghost-cleanup-redux/


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Monday, June 19, 2017 8:12 AM
  • Now tell me what are you not able to understand. For further understanding please read This Blog


    Cheers,

    Shashank


    Not so hard my friend. I think it was clear to the TE. The question was whether an INSERT or a DELETE can lead to forwarded records. And that can be answered quite easy with "NO" :)

    BTW: Do we meet in Bangalore in August? :)

    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Monday, June 19, 2017 8:15 AM
  • Hi XtendedLearner,

    an INSERT or a DELETE will NOT generate forwarded records!

    Inserting records in a HEAP will scan the PFS for free space. A record will be placed in any page of the heap where enough place is available.

    If you delete a record from a heap it will update PFS and the Slot Array - that's it.

    Heaps are not affected by the GHOST_CLEANUP process!

    https://www.sqlskills.com/blogs/paul/ghost-cleanup-redux/


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Arggg!!! yes you are correct, Sometimes we do miss the actual words of OP and apply our judgement. Its update which actually does this and not insert or deletes when I read the question I actually thought OP is asking about How Forwareded records are actually generated.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, June 19, 2017 9:03 AM

  • BTW: Do we meet in Bangalore in August? :)
    I am looking forward to it....

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, June 19, 2017 9:05 AM
  • Not so hard my friend. I think it was clear to the TE. The question was whether an INSERT or a DELETE can lead to forwarded records. And that can be answered quite easy with "NO" :)

    Now the question remains what XtendedLearner had read. One possibility is that the author was entirely confused. But it could also be that the author was referring to that when an UPDATE is implemented as a delete operation + an insert operation. Or more exactly one delete, one insertion/update of a forward pointer and an insert.

    Monday, June 19, 2017 10:28 AM
  • BTW: Do we meet in Bangalore in August? :)

    I am looking forward to it....

    That looks like a forward pointer to me. :-)

    Monday, June 19, 2017 2:09 PM
  • BTW: Do we meet in Bangalore in August? :)

    I am looking forward to it....

    That looks like a forward pointer to me. :-)


    Indeed!!!....So Erland are you coming ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, June 19, 2017 4:20 PM