An allocation unit can be one or multiple pages that are assigned to one of three allocation unit data types. Those types are “IN_ROW_DATA,” “ROW_OVERFLOW_DATA,” and “LOB_DATA.” An allocated page, or pages, will only ever represent one type of allocation unit data type, but the pages can be in multiple extents and each of these extents could have a mix of the three allocation unit data type pages.
An allocation unit data type allows a database to store row data efficiently. A table can have multiple partitions which allows for faster storage and access of data. Each partition has either a heap or a B-tree. Each heap or B-tree uses only the three allocation unit data types to store and track data.
However, that does not mean that there are only three allocation units per table. Since each table can have 1,000 indexes, each index can have 1,000 partitions, and each partition can use all three allocation unit data types, there are three million allocation units available for each table.
The three different allocation unit data types store and manage data differently. Here is a brief explanation of what each does:
“IN_ROW_DATA” is an allocation unit data type where all the data can be stored in a single row. It also means that the data in this row does not exceed 8,060 bytes. A partition will always have at least one page for this allocation unit and there will always be information in this table.
“ROW_OVERFLOW_DATA” is used if a data row has variable length columns and exceeds 8kb. There will not be a page for this unit until there is a row that exceeds 8kb. SQL will allocate a page to ROW_OVERFLOW_DATA and move the widest column from the IN_ROW_DATA allocation unit to this unit’s page. A pointer to the columns new location will be left on the original page. There will be a pointer for each column that has to be moved until the row is under 8kb.
“LOB_DATA” is used for Large Object data (LOB). LOB data can be a wide range of different data types, including variable data, but is mostly data that will remain large by nature and expected to always exceed 8kb. If a table has at least one LOB, it will have a LOB_DATA allocation unit.