SQL Server BLOB Storage

SQL Server stores standard data types such as int, char, and varchar directly inside each row. This way of storing data limits the maximum capacity of each data type to 8000 bytes—slightly less than SQL Server's maximum row size of 8060 bytes. Although this capacity is more than adequate for most data types, the limitation is a problem for binary large object (BLOB) data, which can be significantly larger than 8KB. Fortunately, Microsoft designed SQL Server to store BLOBs somewhat differently than it does the more common data types. Figure A illustrates how SQL Server 2000 and 7.0 accommodate BLOB storage.

SQL Server's internal binary object storage mechanism changed significantly after SQL Server 6.5, which stores binary data as a linked list. But as Figure A shows, SQL Server 2000 and 7.0 store BLOB data on a different data page than the rest of the row's data. SQL Server stores the BLOB data as a collection of 8KB pages that it organizes in a B-tree structure. Each row's BLOB column contains a 16-byte pointer to the root B-tree structure that tracks the various blocks of data that make up the BLOB. If the amount of binary data is less than 64 bytes, SQL Server stores it as part of the root structure itself. Otherwise, the root structure consists of a series of pointers that SQL Server uses to locate the blocks of data that make up the binary object. The primary advantage of the new type of BLOB storage in SQL Server 2000 and SQL Server 7.0 is that it lets applications access the middle or end of the BLOB data much more quickly.