[SQL] Fill Factor

Reference: http://msdn.microsoft.com/en-us/library/ms177459(v=sql.105).aspx

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

如果插入的 index 會亂跳(例如插入新的英文單字)，最好預留一些暫存空間。如果插入的 index 是有順序關係的話，就不用開啟這個功能。((昨天就學到兩招：included columns 以及 fill factor，有趣，表面上只是點點滑鼠按按確定而已，實際上隱藏深奧邏輯判斷及經驗。))