Misunderstanding of SQL Server: Misunderstanding of fill factor on the seventh day on the 30th day

Misunderstanding #25: Multiple misunderstandings about fill Factors
All are errors.

25a) Fill Factor always exists
No, it can be seen through Books Online (Translator: I did not find this sentence in the new version of the BOL ):
Important:
The fill factor takes effect only when the index is created or rebuilt. the SQL Server storage engine does not always ensure that the idle value on the page is consistent with the fill factor. To keep the free value on the page and the specified fill factor unchanged, the fill factor will become meaningless. This is because pages need to be paged even if they are not satisfied.

25 B) Fill factors 0 and 100 are different
Error. It can be seen in one message of the BOL.
Fill factor 0 and 100 all mean each aspect.

25 c) If the fill factor is set to 0, the space will be reserved on non-leaf nodes.
This is wrong. I didn't say this on the BOL. I don't know where this misunderstanding came from, but it is absolutely wrong. You can verify this by using the following code:

Copy codeThe Code is as follows: 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
Insert into t1 default values;
GO 1, 10000

Next, set the fill factor to 0 and recreate the index.Copy codeThe Code is as follows: 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 above code is executed, you can view the value of the m_freeCnt column on the established page, that is, the value of the available space on the page:Copy codeThe Code is as follows: 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

The code above shows that the value is 10, that is, there is no reserved space in the industry. This is a misunderstanding. For details about the implementation of sp_allocationMetadata above, please read this blog post.