SQL Server misunderstanding: three misunderstandings about NULL bitmap on the seventh day on the 30th day

This also reduces the issue of CPU cache hit failure (click this link to view how the CPU cache works and the MESI protocol ). Let's expose three common mistakes related to NULL bitmap.

Misunderstanding # 6a: NULL bitmap is not always used

Correct

Even if the table does not allow NULL columns, the NULL bitmap always exists for data rows (data rows refer to the heap or clustered index leaf nodes ). However, for an index row (the so-called index row is the non-leaf node of the clustered index and non-clustered index and the leaf node of the non-clustered index), the NULL bitmap is not always effective.

The following statement can effectively prove this point:
Copy codeThe Code is as follows:
Create table NullTest (c1 int not null );
CREATE NONCLUSTERED INDEX
NullTest_NC ON NullTest (c1 );
GO
Insert into NullTest VALUES (1 );
GO
EXEC sp_allocationMetadata 'nulltest ';
GO

You can use my blog post: Inside The Storage Engine: sp_AllocationMetadata-putting unmarshented system catalog views to work. to obtain The execution script of sp_allocationMetadata.

Let's use the script below to view the pages on the stack and the pages on the non-clustered index respectively:

Copy codeThe Code is as follows:
Dbcc traceon (3604 );
Dbcc page (foo, 1,152, 3); -- page ID from SP output
Where Index ID = 0
Dbcc page (fool, 1,154, 1); -- page ID from SP output
Where Index ID = 2
GO

First, let's look at the result of dumping this page.
Copy codeThe Code is as follows:
Slot 0 Offset 0x60 Length 11
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Memory Dump
@ 0x685DC060


Let's look at the result of a Dump page on a non-clustered index:
Copy codeThe Code is as follows:
Slot 0, Offset 0x60, Length 13, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = <
No null bitmap Memory Dump @ 0x685DC060


Misunderstanding # 6b: NULL bitmap is only used for empty Columns

Error

When a NULL bitmap exists, the NULL bitmap maps each column in the record to one bit, but the smallest unit in the database is byte. Therefore, in order to rounded up to byte, the number of digits of the NULL bitmap may be more than the number of columns. For more information, see Misconceptions around null bitmap size.

Misunderstanding # 6c: adding an additional column to the table will immediately cause SQL Server to modify the data in the table.

Error

SQL Server immediately modifies data entries only when the columns added to the table contain the default value and the default value is not NULL. In short, the SQL Server storage engine records one or more newly added columns that are not reflected in the data records. In this regard, I have a blog post describing this in depth: Misconceptions around adding columns to a table.