Anatomy of SQL Server article 5 reading Bits data in OrcaMDF)

Anatomy The fifth article of SQL Server reads Bits-type data (translated) improve. dkreading-bits-in-orcamdfBits type storage is very different from other SQL server fixed-length data types. Generally, all fixed-length columns are displayed. The field data in the fixed-length data section of a record is always one by one.

Anatomy of SQL Server article 5 OrcaMDF reads Bits type data storage of the http://improve.dk/reading-bits-in-orcamdf/ Bits type is very different from other SQL server fixed-length data storage. Generally, all fixed-length columns are displayed. The field data in the fixed-length data section of a record is always one by one.

Anatomy of SQL Server article 5 reading Bits data in OrcaMDF)

Http://improve.dk/reading-bits-in-orcamdf/

Bits type storage is very different from other SQL server fixed-length data types. Generally, all fixed-length columns are displayed. The field data of the fixed-length data in a record is always one by one.

The minimum data unit that can be written to a disk is one byte. The naive way to store BIT data is to use an entire (byte @) to store every bit, it is very easy to use common formats to interpret bit-type data.

However, this will waste some space, just like a null bitmap. If a table has only three columns, it will be a waste to store the null bitmap with one byte, because the other five are not used.

@: In this article, we use a byte.

How is the internal bit type stored in the record?

The values of some bit-type columns are stored in one byte and can be up to eight bits. Generally, we will define the following table:

CREATE TABLE BitTest(    A bit    B bit    C bit    D int)

The data of the Fixed Length part of the record takes 5 bytes and 4 bytes to store the int column. The other bytes store the data of the, B, and C columns, only three bytes are used.

Let's add some columns.

CREATE TABLE BitTest(    A bit    B bit    C bit    D int    E bit    F bit    G bit    H smallint    I bit    J bit    K bit)

Columns E to G should be stored behind columns D, but they will continue to use the first bit byte until the first bit byte uses all the bit space.

The figure below shows that the H column (smallint) is directly stored behind the D column, while the D column is followed by the new bit byte storing the K column, because the first bit byte is full

The status we need to know when reading the bit type in the row record

Obviously, we cannot only read the value of one field at a time. When we read the fixed-length data type, we also need to read the fixed-length data offset pointer.

When reading, we need to indicate the status of the field in which one of the currently read bytes belongs, and then read a new bit byte.

Let me introduce the RecordReadState class.

public class RecordReadState{    // We start out having consumed all bits as none have been read    private int currentBitIndex = 8;    private byte bits;    public void LoadBitByte(byte bits)    {        this.bits = bits;        currentBitIndex = 0;    }    public bool AllBitsConsumed    {        get { return currentBitIndex == 8; }    }    public bool GetNextBit()    {        return (bits & (1 << currentBitIndex++)) != 0;    }}

Currently, the RecordReadState class only needs to process bits, but in the future, I may create a BitReadState class to save the read status.

The RecordReadState class stores a byte used as a pointer to indicate the location of the next available bit in the byte. If the byte is used up, it stores all the BIT data.

(CurrentBixIndex = 8 (0-7 being the available bits), the method AllBitsConsumed returns true, indicating that we need to read a new bit byte

The GetNextBit method simply reads the current bit from the bit byte, and then adds the value of currentBitIndex (bit index) to 1.

Demo

using NUnit.Framework;using OrcaMDF.Core.Engine.Records;namespace OrcaMDF.Core.Tests.Engine.Records{    [TestFixture]public class RecordReadStateTests{        [Test]public void General(){var state = new RecordReadState();// No bits availableAssert.IsTrue(state.AllBitsConsumed);state.LoadBitByte(0xD2); // 11010010// Bits availableAssert.IsFalse(state.AllBitsConsumed);// Reading bit valuesAssert.IsFalse(state.GetNextBit());Assert.IsTrue(state.GetNextBit());Assert.IsFalse(state.GetNextBit());Assert.IsFalse(state.GetNextBit());Assert.IsTrue(state.GetNextBit());Assert.IsFalse(state.GetNextBit());Assert.IsTrue(state.GetNextBit());// One bit leftAssert.IsFalse(state.AllBitsConsumed);Assert.IsTrue(state.GetNextBit());// Bits exhausted, ready for next byteAssert.IsTrue(state.AllBitsConsumed);}}}

SqlBit implementation

Once the status is read, we can implement the SqlBit type.

public class SqlBit : ISqlType{    private readonly RecordReadState readState;    public SqlBit(RecordReadState readState)    {        this.readState = readState;    }    public bool IsVariableLength    {        get { return false; }    }    public short? FixedLength    {        get        {            if (readState.AllBitsConsumed)                return 1;            return 0;        }    }    public object GetValue(byte[] value)    {        if(readState.AllBitsConsumed && value.Length != 1)            throw new ArgumentException("All bits consumed, invalid value length: " + value.Length);        if (value.Length == 1)            readState.LoadBitByte(value[0]);        return readState.GetNextBit();    }}

SqlBit passes in a read state in the constructor. The read state indicates the range of read operations on the current record. Note that the fixed length must be based on the current AllBitsConsumed value in the read state.

If all the bits in the byte are occupied, it means that the entire byte needs to be read. if (readState. AllBitsConsumed) returns 0, it means that the entire byte does not need to be read, but the GetValue method will still be called.

The GetValue method verifies a situation where readState. AllBitsConsumed returns true, proving that the bit byte has data stored in it, but the returned Length of value. Length is 0, which proves that there is a problem.

If we read a value, we will request the read state to load a new bit byte. Then, we can call the GetNextBit method to return the Current bit of the read state.

Related tests

using NUnit.Framework;using OrcaMDF.Core.Engine.Records;using OrcaMDF.Core.Engine.SqlTypes;namespace OrcaMDF.Core.Tests.Engine.SqlTypes{    [TestFixture]    public class SqlBitTests    {        [Test]        public void GetValue()        {            var readState = new RecordReadState();            var type = new SqlBit(readState);            // No bytes read - length is one            Assert.AreEqual(1, type.FixedLength);            // Load byte and check length is 0            readState.LoadBitByte(0xD2);            Assert.AreEqual(0, type.FixedLength);            Assert.IsFalse((bool)type.GetValue(new byte[0]));            Assert.IsTrue((bool)type.GetValue(new byte[0]));            Assert.IsFalse((bool)type.GetValue(new byte[0]));            Assert.IsFalse((bool)type.GetValue(new byte[0]));            Assert.IsTrue((bool)type.GetValue(new byte[0]));            Assert.IsFalse((bool)type.GetValue(new byte[0]));            Assert.IsTrue((bool)type.GetValue(new byte[0]));            // One bit left - length should still be 0            Assert.AreEqual(0, type.FixedLength);            Assert.IsTrue((bool)type.GetValue(new byte[0]));            // All bits consumed - length should be 1            Assert.AreEqual(1, type.FixedLength);        }    }}

Article 5