SQL Server Myth: Error about fill factor

Mistaken idea #25: Several mistaken ideas about filling factor

It's all wrong.

25a) filling factor is always present.

No, through Books Online you can see (translator: I did not find this phrase in the new version of BOL):

Important: The fill factor only takes effect when an index is created or rebuilt, and the SQL Server storage engine does not always keep the free values and fill factors in the page consistent. The padding factor is meaningless if you want to keep the free values in the page and the fill factor that you specify. This is because the page needs to be paged even if it is dissatisfied.

Fill factor 0 and 100 are different

Error, by Bol a sentence can be seen

Filling factors 0 and 100 are all in one sense.

c) Fill factor set to 0 will reserve space on non-leaf nodes

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

This is wrong, this point Bol did not say, I do not know where this misunderstanding came from, but this is absolutely wrong. You can verify this by using the following code:

CREATE DATABASE foo; 
Go use 
foo; 
Go 
CREATE TABLE T1 (C1 INT 
IDENTITY, C2 CHAR (1000) DEFAULT ' a '); 
CREATE CLUSTERED INDEX t1c1 on T1 
(C1); 
Go 
SET NOCOUNT on; 
Go 
inserts into T1 DEFAULT VALUES; 

Go 10000

Next, set the fill factor to 0 and rebuild the index

SELECT [Fill_factor] from sys.indexes WHERE NAME = ' t1c1 ' and [object_id] = object_id (' t1 '); Go ALTER INDEX t1c1 on T1 REBUILD with (FILLFACTOR = 100); Go

After the code above executes, view the value of the M_FREECNT column in the established page, which is the value of the free space in the page:

EXEC sp_allocationmetadata ' T1 ';   Go DBCC traceon (3604); DBCC PAGE (foo, 1, 164, 3); --The root page, from the SP output Go DBCC page (foo, 1, 162, 1); --the page ID in the DBCC page output above go

With the above code you can see that the value is 10, which means that there is no space reserved in the industry. At this time a misunderstanding, about the above Sp_allocationmetadata implementation details please see this blog post.