This also reduces the CPU cache hit failure (click on this link to see how the CPU cache works and the Mesi protocol). Let's debunk three common myths about null bitmaps.
myth #6a: A null bitmap is not always used
That's right
Even if there is no null-enabled column in the table, the null bitmap persists for the data row (data rows refer to the heap or leaf node of the clustered index). However, null bitmaps are not always valid for index rows (so-called index rows are non-leaf nodes of clustered and nonclustered indexes, and leaf nodes of nonclustered indexes).
The following statement can prove this in a valid way:
Copy Code code 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 go through my blog: Inside the Storage engine:sp_allocationmetadata-putting undocumented system catalog views to work. For Sp_alloc A Ationmetadata implementation script.
Let's look at the pages on the heap and on the nonclustered index separately, using the script below:
Copy Code code as follows:
DBCC Traceon (3604);
DBCC PAGE (foo, 1, 152, 3); --page ID from SP output
where Index ID = 0
DBCC PAGE (foo, 1, 154, 1); --page ID from SP output
where Index ID = 2
Go
First, let's look at the results of the dump on the heap.
Copy Code code as follows:
Slot 0 Offset 0x60 Length 11
Record Type = Primary_record record Attributes = Null_bitmap Memory Dump
@0x685dc060
Then look at the result of a page dump on the nonclustered index:
Copy Code code as follows:
Slot 0, Offset 0x60, Length, Dumpstyle BYTE
Record Type = Index_record record Attributes = <<<<<<<
No null bitmap Memory Dump @0x685dc060
myth #6b: null bitmaps are used only for nullable columns
Error
When a null bitmap exists, a null bitmap corresponds to one bit for each column in the record, but the smallest unit in the database is bytes, so the number of digits of a null bitmap may be more than the number of columns in order to get the whole byte up. For this question. I already have a blog post for this overview, see: Misconceptions around null bitmap size.
Myth #6c: Adding an extra column to a table immediately causes SQL Server to modify data in the table
Error
SQL Server immediately modifies data entries only if the newly added column to the table is a default value and the default value is not NULL. In summary, the SQL Server storage engine records one or more newly added columns that are not reflected in the data record. In this regard, I have a blog more in-depth description of this: misconceptions around adding columns to a table.
