SQL Server stores data in heaps or b-tree structures. Heaps are unordered set and balanced trees are ordered by their keys.
Heaps and b-tree use collection of pages within the structure and it’s called allocation units.
- IN_ROW_DATA -> contains all data.
- LOB_DATA -> structure for large objects used to stored in xml, varchar(max), nvarchar(max), varbinary(max) data types.
- ROW_OVERFLOW_DATA -> when the data type is variable like varchar and nvarchar exceed 8,060 bytes size limit per row.
To see this information is used the system view allocation_units.
SELECT OBJECT_NAME(I.object_id) , I.name , AU.total_pages , AU.used_pages , AU.data_pages , P.rows , AU.type_desc FROM sys.allocation_units AS AU INNER JOIN sys.partitions AS P ON AU.container_id = P.partition_id INNER JOIN sys.indexes AS I ON I.index_id = P.index_id AND I.object_id = P.object_id WHERE P.object_id = OBJECT_ID('TabelaDeTamanhoVariavel')
Column name | Data type | Description |
---|---|---|
allocation_unit_id | bigint | ID of the allocation unit. Is unique within a database. |
type | tinyint | Type of allocation unit:
0 = Dropped 1 = In-row data (all data types, except LOB data types) 2 = Large object (LOB) data (text, ntext, image, xml, large value types, and CLR user-defined types) 3 = Row-overflow data |
type_desc | nvarchar(60) | Description of the allocation unit type:
|
container_id | bigint | ID of the storage container associated with the allocation unit.
If type = 1 or 3, container_id = sys.partitions.hobt_id. If type is 2, then container_id = sys.partitions.partition_id. 0 = Allocation unit marked for deferred drop |
data_space_id | int | ID of the filegroup in which this allocation unit resides. |
total_pages | bigint | Total number of pages allocated or reserved by this allocation unit. |
used_pages | bigint | Number of total pages actually in use. |
data_pages | bigint | Number of used pages that have:
Value returned excludes internal index pages and allocation-management pages. |