表的物理存储结构

 

一通过视图查询表的相关信息

--关于表相关视图

sys.tables

sys.indexes

sys.columns

sys.check_constraints

sys.default_constraints

sys.key_constraints

sys.allocation_units

sys.partitions

--//TODO非文档目录视图

sys.system_internals_allocation_units

 

--创建测试表

CREATE TABLE dbo.employee (

                  emp_lname   varchar(15) NOT NULL,

                  emp_fname   varchar(10) NOT NULL,

                  address     varchar(30) NOT NULL,

                  phone       char(12)     NOT NULL,

                  job_level   smallint     NOT NULL

)

 

 

select * from sys.tables where name = 'employee' and type = 'U'

 

select * from sys.columns where object_id = OBJECT_ID('employee')

 

 

SELECT object_id, name, index_id, type_desc

FROM sys.indexes

WHERE object_id=object_id('dbo.employee')

 

SELECT *

FROM sys.partitions

WHERE object_id=object_id('dbo.employee')

 

--信息架构视图

SELECT * FROM DEMO.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = N'employee'

 

select * from sys.indexes where object_id = OBJECT_ID('Sales')

 

SELECT object_name(object_id) AS name,

    partition_id, partition_number AS pnum, rows,

    allocation_unit_id AS au_id, type_desc as page_type_desc,

    total_pages AS pages

FROM sys.partitions p JOIN sys.allocation_units a

   ON p.partition_id = a.container_id

WHERE object_id=object_id('dbo.employee')

 

ALTER TABLE dbo.employee ADD resume_short varchar(8000)

ALTER TABLE dbo.employee ADD resume_long text

 

 

SELECT convert(char(8),object_name(i.object_id)) AS table_name,

    i.name AS index_name, i.index_id, i.type_desc as index_type,

    partition_id, partition_number AS pnum, rows,

    allocation_unit_id AS au_id, a.type_desc as page_type_desc,

    total_pages AS pages

FROM sys.indexes i JOIN sys.partitions p

        ON i.object_id = p.object_id AND i.index_id = p.index_id

    JOIN sys.allocation_units a

        ON p.partition_id = a.container_id

WHERE i.object_id=object_id('dbo.employee')

 

二表的内部存储结构

在SQL Server中对于表中的每条记录并没有一个内部的全局行编号。则是使用文件号、页号、以及页面上的插槽号的组合来唯一标识表中的每条记录。

如：PAGE: (1:73) Slot 0

 

-- DBCC Page参数

DBCC Page ({dbid|dbname}, filenum, pagenum [,printopt])

dbid          包含页面的数据库ID dbname    包含页面的数据库的名称 filenum      包含页面的文件编号 pagenum  文件内的页面 printopt    可选的输出选项；选用其中一个值：                   0:默认值，输出缓冲区的标题和页面标题                   1:输出缓冲区的标题、页面标题(分别输出每一行)，以及行偏移量表                   2:输出缓冲区的标题、页面标题(整体输出页面)，以及行偏移量表                   3:输出缓冲区的标题、页面标题(分别输出每一行)，以及行偏移量表；

                     每一行后跟分别列出的它的列值

 

 

 

--创建数据

CREATE DATABASE DEMO

GO

 

--创建测试数据，并插入测试数据

USE DEMO

GO

create table t (col1 int primary key,col2 varchar(10))

go

insert into t select 1,'AAAAA'

insert into t select 3,'CCCCC'

insert into t select 2,'BBBBB'

go

 

--非文档DBCC命令 查看数据库的分区信息

dbcc extentinfo(DEMO,t)

 

--打开跟踪标记

dbcc traceon(3604)

 

--非文档DBCC命令查看页面信息

dbcc page(DEMO,1,73,1)

输出以下信息：

DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。

 

PAGE: (1:73)

 

 

BUFFER: --//TODO：缓冲区信息

--缓冲区信息显示了给定页面的缓冲信息。在此buffer是指一种内存中的结构，用于管理页面，此段的信息仅当该页面处于内存中才有意义。

 

BUF @0x02E0EF50

 

bpage = 0x06C08000                   bhash = 0x00000000                   bpageno = (1:73)

bdbid = 35                           breferences = 0                      bUse1 = 2491

bstat = 0x1c0010b                    blog = 0x212121bb                    bnext = 0x00000000

 

PAGE HEADER: --//TODO：页面报头共96个字节

 

 

Page @0x06C08000

 

m_pageId = (1:73) --这个页面在数据库中所属的文件号和页面编号                

 m_headerVersion = 1                 

m_type = 1

m_typeFlagBits = 0x4                

m_level = 0  --页面在索引中的级别（对于叶子页面总是0）

 m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 82    

m_indexId (AllocUnitId.idInd) = 256 

Metadata: AllocUnitId = 72057594043301888    --包含所属页面的分配单元的ID                      

Metadata: PartitionId = 72057594038321152     --该页面所属分区的ID                            

Metadata: IndexId = 1                --该页面的INDEX ID

Metadata: ObjectId = 2073058421    --该页面所属对象的ID   

m_prevPage = (0:0)     -–上个页面                

m_nextPage = (0:0)     –-下个页面

pminlen = 8              --记录定长部分的总字节数                      

m_slotCnt = 3           --页面上已经使用的插槽（记录）数  

m_freeCnt = 8030      --页面上的空闲字节数

m_freeData = 156       --该页面的第一块空闲空间的字节偏移量

m_reservedCnt = 0      --所有事物保留的字节数             

m_lsn = (27:80:2)      –-用于修改和更新页面的的值

m_xactReserved = 0     --最新开始的事物保留的字节数             

m_xdesId = (0:0)                    

m_ghostRecCnt = 0

m_tornBits = 0                      

 

--分配状态

Allocation Status

 

GAM (1:2) = ALLOCATED               

SGAM (1:3) = ALLOCATED              

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                        

DIFF (1:6) = CHANGED --用于标识该页面是否更改，用于差异备份

ML (1:7) = NOT MIN_LOGGED           

 

DATA: --//TODO：行内数据的数据行最大为8060个字节

 

--//TODO：从输出的信息可以看到这张表一行记录所用到的字节数为20个字节

Slot 0, Offset 0x60, Length 20, DumpStyle BYTE

 

Record Type = PRIMARY_RECORD         Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

 

Memory Dump @0x39F4C060

--//TODO：第一行数据    insert into t select 1,'AAAAA'

00000000:   30000800 01000000 0200fc01 00140041 †..............A        

00000010:   41414141 † ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?AAAA                    

 

Slot 1, Offset 0x88, Length 20, DumpStyle BYTE

 

Record Type = PRIMARY_RECORD         Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

 

Memory Dump @0x39F4C088

--//TODO：第二行数据   insert into t select 2,'BBBBB'

00000000:   30000800 02000000 0200fc01 00140042 †..............B        

00000010:   42424242 † ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?BBBB                    

 

Slot 2, Offset 0x74, Length 20, DumpStyle BYTE

 

Record Type = PRIMARY_RECORD         Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

 

Memory Dump @0x39F4C074

--//TODO：第三行数据insert into t select 3,'CCCCC'

00000000:   30000800 03000000 0200fc01 00140043 †..............C        

00000010:   43434343 † ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?CCCC                    

OFFSET TABLE: --//TODO：行偏移矩阵又称偏移表

Row - Offset                        

2 (0x2) - 116 (0x74)                

1 (0x1) - 136 (0x88)                

0 (0x0) - 96 (0x60)                 

--//TODO：这个偏移表表示了记录在页面上的逻辑顺序。比如：如果一张表上有聚集索引，SQL Server就按照聚集索引来存储记录。这并不意味记录在页面上的物理存储是按照聚集索引键值的顺序来的。由偏移表给出的信息可以看出表中的数据的逻辑顺序与物理顺序是不一致的；如果你删除掉主键或创建聚集索引，则物理顺序与逻辑顺序将一致。

 

DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员郭强联系。

 

---------------------------------------------

-- 1 查看数据库最原始的创建日期

---------------------------------------------

select create_date from sys.databases where name = 'DEMO'

--2008-06-28 09:19:11.687

DBCC Page(DEMO,1,9,3)

 

可以看到以下信息：

 dbi_crdate = 2008-06-28 09:19:11.543

 

--2 查看delete 是否真正删除数据

 

delete from t

 

select * from t

 

dbcc page(DEMO,1,73,1)

 

--//TODO：可以看到如下信息：

原来的m_slotCnt = 3

现在为m_slotCnt = 1

 

--//TODO：只有一条记录

OFFSET TABLE:

 

Row - Offset                        

0 (0x0) - 116 (0x74)   

 

--插入三条记录

insert into t select 4,'DDDDD'

insert into t select 5,'EEEEE'

insert into t select 6,'FFFFF'

go

 

select * from t

 

--查看数据库的分区信息

dbcc extentinfo(DEMO,t)

 

--打开跟踪标记

dbcc traceon(3604)

 

--查看页面信息

dbcc page(DEMO,1,73,1)

 

--//TODO：从输出的信息可以看出该页的内容并没有全部删除，而是保存了一行记录，下次插入数据时，并不是从页面的最开始分配空间。

 

-- 删除掉数据

truncate table t

 

 

-- 删除表

drop table t

 

--//TODO：从输出的信息可以看出，truncate 和drop 也同样没有立刻删除数据

 

--3 差异备份是如何实现的

 

--//TODO：差异备份主要寻找DIFF (1:6) = CHANGED的页面 如果做完全备份后这些标记将改为DIFF (1:6) = NOT CHANGED

 

 

 

三表物理存储中的数据分析

1 所有列都是定长的字段

drop table test

 

CREATE TABLE test

(

Col1 int         NOT NULL,

Col2 char(5)     NOT NULL,

Col3 char(5)     NOT NULL,

Col4 char(5)     NULL,

Col5 int         NULL,

Col6 char(5)     NOT NULl

)

 

insert into test values(1,'AAAAA','BBBBB',NULL,NULL,'CCCCC')

 

dbcc extentinfo(demo,test)

 

dbcc traceon(3604)

dbcc page(demo,1,119,1)

 

 

SELECT object_name(object_id) AS name,

    rows, type_desc as page_type_desc,

    total_pages AS pages, first_page

FROM sys.partitions p JOIN sys.system_internals_allocation_units a

   ON p.partition_id = a.container_id

WHERE object_id=object_id('dbo.test')

 

select dbo.uf_convert_page_nums (0x770000000100)

 

DBCC IND(DEMO,test,-1)

 

 

 

SELECT c.name AS column_name, column_id, max_inrow_length,

        pc.system_type_id, leaf_offset

 FROM sys.system_internals_partition_columns pc

    JOIN sys.partitions p

      ON p.partition_id = pc.partition_id

    JOIN sys.columns c

         ON column_id = partition_column_id

            AND c.object_id = p.object_id

WHERE p.object_id=object_id('test')

 

 

PAGE: (1:119)

 

 

BUFFER:

 

 

BUF @0x02E63450

 

bpage = 0x08F88000                   bhash = 0x00000000                   bpageno = (1:119)

bdbid = 35                           breferences = 0                      bUse1 = 53031

bstat = 0x1c0000b                    blog = 0xa2159bbb                    bnext = 0x00000000

 

PAGE HEADER:

 

 

Page @0x08F88000

 

m_pageId = (1:119)                   m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000

m_objId (AllocUnitId.idObj) = 98     m_indexId (AllocUnitId.idInd) = 256 

Metadata: AllocUnitId = 72057594044350464                                 

Metadata: PartitionId = 72057594039173120                                 Metadata: IndexId = 0

Metadata: ObjectId = 197575742       m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 32                         m_slotCnt = 1                        m_freeCnt = 8059

m_freeData = 131                     m_reservedCnt = 0                    m_lsn = (116:1299:19)

m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

m_tornBits = 0                       

 

Allocation Status

 

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              

PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL                         DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED           

 

DATA:

 

 

Slot 0, Offset 0x60, Length 35, DumpStyle BYTE

 

Record Type = PRIMARY_RECORD         Record Attributes = NULL_BITMAP    

Memory Dump @0x39ECC060

 

00000000:   10002000 01000000 41414141 41424242 ?.. .....AAAAABBB        

00000010:   42420000 00000000 00001b43 43434343 ?BB.........CCCCC        

00000020:   0600d8???????????????????????????????...                     

 

OFFSET TABLE:

 

Row - Offset                        

0 (0x0) - 96 (0x60)                 

 

 

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

 

SELECT c.name AS column_name, column_id, max_inrow_length,

        pc.system_type_id, leaf_offset

 FROM sys.system_internals_partition_columns pc

    JOIN sys.partitions p

      ON p.partition_id = pc.partition_id

    JOIN sys.columns c

         ON column_id = partition_column_id

            AND c.object_id = p.object_id

WHERE p.object_id=object_id('test')

 

Col1 1       4       56     4

Col2 2       5       175 8

Col3 3       5       175 13

Col4 4       5       175 18

Col5 5       4       56     23

Col6 6       5       175 27

 

--解析字段值

--定长字段的长度为 32

         4        8          13         18         23       27                                                    

10 00 2000 01000000 41414141 41424242 42420000 00000000 00001b43 43434343 0600 d8

           1     AAAA A BBB B B NULL       NULL    C C C C C 6    11011000(从高位到地位第四，五为“1”表示第四，五个字段为NULL)