A table row is the data of one leaf entry of the table's tree. It holds only the values of the columns that are set, in three groups by column id, and cannot be read without the column definitions of the catalog. Everything is little-endian.
Reading a column
| Column ids | Kind | Where the value is |
|---|---|---|
| 1 – 127 | Fixed-size | At the column's record offset (from the catalog), if its id is not more than the record's last fixed column. A set bit in the null bitmap means NULL. |
| 128 – 255 | Variable-size | Between the end offset of the previous variable column and its own, if its id is not more than the record's last variable column. The top bit of the end offset (0x8000) means NULL. |
| 256 and up | Tagged | Found by its id in the tag array. A column that is not in the array is NULL (or has its catalog default). |
A column that is NULL and has a default value in the catalog reads as the default when it is not stored.
Layout
- Header – 4 bytes: the last fixed column id (1 byte), the last variable column id (1 byte; 127 when there are none) and the variable data offset (2 bytes)
- Fixed columns – each at its record offset; a NULL column's bytes are filler (often 0x2a)
- Null bitmap – one bit for each fixed column up to the last, rounded up to whole bytes, the lowest id first
- Variable column offsets – at the variable data offset: one 16-bit end offset for each variable column from id 128 to the last
- Variable column data – the values one after another
- Tag array – one entry of four bytes for each tagged column that is set: its id (16 bits) and an offset (16 bits) from the start of the array to its data
- Tagged column data – each value runs to the next one's offset, the last to the end of the record
The tag array's first offset is also the array's length, so the number of tagged columns is that offset divided by four. In a tag entry's offset, bit 0x4000 says that the data starts with a flags byte and bit 0x8000 marks an explicit NULL. In the files with 16 and 32 KiB pages every tagged value has the flags byte whether or not bit 0x4000 is set.
Tagged values
The flags byte tells how to read the rest:
| Flag | Meaning |
|---|---|
| 0x01 | Variable-size data (set on text and binary values). |
| 0x02 | Compressed: the data starts with a compression header (compression). |
| 0x04 | Long value: the data is the id of an entry of the table's long value tree, four or eight bytes. |
| 0x08 | Multi-value: the data is an array of 16-bit offsets, counted from the start of the data, one for each value, then the values. The first offset gives the array's size, so the number of values is half of it. |
| 0x10 | With 0x08: two values, the first 16 bits giving the size of the first. |
A value of zero length is a column that is set to an empty value, not NULL.
Column types
| Id | Name | Stored as |
|---|---|---|
| 1 | Bit | One byte, 0 or 1. |
| 2 | Unsigned byte | One byte. |
| 3 | Short | 16-bit signed integer. |
| 4 | Long | 32-bit signed integer. |
| 5 | Currency | 64-bit signed integer; ten thousandths of the unit. |
| 6, 7 | IEEE single, double | 32 and 64-bit floating point. |
| 8 | Date and time | A 64-bit floating point number of days since 30 December 1899 (an OLE automation date). Windows stores FILETIMEs in Currency, Long long and Binary columns instead. |
| 9 | Binary | Up to 255 bytes. A fixed-size column may also be Binary or Text of its declared size. |
| 10 | Text | Up to 255 bytes of text in the column's code page (1200 is UTF-16LE). |
| 11, 12 | Long binary, long text | Tagged; up to 2 GiB, in the record or in the long value tree. |
| 13 | SLV | Streaming large value (obsolete). |
| 14 | Unsigned long | 32-bit unsigned integer. |
| 15 | Long long | 64-bit signed integer. |
| 16 | GUID | 16 bytes, the first three groups little-endian. |
| 17 | Unsigned short | 16-bit unsigned integer. |
Some Windows databases hold UTF-16 in a column whose code page says 1252; the explorer recognises text whose every second byte is zero.
Links
Reference
- Extensible Storage Engine (ESE) Database File (EDB) format – data definitions and tagged data
- JET_COLTYP – Microsoft's column types