BLOB Types

The BLOB type is a form of VARBINARY string type. (The BLOB type has no connection to columnstore segments, also known as blobs. See Understanding Sort Key Selection for more information on columnstore blobs.) The data for this data type is stored in memory for rowstore tables and the main table structure holds pointers to the memory locations.

The total space consumed by a particular string value is:

• The byte length of the string.

• An 8-byte overhead for the pointer.

• A 4-byte overhead for storing the length. There is no 4-byte overhead if the field is nullable.

While BLOB data types operate the same for most English-language alphanumeric strings, there are several important differences in their behavior. For example, BLOB fields store data verbatim, regardless of whether they are valid Unicode strings. Sorting and comparing a BLOB uses the exact binary representation.

To reduce memory fragmentation, memory for variable-sized data is allocated from a fixed list in multiples of eight (16, 32, 40, etc.), but not every multiple of eight. Variable data of other lengths are rounded up to the next nearest multiple of eight within the list. For example, a ten (10) byte string would be allocated sixteen (16) bytes.

Unlike variable-length TEXT types, comparison operators treat trailing spaces differently when used with a variable-length BLOB type. Also, BLOB type uses the binary character set while TEXT uses UTF8 by default, but can be changed.