Allocation unit in sql server

When we are creating a table in sql server, it creates a whole hierarchy Object, partition and Allocation units. Unit store the data type.

There are 3 types of allocation units in sql server

Ø  In Row Allocation unit : it store the fix length data type like (char, int, float , date time etc)

Ø  Row Overflows Allocation Unit: it store the variable length data type like (nvarchar, varchar etc)

Ø  Lob Data Allocation Unit : it store the lob data type like (ntext, xml, Image etc)

Let’s see the example

Here I am creating a table and see that what happen inside the database.

create table tblAllocationUnitTest (ID int, FName char(200), LName char(200), Mob Char(200))

Once we create the table it store in the sys.object and at least it create a partition.

select * from sys.objects where object_id=885578193

  

It must create a partition

select * from sys.partitions where object_id=885578193

   

We know that data types are store in units. We can see it on the sys.allocation_units

select * from sys.allocation_units where container_id=72057594039304192 --it is partition_id

  

Currently in the table we have only fix length data type.

Now I am adding the variable data type in the table

Alter table tblAllocationUnitTest Add eAdd varchar(500)

  

Now I am adding the LOB data type.

Alter table tblAllocationUnitTest Add ad varchar(max)