I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/

In it, she briefly mentions that the Fill Factor setting does not affect Heaps.

Does this still apply today, and does she mean only pure heaps that don't even have non-clustered indexes?...or to any heap table regardless if it has non-clustered indexes?

  • Thanks Jonathan. Part of my confusion was originally based on the fact that I've seen "heaps" in the sys.indexes table too. Any reason why heaps and indexes are apparently regarded and discussed in a similar fashion (when it comes to databases), as if they are treated as the same types of objects? To me, conceptually, I lump heaps and B-trees into the same collection of objects because they're both types of data structures, where indexes to me are ways to organize, order, and reference the data that are stored within those objects, and therefor belong to a separate collection. – J.D. Sep 17 '18 at 19:10
  • 1
    It's probably the meta data about the object that makes it easier to put heaps in with sys.indexes rather than anywhere else. – Jonathan Fite Sep 18 '18 at 1:56

The non-clustered indexes on a heap will use the fill factor, but a heap is not an index (although it does appear in sys.indexes).

Since there is no way to reorganize a heap it can't have a fill factor. Fill factor only comes into the picture when rebuilding or creating an index; it only applies to indexes.

I believe How Forwarded Records are Read and Processed in a SQL Server Heap by Uwe Ricken may help you out. Specifically regarding how SQL Server uses PFS to determine inserts for records into the heap, and also how a non-clustered index uses the RID in relation to the heap.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.