SQL Server 2008 storage structure of the object is stored view

In fact, the most important storage structure of the database objects to the two SQLServer support system view, that sys.partitions and the sys.allocation_units of a SQL Server not publicly support system view sys.system_internals_allocation_units.

The sys.partitions view all tables and indexes in the database for each partition in the table of the corresponding line; sys.indexes one-to-many relationship, depends entirely on the number of partitions of tables and indexes so you can understand. The view is structured as follows:

No.	Column names	Explanation
1	partition_id	Partition ID. Is unique in the database.
2	object_id	This partition an object ID. Each table or view contains at least one partition.
3	index_id	An object of this partition within the index ID.
4	partition_number	Belongs index or the heap starting from 1 partition number. For non-partitioned tables and indexes, this column has the value 1.
5	hobt_id	This partition contains rows of data heap or B-tree ID.
6	rows	About the number of rows in this partition.
7	database_fragment_id	Logo for informational purposes only. Does not provide support. Future compatibility is not guaranteed.
8	data_compression	Directions compressed state for each partition:  0 = NONE  1 = ROW  2 = PAGE
9	data_compression_desc	Directions compressed state for each partition. Possible values ??are NONE, ROW and PAGE

sys.allocation_units view, each allocation unit in the database one row in the table; a table can have four kinds of allocation unit Type: Deleted row data, LOB data, row-overflow data, depending on the structure of the table and line data distribution; such as described in sys.partitions view, tables and indexes may include a number of partition; a table of the number of allocation units = tables and indexes * number of partitions * contains the allocation unit type; sys.allocation_units and sys. partitions is also a one-to-many relationship.

View of the allocation unit also includes the number of pages in the allocation unit allocation has been used and data usage.

No.	Column names	Explanation
1	allocation_unit_id	Allocation unit ID. Is unique in the database.
2	type	The distribution unit type:  0 = delete  1 = inline data (all data types, but other than the LOB data type)  2 = large object (LOB) data  3 = row-overflow data
3	type_desc	A description of the allocation unit type:  DROPPED  IN_ROW_DATA  LOB_DATA  ROW_OVERFLOW_DATA
4	container_id	And assignment unit associated with the storage container ID.  If type = 1 or 3,  The container_id = sys.partitions.hobt_id.  If the type is 2,  The container_id = sys.partitions.partition_id.  0 = marked for deferred dropped allocation unit
5	data_space_id	The allocation unit where the group ID of the file.
6	total_pages	The total number of pages allocated or reserved for the allocation unit.
7	used_pages	The actual total number of pages.
8	data_pages	Contains the following data of the number of pages:  Line data  The LOB data row-overflow data value returned ruled out internal index page and distribution management page.
9	database_fragment_id	Logo for informational purposes only. Does not provide support. Does not guarantee future compatibility

the sys.system_internals_allocation_units view, usage and sys.allocation_units exactly the same, and increased allocation unit tracking management page address information but on the basis of sys.allocation_units, the concept of first_page, root_page, first_iam_page, will be described in subsequent chapters .

No.	Column names	Explanation
1	allocation_unit_id	With sys.allocation_units
2	type	With sys.allocation_units
3	type_desc	With sys.allocation_units
4	container_id	With sys.allocation_units
5	filegroup_id	With sys.allocation_units
6	total_pages	With sys.allocation_units
7	used_pages	With sys.allocation_units
8	data_pages	With sys.allocation_units
9	first_page	Home address (File No. + page number)
10	root_page	Index of the root node address (File No. + page number)
11	first_iam_page	Iam the address of the page (File No. + page number)

We speak for example.

# Div_code img {border: 0px none;}

