SQL Server2008儲存結構之基本系統檢視
之所以寫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 |
對於資料庫層面的儲存結構,我們可以參看以下檢視:
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
關於資料庫表的儲存資訊,透過以下系統表我們可以大致瞭解資料庫表在資料庫中是如何定義的。以下檢視提供了基本的資料庫物件資訊。
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6517/viewspace-676414/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Server2008儲存結構之物件儲存檢視SQLServer物件
- SQL Server2008儲存結構之堆表、行溢位SQLServer
- SQL Server 2008儲存結構之PFS結構SQLServer
- SQL Server 2008儲存結構之IAM結構SQLServer
- SQL Server 2008儲存結構之GAM、SGAMSQLServerGAM
- SQL Server 2008 儲存結構之DCM、BCMSQLServer
- PostgreSQL儲存引擎之page結構SQL儲存引擎
- SQL Server基礎之儲存過程SQLServer儲存過程
- SQL Server 比較兩個資料庫的檢視和儲存過程結構差異SQLServer資料庫儲存過程
- 儲存結構
- 檢視Linux版本系統資訊方法彙總Linux
- PostgreSQL儲存引擎之heap tuple結構SQL儲存引擎
- 如何把SQL Server中一個表,一個儲存過程,一個檢視等改為系統表,系統儲存過程,系統檢視等...SQLServer儲存過程
- 六、層次結構儲存系統
- Redis儲存結構以及儲存格式Redis
- 檢視AIX基於HBA的儲存的WWN號AI
- JanusGraph -- 儲存結構
- CentOS 儲存結構CentOS
- 線性表之順序儲存結構
- 線性表之鏈式儲存結構
- Sql Server 獲取指定表、檢視結構SQLServer
- SQL Server2008無法修改表結構?SQLServer
- 《MySQL 基礎篇》十一:索引的儲存結構MySql索引
- Android 儲存系統之架構篇Android架構
- 圖的儲存結構
- 三種儲存結構
- 臺式windows10系統怎麼檢視系統版本 聯想筆記本系統怎麼檢視Windows筆記
- MySQL Innodb 儲存結構 & 儲存Null值 解析MySqlNull
- 基礎——使用檢視和儲存過程的優缺點儲存過程
- SQL Server基礎:儲存過程SQLServer儲存過程
- MySQL--儲存過程與檢視MySql儲存過程
- 怎麼檢視mysql的儲存引擎MySql儲存引擎
- mybatis呼叫檢視和儲存過程MyBatis儲存過程
- InnoDB記錄儲存結構
- php圖的儲存結構PHP
- HBase 資料儲存結構
- redis 儲存結構原理 2Redis
- oracle物理儲存結構理解Oracle