原帖首發(fā)于it168專稿，鏈接為http://tech.it168.com/a2010/0902/1098/000001098556_1.shtmlITPUB個(gè)人空間i{(Rht ^1X
之所以寫SQL Server 2008存儲(chǔ)結(jié)構(gòu)，很大程度上是因?yàn)榭戳恕禨QL Server 2005技術(shù)內(nèi)幕存儲(chǔ)引擎》和《SQL Server 2008 Internals》，其次主要是為了滿足自己的好奇心和虛榮心。

　　說實(shí)話，了解SQLServer2008的存儲(chǔ)結(jié)構(gòu)，也許并不會(huì)提高你的SQL技能，也許也不會(huì)提升你對(duì)SQL Server性能優(yōu)化的能力。出于好玩的目的，希望能夠和大家分享一下。

　　本文算是對(duì)兩本書的閱讀筆記，加上自己的動(dòng)手實(shí)踐和領(lǐng)悟;如果涉及版權(quán)問題和原創(chuàng)問題概不負(fù)責(zé)。

　　從直觀的角度出發(fā)，我們可以觀察到SQL Server的物理存儲(chǔ)由若干數(shù)據(jù)庫(kù)構(gòu)成，其中

數(shù)據(jù)庫(kù)類別	數(shù)據(jù)庫(kù)名稱	數(shù)據(jù)庫(kù)描述
系統(tǒng)數(shù)據(jù)庫(kù)	master	master 數(shù)據(jù)庫(kù)記錄 SQL Server 系統(tǒng)的所有系統(tǒng)級(jí)信息。主要包括實(shí)例范圍的元數(shù)據(jù)、端點(diǎn)、鏈接服務(wù)器和系統(tǒng)配置設(shè)置以及記錄了所有其他數(shù)據(jù)庫(kù)的存在、數(shù)據(jù)庫(kù)文件的位置以及 SQL Server 的初始化信息。
	model	提供了SQL Server 實(shí)例上創(chuàng)建的所有數(shù)據(jù)庫(kù)的模板。
	msdb	主要由 SQL Server 代理用于計(jì)劃警報(bào)和作業(yè)
	tempdb	tempdb 系統(tǒng)數(shù)據(jù)庫(kù)是一個(gè)全局資源，可供連接到 SQL Server 實(shí)例的所有用戶使用，并可用于保存顯式創(chuàng)建的臨時(shí)用戶對(duì)象、SQL Server 數(shù)據(jù)庫(kù)引擎創(chuàng)建的內(nèi)部對(duì)象，行版本數(shù)據(jù)等
戶數(shù)據(jù)庫(kù)	db1/db2	?

ITPUB個(gè)人空間 A+fZ5x4TB)O
?　　如果我們?cè)跀?shù)據(jù)庫(kù)處點(diǎn)擊右鍵，選擇屬性，可以在文件處看到：

　　每一個(gè)數(shù)據(jù)庫(kù)無論系統(tǒng)數(shù)據(jù)庫(kù)還是用戶數(shù)據(jù)庫(kù)都是由兩類數(shù)據(jù)庫(kù)文件構(gòu)成，即行數(shù)據(jù)數(shù)據(jù)庫(kù)文件和日志文件;而行數(shù)據(jù)數(shù)據(jù)庫(kù)文件則有一個(gè)主要數(shù)據(jù)文件和N個(gè)次要數(shù)據(jù)文件構(gòu)成。

　　我們還可以再考察一下文件組頁，每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)Primary主文件組和N個(gè)用戶定義文件組構(gòu)成。通過對(duì)表對(duì)象應(yīng)用filegroup選項(xiàng)，能夠?qū)⒉煌谋矸稚⒌讲煌拇疟P上，以提高系統(tǒng)性能。

　　數(shù)據(jù)庫(kù)又主要由表、視圖、函數(shù)、存儲(chǔ)過程、觸發(fā)器、類型、規(guī)則、默認(rèn)值等等構(gòu)成。

　　當(dāng)然我們主要考察的對(duì)象是表，每一個(gè)數(shù)據(jù)庫(kù)實(shí)際上都包含一系列系統(tǒng)表和一系列用戶表。

　　而表又包括一系列的列、主外鍵、約束、觸發(fā)器、索引等。

