August 24, 2004 at 2:16 pm
Can someone point me to where I can study the architecture SQL Server uses to store physical rows in a block. I interested in the struct that identifies a row header and the bits/bit mask which identify each columns NULL state. I'm not looking for whether a column is NULLABLE, I'm looking for the Bit Mask which states that a particular row's column is in_fact null.
August 24, 2004 at 4:13 pm
Take a look at "Inside Microsoft SQL Server 2000" by Kalen Delaney. Chapter 6, specifically pages 252 - 254 will give you the structure.
Steve
August 25, 2004 at 8:57 am
Thanks!
After reading this it is obvious that a BIT datatype actually uses 2 bits, one for the data and one for the Null Column BitMap. Someone who is using bits heavily for space savings would need to know this. If you are dealing with 100s of millions of rows and each row is using 8 BIT datatypes then instead of having 100s of millions of "bit type" bytes you will actually have 100s of millions of "bit type" bytes X 2 where the extra byte is in the row header.
This is a great reason to use bit mapping (Bit-Wise operations) instead of the BIT datatype. Additionally, with bit mapping (into a tinyint, smallint, int) you can still index the bit map into an Index where BIT datatypes cannot be part of an index.
On the side of BIT datatypes, they really have three states available; NULL, 0 and 1; not just 0 and 1. So now we can have Yes, No and Maybe.
Just some thoughts.
Thanks again.
August 26, 2004 at 9:18 am
Look at the book Inside SQL Server 2000 by Microsoft Press. This book gives a complete overview of how data pages / index pages are stored in SQL Server, including diagrams, etc. Also it reveals undocumented commands for dumping the page structure, etc.
Glenn Block
Principal Software Architect
Streamline Solutions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply