SQL Server2008存储结构之对象存储视图-技术开发专区

SQL Server2008存储结构之对象存储视图

作者：IT168 不胜人生一场醉编辑：胡铭娅 2010-09-16 00:05 IT168网站原创

　　让我们还是以实例说话吧

--创建一张包含BLOB字段的数据表，同时创建一个聚集索引和非聚集索引，并插入3条记录
CREATE TABLE test(a INT,b VARCHAR(20),c TEXT);
CREATE UNIQUE CLUSTERED INDEX idx_test ON test(a);
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
--根据表名称查询出object_id
SELECT name,object_id,parent_object_id,type_desc
  FROM SYS.OBJECTS WHERE NAME='TEST'  --2089058478
--再查询相关索引视图，可以清楚的看到索引视图中包含两条索引记录，即聚集索引和非聚集索引
SELECT object_id,name,index_id,type,type_desc
  FROM SYS.INDEXES WHERE OBJECT_ID=2089058478
--再查询相关分区视图，可以看到分区视图中包含两条记录，即聚集索引和非聚集索引
SELECT partition_id,object_id,index_id,partition_number,hobt_id,rows
  FROM SYS.PARTITIONS WHERE OBJECT_ID=2089058478
--再查询分配单元视图，可以看到分区视图中包含三条记录，即聚集索引和非聚集索引以及LOB数据
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 =2 AND U.CONTAINER_ID=P.PARTITION_ID
       AND P.OBJECT_ID=2089058478
  ) A
--最后再查询system_internals_allocation_units视图，可以看到该视图中与分配单元视图基本类似，除了多了三个页面地址
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 =2 AND U.CONTAINER_ID=P.PARTITION_ID
       AND P.OBJECT_ID=2089058478
  ) A

--创建一张包含BLOB字段的数据表
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 * FROM heaptest
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 =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 =2 AND U.CONTAINER_ID=P.PARTITION_ID
       AND P.OBJECT_ID=2105058535
  ) A

第1页：视图结构表第2页：创建数据表

关注我们