之所以写SQL Server 2008存储结构,很大程度上是因为看了《SQL Server 2005技术内幕存储引擎》和《SQL Server 2008 Internals》,其次主要是为了满足自己的好奇心和虚荣心。
说实话,了解SQLServer2008的存储结构,也许并不会提高你的SQL技能,也许也不会提升你对SQL Server性能优化的能力。出于好玩的目的,希望能够和大家分享一下。
本文算是对两本书的阅读笔记,加上自己的动手实践和领悟;如果涉及版权问题和原创问题概不负责。
从直观的角度出发,我们可以观察到SQL Server的物理存储由若干数据库构成,其中
数据库类别 |
数据库名称 |
数据库描述 |
系统数据库 |
master |
master 数据库记录 SQL Server 系统的所有系统级信息。主要包括实例范围的元数据、端点、链接服务器
和系统配置设置以及记录了所有其他数据库的存在、数据库文件的位置以及 SQL Server 的初始化信息。 |
model |
提供了SQL Server 实例上创建的所有数据库的模板。 |
msdb |
主要由 SQL Server 代理用于计划警报和作业 |
tempdb |
tempdb 系统数据库是一个全局资源,可供连接到 SQL Server 实例的所有用户使用,并可用于保存显式创建的临时用户对象、SQL Server 数据库引擎创建的内部对象,行版本数据等 |
户数据库 |
db1/db2 |
|
如果我们在数据库处点击右键,选择属性,可以在文件处看到:
每一个数据库无论系统数据库还是用户数据库都是由两类数据库文件构成,即行数据数据库文件和日志文件;而行数据数据库文件则有一个主要数据文件和N个次要数据文件构成。
我们还可以再考察一下文件组页,每个数据库都有一个Primary主文件组和N个用户定义文件组构成。通过对表对象应用filegroup选项,能够将不同的表分散到不同的磁盘上,以提高系统性能。
数据库又主要由表、视图、函数、存储过程、触发器、类型、规则、默认值等等构成。
当然我们主要考察的对象是表,每一个数据库实际上都包含一系列系统表和一系列用户表。
而表又包括一系列的列、主外键、约束、触发器、索引等。
SQLServer2008中提供了相当丰富的系统视图,能够从宏观到微观,从静态到动态反应数据库对象的存储结果、系统性能、系统等待事件等等。同时
也保留了与早期版本兼容性的视图,主要差别在于SQLServer2008提供的新系统视图一是更加全面和丰富、二是更注重命名规则。
SQLServer2008的几乎所有对象信息都存在于sys.objects系统视图中,同时又在不同的系统视图中保留了相应的副本,对于函数、视图、
存储过程、触发器等相应的文本对象,把相应的对象的详细资料存于新的sys.sql_modules视图中。
序号 |
对象类型 |
对象类型描述 |
相关系统表 |
1 |
AF = 聚合函数 (CLR) |
AGGREGATE_FUNCTION |
N/A |
2 |
C = CHECK 约束 |
CHECK_CONSTRAINT |
CHECK_CONSTRAINTS |
3 |
D = DEFAULT(约束或独立) |
DEFAULT_CONSTRAINT |
DEFAULT_CONSTRAINTS |
4 |
F = FOREIGN KEY 约束 |
FOREIGN_KEY_CONSTRAINT |
FOREIGN_KEYS |
5 |
FN = SQL 标量函数 |
SQL_SCALAR_FUNCTION |
SQL_MODULES |
6 |
FS = 程序集 (CLR) 标量函数 |
CLR_SCALAR_FUNCTION |
N/A |
7 |
FT = 程序集 (CLR) 表值函数 |
CLR_TABLE_VALUED_FUNCTION |
N/A |
8 |
IF = SQL 内联表值函数 |
SQL_INLINE_TABLE_VALUED_FUNCTION |
SQL_MODULES |
9 |
IT = 内部表 |
INTERNAL_TABLE |
INTERNAL_TABLES |
10 |
P = SQL 存储过程 |
SQL_STORED_PROCEDURE |
PROCEDURES
SQL_MODULES
|
11 |
PC = 程序集 (CLR) 存储过程 |
CLR_STORED_PROCEDURE |
N/A |
12 |
PG = 计划指南 |
PLAN_GUIDE |
PLAN_GUIDES |
13 |
PK = PRIMARY KEY 约束 |
PRIMARY_KEY_CONSTRAINT |
KEY_CONSTRAINTS |
14 |
R = 规则(旧式,独立) |
RULE |
SQL_MODULES |
15 |
RF = 复制筛选过程 |
REPLICATION_FILTER_PROCEDURE |
SQL_MODULES |
16 |
S = 系统基表 |
SYSTEM_TABLE |
OBJECTS |
17 |
SN = 同义词 |
SYNONYM |
SYNONYMS |
18 |
SQ = 服务队列 |
SERVICE_QUEUE |
SERVICE_QUEUESS |
19 |
TA = 程序集 (CLR) DML 触发器 |
CLR_TRIGGER |
N/A |
20 |
TF = SQL 表值函数 |
SQL_TABLE_VALUED_FUNCTION |
SQL_MODULES |
21 |
TR = SQL DML 触发器 |
SQL_TRIGGER |
TRIGGERS
SQL_MODULES
|
22 |
U = 表(用户定义类型) |
USER_TABLE |
TABLES |
23 |
UQ = UNIQUE 约束 |
UNIQUE_CONSTRAINT |
KEY_CONSTRAINTS |
24 |
V = 视图 |
VIEW |
VIEWS
SQL_MODULES
|
25 |
X = 扩展存储过程 |
EXTENDED_STORED_PROCEDURE |
EXTENDED_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
分享到:
相关推荐
SQLSERVER2008存储结构一_系统视图.doc
sqlserver系统视图与存储过程应用说明,自己总结的,方便查看进程和表字段的注释
第2部分为SQLServer 2008的准备篇,介绍了SQL Server 2008的功能、特性、各版本的比较、安装方法、SQL Server 2008的服务、客户端的工具等;第3部分为SQL Server 2008的基本操作篇,介绍如何管理与配置SQL Server ...
第5章 操作架构、索引和视图 SQL 2008完全学习手册 视频教程 第6章 数据查询和管理 第7章 操作查询 第8章 Transact-SQL编程 第9章 存储过程 第10章 触发器 第11章 SQL Server 2008的安全机制 第12章 备份与恢复...
sqlserver存储过程,视图多,项目不断优化升级,部分存储过程,视图已经报错,但是一直存在数据库中,用vs2013及以上打开项目,运行清理无效的视图,存储过程
使用XML查询技术,存储过程与触发器,操作架构、索引和视图,SQL Server的安全机制,SQL Server备份与恢复,使用.NET访问SQL Server 2008,SQL Server 2008高级主题:SMO、SQL Server 2008服务体系和SQL Server代理...
解密SQL SERVER 2005加密存储过程,视图、函数.sql
第2部分为SQLServer 2008的准备篇,介绍了SQL Server 2008的功能、特性、各版本的比较、安装方法、SQL Server 2008的服务、客户端的工具等;第3部分为SQL Server 2008的基本操作篇,介绍如何管理与配置SQL Server ...
数据库是按照数据结构来组织、存储...此学习课程将以SQL Server2008为平台,为您系统的介绍数据库的基本理论及应用。其中涉及数据库的创建及管理;T-SQL数据语言操作;视图、索引、存储过程;数据库的备份恢复等知识。
SQL储存过程等的解密,破解字节不受限制,适用于SQLSERVER2000存储过程,函数,视图,触发器
SQL Server专家的呕心力作,数据库管理员的实战宝典,全面、深入地剖析SQL Server2008新特性,结构独特,实例丰富,操作性强。 作者简介 作者:(美国)斯坦里克 (William R.Stanek) 译者:贾洪峰 William R.Stanek...
第2部分为SQLServer 2008的准备篇,介绍了SQL Server 2008的功能、特性、各版本的比较、安装方法、SQL Server 2008的服务、客户端的工具等;第3部分为SQL Server 2008的基本操作篇,介绍如何管理与配置SQL Server ...
SQL Server专家的呕心力作,数据库管理员的实战宝典,全面、深入地剖析SQL Server2008新特性,结构独特,实例丰富,操作性强。 作者简介 作者:(美国)斯坦里克 (William R.Stanek) 译者:贾洪峰 William R.Stanek...
sqlserver 快速查询存储过程或者视图中是否用到某个表或者字段。
第2部分为SQLServer 2008的准备篇,介绍了SQL Server 2008的功能、特性、各版本的比较、安装方法、SQL Server 2008的服务、客户端的工具等;第3部分为SQL Server 2008的基本操作篇,介绍如何管理与配置SQL Server ...
SQL Server专家的呕心力作,数据库管理员的实战宝典,全面、深入地剖析SQL Server2008新特性,结构独特,实例丰富,操作性强。 作者简介 作者:(美国)斯坦里克 (William R.Stanek) 译者:贾洪峰 William R.Stanek...
SQL SERVER重置所有视图的存储过程 绝对成功 自己可以去掉游标写个重置单个视图的存储过程,用于表加减字段导致视图错位。
的方法与技巧,最后通过综合实例:ASP.NET+SQL Server 2008实现网络在线论坛系统,讲解网络应用程 序开发的一般流程、常用技术和方法。本书采用理论与实例结合、相互渗透、逐步引导的方法,实例丰富、 图文并茂、...
Sql Server 2008 数据库课设报告(图书管理系统) 包一下全部要求,数据库设计完整sql代码: 1.数据库设计 要求数据库设计要合理,对数据库设计作必要的说明并抓图。 数据库名必须与自己真实姓名有关,所有同学不能...