- Create a data table that contains a BLOB field, at the same time create a clustered index and non-clustered index, and insert three records 
CREATE TABLE test (a INT, b VARCHAR (20), c TEXT); 
CREATE UNIQUE CLUSTERED INDEX idx_test ON test (a); 
The CREATE INDEX ix_test ON test (b); 
INSERT INTO test VALUES (1, 'a', 'aaa') 
INSERT INTO test VALUES (2, 'b', 'bbb') 
INSERT INTO test VALUES (3, 'c', 'ccc') 
SELECT * FROM test 
- According to the table name query object_id 
SELECT name, object_id, parent_object_id, type_desc 
FROM SYS.OBJECTS WHERE NAME = 'TEST' - 2089058478 
- And then query the index view contains two index records in the index view, you can clearly see, that the clustered index and non-clustered index 
SELECT object_id, name, index_id, type, type_desc 
FROM SYS.INDEXES WHERE OBJECT_ID = 2089058478 
- Search partitioned view, you can see the partitioned view contains two records, the clustered index and non-clustered index 
SELECT partition_id, object_id, index_id, partition_number, hobt_id, rows 
FROM SYS.PARTITIONS WHERE OBJECT_ID = 2089058478 
- Check distribution unit view, you can see the partitioned view contains three records, aggregation index and non-clustered index and LOB data 
SELECT allocation_unit_id, type, type_desc, container_id, 
data_space_id, total_pages, used_pages, data_pages 
FROM 
( 
SELECT * FROM SYS.ALLOCATION_UNITS U, SYS.PARTITIONS P 
WHERE U.TYPE IN (1, 3) AND U.CONTAINER_ID = P.HOBT_ID 
AND P.OBJECT_ID = 2089058478 
UNION ALL 
SELECT * FROM SYS.ALLOCATION_UNITS U, SYS.PARTITIONS P 
WHERE u.type is = 2 AND U.CONTAINER_ID = P.PARTITION_ID 
AND P.OBJECT_ID = 2089058478 
) A 
- Finally, query system_internals_allocation_units view, you can see the view with the allocation unit view essentially similar, in addition to three page address 
SELECT allocation_unit_id, type, type_desc, container_id, filegroup_id, 
total_pages, used_pages, data_pages, 
first_page, root_page, first_iam_page 
FROM 
( 
SELECT * FROM sys.system_internals_allocation_units U, SYS.PARTITIONS P 
WHERE U.TYPE IN (1, 3) AND U.CONTAINER_ID = P.HOBT_ID 
AND P.OBJECT_ID = 2089058478 
UNION ALL 
SELECT * FROM sys.system_internals_allocation_units U, SYS.PARTITIONS P 
WHERE u.type is = 2 AND U.CONTAINER_ID = P.PARTITION_ID 
AND P.OBJECT_ID = 2089058478 
) A 

 

# Div_code img {border: 0px none;}

- Create a data table containing the BLOB field 
CREATE TABLE heaptest (a INT, b VARCHAR (20), c TEXT); 
INSERT INTO heaptest VALUES (1, 'a', 'aaa') 
INSERT INTO heaptest VALUES (2, 'b', 'bbb') 
INSERT INTO heaptest VALUES (3, 'c', 'ccc') 
SELECT * The FROM heaptest in 
SELECT name, object_id, parent_object_id, type_desc 
FROM SYS.OBJECTS WHERE NAME = 'heaptest' - 2105058535 
SELECT object_id, name, index_id, type, type_desc 
FROM SYS.INDEXES WHERE OBJECT_ID = 2105058535 
SELECT partition_id, object_id, index_id, partition_number, hobt_id, rows 
FROM SYS.PARTITIONS WHERE OBJECT_ID = 2105058535 
SELECT allocation_unit_id, type, type_desc, container_id, 
data_space_id, total_pages, used_pages, data_pages 
FROM 
( 
SELECT * FROM SYS.ALLOCATION_UNITS U, SYS.PARTITIONS P 
WHERE U.TYPE IN (1, 3) AND U.CONTAINER_ID = P.HOBT_ID 
AND P.OBJECT_ID = 2105058535 
UNION ALL 
SELECT * FROM SYS.ALLOCATION_UNITS U, SYS.PARTITIONS P 
WHERE u.type is = 2 AND U.CONTAINER_ID = P.PARTITION_ID 
AND P.OBJECT_ID = 2105058535 
) A 

SELECT allocation_unit_id, type, type_desc, container_id, filegroup_id, 
total_pages, used_pages, data_pages, 
first_page, root_page, first_iam_page 
FROM 
( 
SELECT * FROM sys.system_internals_allocation_units U, SYS.PARTITIONS P 
WHERE U.TYPE IN (1, 3) AND U.CONTAINER_ID = P.HOBT_ID 
AND P.OBJECT_ID = 2105058535 
UNION ALL 
SELECT * FROM sys.system_internals_allocation_units U, SYS.PARTITIONS P 
WHERE u.type is = 2 AND U.CONTAINER_ID = P.PARTITION_ID 
AND P.OBJECT_ID = 2105058535 
) A 

 

from http://www.databaseskill.com/2355872/