　 　SQLServer2008中提供了相當(dāng)豐富的系統(tǒng)視圖，能夠從宏觀到微觀，從靜態(tài)到動(dòng)態(tài)反應(yīng)數(shù)據(jù)庫(kù)對(duì)象的存儲(chǔ)結(jié)果、系統(tǒng)性能、系統(tǒng)等待事件等等。同時(shí) 也保留了與早期版本兼容性的視圖，主要差別在于SQLServer2008提供的新系統(tǒng)視圖一是更加全面和豐富、二是更注重命名規(guī)則。

??????? SQLServer2008的幾乎所有對(duì)象信息都存在于sys.objects系統(tǒng)視圖中，同時(shí)又在不同的系統(tǒng)視圖中保留了相應(yīng)的副本，對(duì)于函數(shù)、視圖、 存儲(chǔ)過程、觸發(fā)器等相應(yīng)的文本對(duì)象，把相應(yīng)的對(duì)象的詳細(xì)資料存于新的sys.sql_modules視圖中。

序號(hào)	對(duì)象類型	對(duì)象類型描述	相關(guān)系統(tǒng)表
1	AF = 聚合函數(shù) (CLR)	AGGREGATE_FUNCTION	N/A
2	C = CHECK 約束	CHECK_CONSTRAINT	CHECK_CONSTRAINTS
3	D = DEFAULT（約束或獨(dú)立）	DEFAULT_CONSTRAINT	DEFAULT_CONSTRAINTS
4	F = FOREIGN KEY 約束	FOREIGN_KEY_CONSTRAINT	FOREIGN_KEYS
5	FN = SQL 標(biāo)量函數(shù)	SQL_SCALAR_FUNCTION	SQL_MODULES
6	FS = 程序集 (CLR) 標(biāo)量函數(shù)	CLR_SCALAR_FUNCTION	N/A
7	FT = 程序集 (CLR) 表值函數(shù)	CLR_TABLE_VALUED_FUNCTION	N/A
8	IF = SQL 內(nèi)聯(lián)表值函數(shù)	SQL_INLINE_TABLE_VALUED_FUNCTION	SQL_MODULES
9	IT = 內(nèi)部表	INTERNAL_TABLE	INTERNAL_TABLES
10	P = SQL 存儲(chǔ)過程	SQL_STORED_PROCEDURE	PROCEDURES :Bw3D7r]h `6W(YI6517SQL_MODULES &suSA%G|aeU6517?
11	PC = 程序集 (CLR) 存儲(chǔ)過程	CLR_STORED_PROCEDURE	N/A
12	PG = 計(jì)劃指南	PLAN_GUIDE	PLAN_GUIDES
13	PK = PRIMARY KEY 約束	PRIMARY_KEY_CONSTRAINT	KEY_CONSTRAINTS
14	R = 規(guī)則（舊式，獨(dú)立）	RULE	SQL_MODULES
15	RF = 復(fù)制篩選過程	REPLICATION_FILTER_PROCEDURE	SQL_MODULES
16	S = 系統(tǒng)基表	SYSTEM_TABLE	OBJECTS
17	SN = 同義詞	SYNONYM	SYNONYMS
18	SQ = 服務(wù)隊(duì)列	SERVICE_QUEUE	SERVICE_QUEUESS
19	TA = 程序集 (CLR) DML 觸發(fā)器	CLR_TRIGGER	N/A
20	TF = SQL 表值函數(shù)	SQL_TABLE_VALUED_FUNCTION	SQL_MODULES
21	TR = SQL DML 觸發(fā)器	SQL_TRIGGER	TRIGGERSITPUB個(gè)人空間? F%v;C5U(e.J SQL_MODULES f7A+ZZDY6517?
22	U = 表（用戶定義類型）	USER_TABLE	TABLES
23	UQ = UNIQUE 約束	UNIQUE_CONSTRAINT	KEY_CONSTRAINTS
24	V = 視圖	VIEW	VIEWS 5}A'o8x@ZSmsh6517SQL_MODULES 4P6q zk&VO6}6517?
25	X = 擴(kuò)展存儲(chǔ)過程	EXTENDED_STORED_PROCEDURE	EXTENDED_PROCEDURES

?對(duì)于數(shù)據(jù)庫(kù)層面的存儲(chǔ)結(jié)構(gòu)，我們可以參看以下視圖：

#div_code img { border: 0px none; } --數(shù)據(jù)庫(kù)實(shí)例的概要情況
r|hY*huP`6517SELECT*FROM SYS.SERVERS
*C(X9`-@KL d6517WHERE SERVER_ID=0ITPUB個(gè)人空間C]ydj)XN0g
--兼容性視圖SELECT*FROM SYS.SYSSERVERS
Wd'N6cy2c'd6517
:L+IV5U.z.Q|6517--各個(gè)數(shù)據(jù)庫(kù)的詳細(xì)信息ITPUB個(gè)人空間/ui0^!e7dj @
SELECT*FROM SYS.DATABASESITPUB個(gè)人空間'u8XG~3I1C~r
--兼容性視圖SELECT*FROM SYS.SYSDATABASESITPUB個(gè)人空間D&cn4z4Iv.U
ITPUB個(gè)人空間[4p A+j _g
--文件組的詳細(xì)信息
(Vc'f9U$H_XQ;Z6517SELECT*FROM SYS.FILEGROUPS??ITPUB個(gè)人空間dox\*^b']
--兼容性視圖SELECT*FROM SYS.SYSFILEGROUPS
^]7POz4u6517
\wDrS{_ \~gJ6517--各個(gè)數(shù)據(jù)庫(kù)文件的詳細(xì)信息ITPUB個(gè)人空間9UH'DO6]&Z5^C(mh-n%p
SELECT*FROM SYS.MASTER_FILESITPUB個(gè)人空間9y0o"Z:e*@C}
--兼容性視圖SELECT*FROM SYS.SYSALTFILESITPUB個(gè)人空間3nos#R$WgS FS

.C1Jd*y6mhzk6517--當(dāng)前數(shù)據(jù)庫(kù)文件的詳細(xì)信息
j;QaZP D/j:Q6517SELECT*FROM SYS.DATABASE_FILES
4j.^$q$CJ5ck/u3biI6517--兼容性視圖SELECT*FROM SYS.SYSFILES
8^:`g YU&hz6517ITPUB個(gè)人空間ow8Tt|&{$R
--數(shù)據(jù)空間的詳細(xì)情況，可以是文件組或分區(qū)方案
4tiUU^)e6517SELECT*FROM SYS.DATA_SPACESITPUB個(gè)人空間\d{h!so

?

　　關(guān)于數(shù)據(jù)庫(kù)表的存儲(chǔ)信息，通過以下系統(tǒng)表我們可以大致了解數(shù)據(jù)庫(kù)表在數(shù)據(jù)庫(kù)中是如何定義的。以下視圖提供了基本的數(shù)據(jù)庫(kù)對(duì)象信息。

#div_code img { border: 0px none; } --我們首先創(chuàng)建一張表和一些索引
lS.TJUoIA4_3c6517CREATE TABLE dbo.test
*oNYEg!K$I'U6517(ITPUB個(gè)人空間"[[)I%fY|9K&R
??idintIDENTITY(1,1)NOTNULL,
5N Q}.er/D6517??name char(100)NULL,
n3~4GfYO+K6517CONSTRAINT PK_test PRIMARY KEY CLUSTERED (idASC)ITPUB個(gè)人空間!U+R%J(Z$VKnm oal
)ITPUB個(gè)人空間N:_r0H~YZ{J&\
CREATE NONCLUSTERED INDEX IX_testONdbo.test(name)ITPUB個(gè)人空間AkV_d9o

8C,{ OS k6517--表和對(duì)象詳細(xì)信息，根據(jù)表名稱查詢出object_id為ITPUB個(gè)人空間]%R"nsOa"_"T @*j
--事實(shí)上幾乎所有的用戶對(duì)象都出自于SYS.OBJECTS表ITPUB個(gè)人空間\,B[5H$i6|L @q
SELECT*FROM SYS.OBJECTSITPUB個(gè)人空間{1]R*Us.W
WHERE type_desc='USER_TABLE' AND NAME='TEST'ITPUB個(gè)人空間FM:n? @v
--兼容性視圖SYSOBJECTS
6Q}6t)_a/Wr4|F'k6517--如果要查詢與該表相關(guān)的其他所有對(duì)象，則可以執(zhí)行以下語句ITPUB個(gè)人空間e+GWA(t
SELECT*FROM SYS.OBJECTS
ID S D'z&cNc6517WHERE type_desc='USER_TABLE' AND NAME='TEST' OR
[[z*W1gb3Bm6AtT6517?????? parent_object_id inITPUB個(gè)人空間d`'Z]{T i9[;tU
????????(SELECTobject_id FROM SYS.OBJECTSITPUB個(gè)人空間SJ$LP-Z/p#Xu]
??????????WHERE type_desc='USER_TABLE' AND NAME='TEST')ITPUB個(gè)人空間 f$y:D8j8U p#gx/e
??????????
/Z+dBt4gp!l6_w6517--表字段詳細(xì)信息，可以查詢出相關(guān)column_idITPUB個(gè)人空間9EKUDVs^a
SELECT*FROM SYS.COLUMNSITPUB個(gè)人空間~"O Z fz#k~
WHERE OBJECT_ID=5575058ITPUB個(gè)人空間:c5[Lv)sE{#QtW
--兼容性視圖SYSCOLUMNS
)h(fk;`(|@\6517ITPUB個(gè)人空間 J4G2N{q9Ps3L
--表索引詳細(xì)情況,可以清楚的看到存在兩個(gè)索引
:p?|h r6517SELECT*FROM SYS.INDEXES WHERE OBJECT_ID=5575058
5QSG1ru+^ y-m0b6517--兼容性視圖SYSINDEXES
U] zZ#g0y6517
?du^(n+j6517--表分區(qū)情況，數(shù)據(jù)庫(kù)中所有表和索引的每個(gè)分區(qū)在表中各對(duì)應(yīng)一行
U C8Ak F+]6517--此處可以看到該表有兩個(gè)分區(qū)，聚集索引即表本身，還有一個(gè)是name的非聚集索引ITPUB個(gè)人空間9y8Gc"{?&DB9~
--partition_id 即分區(qū)的ID
:Z skZ;\wN{6517--hobt_id包含此分區(qū)的行的數(shù)據(jù)堆或B樹的ID??ITPUB個(gè)人空間\N,PS4Ycw3M
SELECT*FROM SYS.PARTITIONS WHERE OBJECT_ID=5575058
:^hF A$^6517
Qh,H?3xD^6517--分配單元情況，數(shù)據(jù)庫(kù)中的每個(gè)分配單元都在表中占一行ITPUB個(gè)人空間.k T^;Z4v&^%Vr'nth\
--該表只有和SYS.PARTITIONS配合使用才有意義ITPUB個(gè)人空間[)?]^7CZPd W7F
SELECT*FROM SYS.ALLOCATION_UNITS
5J_.@JQ3}lZ!K6517
C WTYLW6517--SYS.ALLOCATION_UNITS和SYS.PARTITIONS一起使用能夠反映出某個(gè)對(duì)象的頁面分配和使用情況ITPUB個(gè)人空間.Nu/d!N.y7xuQH
SELECT*FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P
KiJ5Jy6517WHERE U.TYPE IN (1,3)ANDU.CONTAINER_ID=P.HOBT_IDANDP.OBJECT_ID=5575058
.@%} D+Lz&?KE+j,j4~6517UNION ALLITPUB個(gè)人空間O[#} t,c0h
SELECT*FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P
({.s#} nQ_Id/{S6517WHERE U.TYPE=2ANDU.CONTAINER_ID=P.PARTITION_IDANDP.OBJECT_ID=5575058ITPUB個(gè)人空間D)Bg*Fr5a
ITPUB個(gè)人空間@qrhym#rzo
--返回每個(gè)分區(qū)的頁和行計(jì)數(shù)信息ITPUB個(gè)人空間vd1C8H$kKCl/I
SELECT*FROM SYS.DM_DB_PARTITION_STATS WHERE OBJECT_ID=5575058
i-^'[?re6}!rM*K&u6517ITPUB個(gè)人空間xH`(h9ZJ:O)kcU
--返回索引的詳細(xì)字段情況ITPUB個(gè)人空間 @`!\Ct9m?
SELECT*FROM SYS.INDEX_COLUMNS WHERE OBJECT_ID=5575058ITPUB個(gè)人空間4?-o[E~qFN4H#S
--兼容性視圖SYSINDEXKEYS
iXQ9h9Yv6ThA6517
j|9Oq.Y6517--以下為根據(jù)某個(gè)索引名稱獲取其相關(guān)字段的語句
|LMiD#V,E+? ~6517DECLARE @index_field_names VARCHAR(500)
Nr$`-~ M[X6517SET@index_field_names='';
x(E A/hM`O;prV-?*c6517SELECT@index_field_names=@index_field_names+c.name+','
"rD _}2j|6517??FROM SYS.INDEX_COLUMNS a,SYS.INDEXES b,SYS.COLUMNS c
|0y7N;h SY6517WHERE a.object_id=b.object_idANDa.index_id=b.index_id
%y'z4~`~xA6517??ANDa.object_id=c.object_idANDa.column_id=c.column_id
"yqzD8a*S6517??ANDb.name='IX_test2'
$_!m+l9zn6517ORDER BY a.index_column_id
| z:EPEq}6517SET@index_field_names=LEFT(@index_field_names,LEN(@index_field_names)-1)
qN1t9g1z6517PRINT @index_field_names
0VL:`Xl7j L+@?H6517ITPUB個(gè)人空間(Rs?q6] u+B
--CHECK約束，數(shù)據(jù)來源sys.objects.type='C'
M)SR1S c~i}_ A6517SELECT*FROM SYS.CHECK_CONSTRAINTS WHERE OBJECT_ID=?
jhKwG1S!t5L6517--兼容性視圖SYSCONSTRAINTSITPUB個(gè)人空間F*J8P h+?J8I

*W@/?8c"z1hrw6517--數(shù)據(jù)來源sys.objects.type=DITPUB個(gè)人空間9Y%l:D$w~f
SELECT*FROM SYS.DEFAULT_CONSTRAINTS WHERE OBJECT_ID=?
:`WZa;x W6517--兼容性視圖SYSCONSTRAINTSITPUB個(gè)人空間pEi(`wM3^
ITPUB個(gè)人空間Y_/C2c1E Nr
--主鍵或唯一約束,數(shù)據(jù)來源sys.objects.type PK 和UQITPUB個(gè)人空間?;f:K&|$m.h!]%n
SELECT*FROM SYS.KEY_CONSTRAINTS WHERE OBJECT_ID=?ITPUB個(gè)人空間I_)E.FmrG)q;t#V-yF o
--兼容性視圖SYSCONSTRAINTSITPUB個(gè)人空間ap|Zxx L
ITPUB個(gè)人空間Jl%\$@^$i
--外鍵，數(shù)據(jù)來源sys.object.type=FITPUB個(gè)人空間`*CBB-N;G
SELECT*FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID=???ITPUB個(gè)人空間b"D:{v,ju
--兼容性視圖SYSREFERENCES
;itr.lQ'X~^6517ITPUB個(gè)人空間5E|-A+j4h
--觸發(fā)器ITPUB個(gè)人空間:\|:d0mkxL]| Z
SELECT*FROM SYS.TRIGGERS WHERE OBJECT_ID=???
vk5t9| t8K5R^!f6517ITPUB個(gè)人空間iK7ISM7~ VYr
--注釋
&Lh"e$O2e;i6`*c6517SELECT*FROM SYS.SQL_MODULES
wpD2Zd-z6517--兼容性視圖SYSCOMMENTS
E4bj`+Kp:x/`6517
V4]6O3V&JA E)n C H6517--數(shù)據(jù)庫(kù)用戶表
w/d'RA+QG&w-f6517SELECT*FROM SYS.DATABASE_PRINCIPALS
:G Xo z5U|8z6517--兼容性視圖SYSUSERS
5_.{"EB,m L6517
Jz [.P:M8Is&i[6517--數(shù)據(jù)庫(kù)數(shù)據(jù)類型表ITPUB個(gè)人空間,y:J_4x#b
SELECT*FROM SYS.TYPES
"_lB)iR2[^1C6517--兼容性視圖SYSTYPES

轉(zhuǎn)載于:https://blog.51cto.com/baoqiangwang/408425

總結(jié)

以上是生活随笔為你收集整理的SQL Server 2008连载之存储结构——基本系统视图的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。