SQL Server2008存储结构之基本系统视图

之所以写SQL Server 2008存储结构，很大程度上是因为看了《SQL Server 2005技术内幕存储引擎》和《SQL Server 2008 Internals》，其次主要是为了满足自己的好奇心和虚荣心。

　　说实话，了解SQLServer2008的存储结构，也许并不会提高你的SQL技能，也许也不会提升你对SQL Server性能优化的能力。出于好玩的目的，希望能够和大家分享一下。

　　本文算是对两本书的阅读笔记，加上自己的动手实践和领悟;如果涉及版权问题和原创问题概不负责。

　　从直观的角度出发，我们可以观察到SQL Server的物理存储由若干数据库构成，其中

数据库类别数据库名称数据库描述系统数据库mastermaster 数据库记录 SQL Server 系统的所有系统级信息。主要包括实例范围的元数据、端点、链接服务器和系统配置设置以及记录了所有其他数据库的存在、数据库文件的位置以及 SQL Server 的初始化信息。model提供了SQL Server 实例上创建的所有数据库的模板。msdb主要由 SQL Server 代理用于计划警报和作业tempdbtempdb 系统数据库是一个全局资源，可供连接到 SQL Server 实例的所有用户使用，并可用于保存显式创建的临时用户对象、SQL Server 数据库引擎创建的内部对象，行版本数据等户数据库db1/db2 

 　　如果我们在数据库处点击右键，选择属性，可以在文件处看到：

　　每一个数据库无论系统数据库还是用户数据库都是由两类数据库文件构成，即行数据数据库文件和日志文件;而行数据数据库文件则有一个主要数据文件和N个次要数据文件构成。

　　我们还可以再考察一下文件组页，每个数据库都有一个Primary主文件组和N个用户定义文件组构成。通过对表对象应用filegroup选项，能够将不同的表分散到不同的磁盘上，以提高系统性能。

　　数据库又主要由表、视图、函数、存储过程、触发器、类型、规则、默认值等等构成。

　　当然我们主要考察的对象是表，每一个数据库实际上都包含一系列系统表和一系列用户表。

　　而表又包括一系列的列、主外键、约束、触发器、索引等。

　　SQLServer2008中提供了相当丰富的系统视图，能够从宏观到微观，从静态到动态反应数据库对象的存储结果、系统性能、系统等待事件等等。同时也保留了与早期版本兼容性的视图，主要差别在于SQLServer2008提供的新系统视图一是更加全面和丰富、二是更注重命名规则。

        SQLServer2008的几乎所有对象信息都存在于sys.objects系统视图中，同时又在不同的系统视图中保留了相应的副本，对于函数、视图、存储过程、触发器等相应的文本对象，把相应的对象的详细资料存于新的sys.sql_modules视图中。

序号对象类型对象类型描述相关系统表1AF = 聚合函数 (CLR)AGGREGATE_FUNCTIONN/A2C = CHECK 约束CHECK_CONSTRAINTCHECK_CONSTRAINTS3D = DEFAULT（约束或独立）DEFAULT_CONSTRAINTDEFAULT_CONSTRAINTS4F = FOREIGN KEY 约束FOREIGN_KEY_CONSTRAINTFOREIGN_KEYS5FN = SQL 标量函数SQL_SCALAR_FUNCTIONSQL_MODULES6FS = 程序集 (CLR) 标量函数CLR_SCALAR_FUNCTIONN/A7FT = 程序集 (CLR) 表值函数CLR_TABLE_VALUED_FUNCTIONN/A8IF = SQL 内联表值函数SQL_INLINE_TABLE_VALUED_FUNCTIONSQL_MODULES9IT = 内部表INTERNAL_TABLEINTERNAL_TABLES10P = SQL 存储过程SQL_STORED_PROCEDUREPROCEDURES
SQL_MODULES
 11PC = 程序集 (CLR) 存储过程CLR_STORED_PROCEDUREN/A12PG = 计划指南PLAN_GUIDEPLAN_GUIDES13PK = PRIMARY KEY 约束PRIMARY_KEY_CONSTRAINTKEY_CONSTRAINTS14R = 规则（旧式，独立）RULESQL_MODULES15RF = 复制筛选过程REPLICATION_FILTER_PROCEDURESQL_MODULES16S = 系统基表SYSTEM_TABLEOBJECTS17SN = 同义词SYNONYMSYNONYMS18SQ = 服务队列SERVICE_QUEUESERVICE_QUEUESS19TA = 程序集 (CLR) DML 触发器CLR_TRIGGERN/A20TF = SQL 表值函数SQL_TABLE_VALUED_FUNCTIONSQL_MODULES21TR = SQL DML 触发器SQL_TRIGGERTRIGGERS
SQL_MODULES
 22U = 表（用户定义类型）USER_TABLETABLES23UQ = UNIQUE 约束UNIQUE_CONSTRAINTKEY_CONSTRAINTS24V = 视图VIEWVIEWS
SQL_MODULES
 25X = 扩展存储过程EXTENDED_STORED_PROCEDUREEXTENDED_PROCEDURES

 

　　对于数据库层面的存储结构，我们可以参看以下视图：

#div_code img { border: 0px none; }

