Before moving on to see anatomy of different types of pages, lets spend some time to understand

The relationship among sys.objects, sys.partitions, sys.allocation_units and sys.system_internals_allocation_units system views.

Case 1 (simple table with record size less than 8000 bytes and no LOB data) :

When a table is created (Heap), by default all its pages are aligned in  one partition. Partition is a unit of data organization.

Whenever non clustered indexes are added, partitions are framed for each non clustered index and all index pages related to non clustered indexes are logically assigned to respective partitions.

Based on data it holds, a partition can have 3 kind of page groups like IN_ROW_DATA pages, ROW_OVERFLOW_DATA pages and LOB_DATA Pages. These page groups are in other term called allocation units.

Difference among  IN_ROW_DATA, ROW_OVERFLOW_DATA and LOB_DATA Pages

1.     IN_ROW_DATA (default) : If a table is relatively simple in desin (meaning record size is less than 8000 and no LOB data types are present), all records are stored in a pages refered as IN_ROW_DATA pages.

2.     ROW_OVERFLOW_DATA : assume that a table is created with record size 12000 bytes having 4 varchar data types of size 4000 bytes. Whenever user inserts a record with size greater than 8000 (page size is 8K), then the excess data is moved to ROW_OVERFLOW_DATA pages. In simple terms, ROW_OVERFLOW_DATA pages will come in  to picture only when the row size exceed page maximum limit.

3.     LOB_DATA : LOB data like text not stored along with data pages. LOB data is stored in special pages called LOB_DATA pages. 16 byte pointer in data page will be used to refer LOB_DATA page.

If record size of a table is less than 8000 and there is no LOB data type is present, all records are placed in IN_ROW_DATA pages.

Lets examine that by creating a simple table (with record size less than 8000 and with no LOB data types).

CREATE TABLE tExample1(

strEmpCode char(6),

strName varchar(100),

strDept varchar(10),

strCity varchar(100))

GO

Once the table is created, table related informations will immediately reflect in many system views like sys.objects, sys.indexes, sys.indexes, sys.columns etc.

object_id would be the reference in most of the system views.

once the table named tExample1 is created, an unique object_id will be assigned to the table object.

Here is  the query to list object_id and object related informations using a below query.

select object_id, name, type_desc from sys.objects where name = 'tExample1'

go

Sample output: (note that object_id may be different in other systems)

as mentioned above, a table is logically aligned with a  container in other words partition.

Below query displays container details specific to table named tExample1.

select object_name(object_id), object_id, partition_id, hobt_id, partition_number, rows

from sys.partitions

where object_name(object_id) = 'tExample1'

go

Sample output:

Object_id : Object id of table tExample1

Partition_id : id of partition/container hold data

Hobt_id : Id of the data heap or B-tree that contains the rows for this partition. most of the cases this is
                 same as partition_id.

[a Table à can have one or more Paritions à each partition can have max of 3 Allocation units]

Here is the query to list Allocation_unit details of partition 72057594038648800

select *

from sys.allocation_units

where container_id = 72057594038648832

go

 Here container_id is referring partition and allocation_unit_id refers IN_ROW_DATA allocation_unit.

Let’s combine everything in a single query

select so.name, so.object_id, sp.index_id, sp.partition_id, sp.hobt_id, sa.allocation_unit_id, sa.type_desc, sa.total_pages

from sys.objects so

      inner join sys.partitions sp on so.object_id = sp.object_id

      inner join sys.allocation_units sa on sa.container_id = sp.hobt_id

 where so.name = 'tExample1'

go

Sample output :

Case 2:

Here is an example to demonstrate Object, Partition and Allocation_unit concept in detail

Lets create a table having an LOB column and record size is greater than 8000.

create table tExample2(

strId int identity(1,1) PRIMARY KEY,

strdeptcode char(10),

strComment1 varchar(4000),

strComment2 varchar(4000),

strComment3 varchar(4000),

strLongText varchar(max))

go

this is to demonstrate use of different Allocation_unit types

create nonclustered index nci_tExample2_strdeptcode on tExample2(strDeptCode)

go

Lets insert 2 records in tExample2 table.

insert into tExample2(strDeptCode, strComment1, strComment2, strComment3, strLongText)

values('DEPT01',replicate('a',4000),replicate('b',4000), replicate('c',4000), replicate('d',10000))

GO 2

Note that, now record size is 8000 and we have inserted a value in LOB data type.

Here is the query to list all partitions, and allocation units allocated for tExample2 table

select so.name, so.object_id, sp.index_id, sp.partition_id, sp.hobt_id, sa.allocation_unit_id, sa.type_desc, sa.total_pages

from sys.objects so

      inner join sys.partitions sp on so.object_id = sp.object_id

      inner join sys.allocation_units sa on sa.container_id = sp.hobt_id

 where so.name = 'tExample2'

go

here is the sample output with explaination:

Below picture shows how a table data is organized with many partitions and allocation units :

Conclusion:

In this article, we have seen the relationship among a table, partitions and allocation units. And also we have seen how data is organized with multiple partitions and allocation units.

In next article, I am going to cover below topics

1.       How to identify starting point (page) of a table

2.       And an introduction to architecture of a Page