--数据库实例的概要情况
SELECT * FROM SYS.SERVERS
WHERE SERVER_ID=0
--兼容性视图SELECT * FROM SYS.SYSSERVERS

--各个数据库的详细信息
SELECT * FROM SYS.DATABASES
--兼容性视图SELECT * FROM SYS.SYSDATABASES

--文件组的详细信息
SELECT * FROM SYS.FILEGROUPS  
--兼容性视图SELECT * FROM SYS.SYSFILEGROUPS

--各个数据库文件的详细信息
SELECT * FROM SYS.MASTER_FILES
--兼容性视图SELECT * FROM SYS.SYSALTFILES

--当前数据库文件的详细信息
SELECT * FROM SYS.DATABASE_FILES
--兼容性视图SELECT * FROM SYS.SYSFILES

--数据空间的详细情况，可以是文件组或分区方案
SELECT * FROM SYS.DATA_SPACES

 

　　关于数据库表的存储信息，通过以下系统表我们可以大致了解数据库表在数据库中是如何定义的。以下视图提供了基本的数据库对象信息。

#div_code img { border: 0px none; }

--我们首先创建一张表和一些索引
CREATE TABLE dbo.test
(
  id int IDENTITY(1,1) NOT NULL,
  name char(100) NULL,
CONSTRAINT PK_test PRIMARY KEY CLUSTERED (id ASC)
)
CREATE NONCLUSTERED INDEX IX_test ON dbo.test(name)

--表和对象详细信息，根据表名称查询出object_id为
--事实上几乎所有的用户对象都出自于SYS.OBJECTS表
SELECT * FROM SYS.OBJECTS
WHERE type_desc='USER_TABLE' AND NAME='TEST'
--兼容性视图SYSOBJECTS
--如果要查询与该表相关的其他所有对象，则可以执行以下语句
SELECT * FROM SYS.OBJECTS
WHERE type_desc='USER_TABLE' AND NAME='TEST' OR
       parent_object_id in
        (SELECT object_id FROM SYS.OBJECTS
          WHERE type_desc='USER_TABLE' AND NAME='TEST')
          
--表字段详细信息，可以查询出相关column_id
SELECT * FROM SYS.COLUMNS
WHERE OBJECT_ID=5575058
--兼容性视图SYSCOLUMNS

--表索引详细情况,可以清楚的看到存在两个索引
SELECT * FROM SYS.INDEXES WHERE OBJECT_ID=5575058
--兼容性视图SYSINDEXES

--表分区情况，数据库中所有表和索引的每个分区在表中各对应一行
--此处可以看到该表有两个分区，聚集索引即表本身，还有一个是name的非聚集索引
--partition_id 即分区的ID
--hobt_id包含此分区的行的数据堆或B树的ID  
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID=5575058

--分配单元情况，数据库中的每个分配单元都在表中占一行
--该表只有和SYS.PARTITIONS配合使用才有意义
SELECT * FROM SYS.ALLOCATION_UNITS

--SYS.ALLOCATION_UNITS和SYS.PARTITIONS一起使用能够反映出某个对象的页面分配和使用情况
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=5575058
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=5575058

--返回每个分区的页和行计数信息
SELECT * FROM SYS.DM_DB_PARTITION_STATS WHERE OBJECT_ID=5575058

--返回索引的详细字段情况
SELECT * FROM SYS.INDEX_COLUMNS WHERE OBJECT_ID=5575058
--兼容性视图SYSINDEXKEYS

--以下为根据某个索引名称获取其相关字段的语句
DECLARE @index_field_names VARCHAR(500)
SET @index_field_names='';
SELECT @index_field_names=@index_field_names+c.name+','
  FROM SYS.INDEX_COLUMNS a,SYS.INDEXES b,SYS.COLUMNS c
WHERE a.object_id=b.object_id AND a.index_id=b.index_id
   AND a.object_id=c.object_id AND a.column_id=c.column_id
   AND b.name='IX_test2'
ORDER BY a.index_column_id
SET @index_field_names=LEFT(@index_field_names,LEN(@index_field_names)-1)
PRINT @index_field_names

--CHECK约束，数据来源sys.objects.type = 'C'
SELECT * FROM SYS.CHECK_CONSTRAINTS WHERE OBJECT_ID=?
--兼容性视图SYSCONSTRAINTS

--数据来源sys.objects.type = D
SELECT * FROM SYS.DEFAULT_CONSTRAINTS WHERE OBJECT_ID=?
--兼容性视图SYSCONSTRAINTS

--主键或唯一约束,数据来源sys.objects.type PK 和UQ
SELECT * FROM SYS.KEY_CONSTRAINTS WHERE OBJECT_ID=?
--兼容性视图SYSCONSTRAINTS

--外键，数据来源sys.object.type = F
SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID=?  
--兼容性视图SYSREFERENCES

--触发器
SELECT * FROM SYS.TRIGGERS WHERE OBJECT_ID=?  

--注释
SELECT * FROM SYS.SQL_MODULES
--兼容性视图SYSCOMMENTS

--数据库用户表
SELECT * FROM SYS.DATABASE_PRINCIPALS
--兼容性视图SYSUSERS

--数据库数据类型表
SELECT * FROM SYS.TYPES
--兼容性视图SYSTYPES