第 4 章

1. 第 4 章 資料庫的建立 與規劃

2. 4-1 認識系統資料庫 • SQL Server 將與伺服器本身有關的各項資訊, 都存放在特定的資料庫中, 這些資料庫稱為『系統資料庫』 • master、msdb、model、tempdb 這四個基本的系統資料庫 • 不能刪除這些資料庫 • 還有一個隱藏的 Resource 資料庫

3. master • master 資料庫記錄的是有關 SQL Server 的資訊, 包括所有的登入帳戶、系統的組態、各資料的初始資訊等各類重要資料。 • 不再讓我們直接瀏覽 • 修改各資料庫中的系統資料表必須透過系統檢視 (system view) 來瀏覽 • 可用 "select * from sysobjects where type ='S'" 來查看master 資料庫中有多少隱藏起來的資料表：

4. master

5. master • 由於 master 資料庫的內容對整個資料庫系統的關係重大, 因此最好要定時備份此資料庫的內容。在下一節會對 master 資料庫中幾個重要的檢視表做簡單的介紹。

6. msdb • msdb 是另一個供系統使用的資料庫, 其主要用途： • SQL Server Agent 做各類排程作業(job) • 備份和還原的記錄、複寫和資料維護計劃等 • 不宜由我們直接做更動的

7. msdb

8. model • model 稱為『樣板』資料庫 • 當我們在 SQL Server 中建立新的資料庫時, SQL Server 會以 model 資料庫為藍本, 將其內容複製到新資料庫中 • 因此在所有新建的資料庫中, 都會有和 model 資料庫內容一樣的系統資料表和檢視表等資料庫物件。 • 有需要的話, 我們可以更動其內容，以後在 SQL Server 中建立新的資料庫時, 這些物件也都會出現在新資料庫中, 省下自己再另行建立的麻煩。

9. tempdb • tempdb • 用來存放暫時性資料用的(查詢或排序) • 每次 SQL Server 重新啟動時, 都會重建一份新的 tempdb 資料庫 • tempdb 資料庫的大小也會跟著自動調整 • tempdb 包含和model 相同的資料庫物件

10. Resource • Management Studio 中看不到 • 用檔案總管進入 SQL Server 的資料庫檔資料夾 (例如 Program Files\Microsoft SQL Server\MSSQL. 1\MSSQL\Data, 讀者電腦的路徑可能與此不同), 就可以看到 Resource 資料庫的資料檔及交易記錄檔 mssqlsystemresource.mdf、mssqlsystemresource.ldf, • 存放了許多與 SQL Server 2005 本身相關的系統物件, 使用者物件都不會存放Resource 資料庫中。

11. Resource • SQL Server 2005 採用 Resource 資料庫的目的之一, 就是讓系統資源集中存放管理, 日後將可透過升級 Resource 資料庫的方式, 即可升級 SQL Server 2005的功能。

12. 4-2 系統資料表 • 系統資料表就是由 SQL Server 自己建立及使用的資料庫資料表 • 系統目錄(System Catalog) 資料表 • master、msdb 等資料庫專有的系統資料表。 • 必須透過 SQL Server 提供的系統檢視表 (用來檢視系統目錄的系統檢視表, 又稱為目錄檢視表, Catalog view)。

13. 目錄檢視表 • 在 model 這個樣版資料庫中查詢 sysobjects 檢視表, 可發現會被複製到每個新資料庫的系統目錄資料表共有 41 個之多 (type 欄位值為 ‘s’ 者即為系統目錄資料表)。 • 這些資料表都是用來記錄與該資料庫相關的各項資訊, 透過目錄檢視表即可看到這些資料表中記錄了哪些資訊, 以下介紹幾個重要的檢視表及它們所能查看的資訊。

14. 目錄檢視表

15. 目錄檢視表 • sys.allocation_units：列出有關配置單元 (Allocation unit, 參見 4-4 節) 的資訊。 • sys.columns：顧名思義, 此檢視表會傳回有關欄位 (Column) 的各項資訊, 資料庫中所有資料表和檢視表中的欄位, 以及預存程序中的變數, 在此資料表中都會有一列記錄其資訊。例如若在我們的資料表中有個名為 'TelephoneNumber' 的欄位, 就能在 sys.columns 中找出它的相關記錄。

16. 目錄檢視表 • sys.check_constraints：條件約束 (Constrain) 與欄位的對應關係, 也就是哪一個欄位要套用哪一個條件約束。所以若資料庫中設定的條件約束多, 此檢視表傳回的記錄也會隨之增加。 • sys.database_files：資料庫所有檔案的資訊, 例如檔案的識別碼、所屬檔案群組的識別碼、檔案大小、和檔案的路徑等等。

17. 目錄檢視表 • sys.database_permissions：目前的資料庫存取權限資訊。 • sys.database_principals：由其名稱可看出, 此檢視表會傳回此資料庫中所有使用者與角色物件(統稱為資料層級的安全性主體, Principal) 的資訊, 包括主體名稱、識別碼、類型、預設結構描述、建立及修改日期等。 • sys.database_role_members：會傳回資料庫中各角色的識別碼及其成員的識別碼。

18. 目錄檢視表 • sys.filegroups：檢視這個資料庫所擁有的檔案群組資訊, 例如群組的識別碼和群組的名稱等。 • sys.foreignkeys：檢視我們在資料庫資料表中所設的 FOREIGN KEY 資訊。 • sys.fulltext_catalogs：檢視此資料庫中的全文檢索目錄資訊, 包括全文檢索目錄的名稱以及存放的路徑等, 若未在該資料庫中建立全文檢索目錄, 則此資料表會是空白的。

19. 目錄檢視表 • sys.indexes：雖然名稱中只有 index 這個字, 不過除傳回索引的資訊外, 也包含 heap 的資訊 (未建立叢集索引的資料表即稱為 heap)。 • sys.index_columns：記錄資料庫中所設的索引鍵資訊, 例如所在的資料表及欄位編號。

20. 目錄檢視表 • sys.objects：檢視資料庫所有使用者物件的資訊, 包括物件名稱、物件識別碼、物件的類型、和擁有者的使用者識別碼等；另外還有個 sys.system_objects 則會傳回系統物件的資訊。如果想查看資料庫中『所有』物件, 則可查看 sys.all_objects 或執行系統預存程序 sp_help。

21. 目錄檢視表 • sys.server_permissions：目前的伺服器存取權限資訊。 • sys.sql_dependencies：檢視所有檢視表、預存程序、和觸發程序這三類物件與其資料來源物件 (資料表、檢視表、或預存程序) 之間的依存關係, 以及在其定義中所含的其它物件。不過此表列出的都是數字性的識別碼等資訊, 不太適合我們查詢, 如果想查看文字性的資訊, 可使用系統預存程序 sp_depends。

22. 目錄檢視表 • sys.sql_modules：列出資料庫中所有檢視表、觸發程序、預存程序、和規則等物件的資訊, 如果您查詢這個檢視表的內容, 可在其中的 definition 欄看到上列物件的原始 SQL 語法定義。不過請勿自行手動更動這些內容, 因為如此可能會使您的檢視表或預存程序等物件失效。 • sys.types：檢視系統內建型別和在這個資料庫中所建立的使用者自訂型別, 包括型別的名稱、是否為 nullable、及其長度等各項資訊。

23. master 資料庫特有的系統檢視表 • 除了前述每個資料庫都有的目錄檢視表外, 在 master 資料庫中還另有許多特別的系統檢視表, 可用來查看與整個 SQL Server 系統相關的資訊, 以下介紹其中幾個實用的檢視表。 • sys.backup_devices：可檢視已建立的備份裝置之相關資訊, 包括裝置名稱、類型、路徑。

24. master 資料庫特有的系統檢視表 • sys.configurations：可檢視系統的組態資訊, 建議您對 master 資料庫執行一下 “select * from sys.configurations” 敘述來看看 SQL Server 到底有哪些組態設定,如此可大致認識一下我們能對 SQL Server 做什麼樣的調校。 • 在傳回結果中, value_in_use 欄位表示的是『執行中』的 SQL Server 所採用的設定值；若您正好更改了某項系統組態, 那麼這個新的且尚未生效的設定值則會顯示在values欄位, 待下次重新啟動 SQL Server 或執行 "RECONFIGURE" 指令後, 新的設定值才會生效。關於系統組態設定, 請參見 11-5 節的介紹。

25. master 資料庫特有的系統檢視表 • sys.databases：檢視 SQL Server 中所有資料庫的資訊, 包括資料庫的名稱、建立日期、目前所用的復原模式設定值等。 • sys.dm_tran_locks：列出目前已設定的所有鎖定 (lock) 資訊。 • sys.linked_logins：列出連上各連結伺服器 (參見附錄 B) 所用的帳號設定。 • sys.masterfiles：檢視由 SQL Server 所管理的各項檔案資訊。

26. master 資料庫特有的系統檢視表 • sys.messages：列出 SQL Server 所有的錯誤訊息 (包括所有語言的版本)。 • sys.remotelogins：列出從遠端 SQL Server 伺服器登入的使用者資訊。 • sys.servers：列出所有此 SQL Server 可存取的遠端 SQL Server 伺服器 (參見附錄 B)。 • sys.server_principals：列出這個 SQL Server 所有的登入帳戶資訊, 包括登入名稱、預設資料庫和語系等。

27. master 資料庫特有的系統檢視表 • sys.syscharsets：檢視 SQL Server 所支援的各種字元集和排序方式的資訊, 也就是在第二章提過在重建系統資料庫時所用的定序參數值, 例如 id 為 196 的記錄, 代表的就是使用繁體中文字集 (Code Page 950) 並使用二元排序方式。

28. 其它系統檢視表 • 為符合 SQL-92 標準的規範, SQL Server 也提供另一組系統檢視表, 稱為資訊結構描述檢視表, 它們都定義在 INFORMATION_SCHEMA 這個特別的結構描述中。 • 這些檢視表所傳回的內容, 其實都是透過前述的系統檢視表取得, 至於屬於 SQL Server 特有的 (非 SQL-92 標準) 系統資訊, 將無法透過資訊結構描述檢視表取得。

29. 4-3 資料庫的規劃 • 資料庫本身也是由 SQL Server 透過作業系統, 以檔案的形式存於磁碟之中。不過雖然檔案存於磁碟上的確切位置, 或是實際的存取動作, 都是由作業系統在管理的, 但 SQL Server 自己也對檔案的使用方式有一套完整的架構, 我們可稱之為資料庫的實體架構。 • 在建立新的資料庫時, SQL Server 預設就會建立兩個檔案供資料庫使用, 而必要時我們也能建立新的檔案供現有的資料庫使用。

30. 檔案 • 前面說過, 在 SQL Server 中建立新資料庫時, 預設會建立兩個檔案, 其中一個就是主要資料檔, 另一個則是記錄所有對資料庫更新動作的記錄檔。但資料庫可使用的檔案並不限於這兩者, 在 SQL Server 中將資料庫使用的檔案分為三類： • 主要資料檔 (Primary data file)：每個資料庫都會有一個主要資料檔, 而且也只會有一個。如果沒有特別的需求, 在建立資料庫時, 使用 SQL Server 所提供的預設檔名做為主要資料庫檔的名稱就可以了。

31. 檔案 • 次要資料檔 (Secondary data file)：這種檔案預設是不會產生的, 但是當您希望讓SQL Server 能將資料庫的資料存於主要資料檔以外的其它檔案時, 就可建立次要資料檔。次要資料檔的數量, 原則上是沒什麼限制, 也就是說您可為資料庫建立多個次要資料檔, 讓資料分散存於各檔案中。

32. 檔案 • 交易記錄檔 (log file)：交易記錄檔和主要資料檔一樣都是在建立資料庫時就會產生的, 但是它也和次要資料庫檔一樣, 我們可以為單一個資料庫設定多個交易記錄檔。交易記錄檔並不是用來記錄資料庫的資料內容, 而是記錄使用者對資料庫所做的各項異動, 有了對資料庫內容更改的完整記錄, 當 SQL Server 發生問題而導致資料庫內容有誤時, 就能藉由交易記錄檔內的資訊, 來復原資料庫的資料了。

33. 檔案 • 由於在建立資料庫時, 就會先建好主要資料檔, 若沒什麼特別需求的話, 就可這樣一直用下去, 也就是以主要資料檔為唯一存放資料庫的檔案。不過在一些較特別的情況下, 您可能就需採取不同的檔案使用方式。 • 例如在系統建立之初, 磁碟的容量還足夠存放我們的資料庫, 但經過一段時後, 磁碟空間不足而加裝新的磁碟, 此時就可在新磁碟上建立次要資料檔, 以便我們能繼續新增資料。

34. 檔案 • 再考慮另一個較複雜的例子, 若資料庫的使用者眾多, 查詢動作也非常多, 就檔案的使用面而言, 有人就會想到將此檔案放在系統中速度較快的磁碟或 RAID 磁碟陣列上以提昇存取效率。 • 但若此資料庫很大又只用到一個主要資料檔, 可能會造成磁碟空間不足的問題, 此時就應及早替資料庫規劃出次要資料檔, 並分散於不同的磁碟。

35. 檔案 • 而且要讓較常被用的資料庫物件 (例如索引), 存放在速度較快的硬碟上;如此不但讓速度較快的硬碟能物盡其用, 也能使資料庫的存取效率有所提昇。 • 不過我們並不能要求 SQL Server 將哪個資料表、索引放在指定的資料檔中, 在建立資料表和索引的 SQL 敘述中, 都只能用檔案群組來指定存放的位置, 以下就來看如何使用檔案群組。

36. 檔案群組 • 當我們為資料庫設定了多個檔案時, 在檔案的配置和管理上將會變得較為複雜, 因此 SQL Server 特別提供了檔案群組 (filegroup) 的機制, 以方便我們管理資料庫的檔案。就如上一小節所述的, 在 SQL Server 中, 我們對於資料庫的檔案使用方式, 是以檔案群組來指定, 而不能以檔案來指定。檔案群組也是分為三種：

37. 檔案群組 • 主檔案群組 (Primary filegroup)：內含主要資料檔的檔案群組, 也就是在建立資料庫時預設就會產生的檔案群。若在為資料庫加入其它的次要資料檔時, 未特別指定檔案所屬的檔案群組, 則預設這些檔案也都是放在主檔案群組中。通常系統資料表的內容也都是放在主檔案群組的資料檔中。 • 使用者定義檔案群組 (User-defined filegroup)：凡是由我們自行建立的檔案群都屬於此類。

38. 檔案群組 • 預設檔案群組 (Default filegroup)：預設檔案群組並不是指主檔案群組和使用者定義檔案群組以外的另一種檔案群組, 而是指目前資料庫預設使用的檔案群組,在建立新的資料庫物件時, 若未指定要建立在哪一個檔案群組, 則這些物件都會被放在預設檔案群組中。 • 在沒有特別指定的情況下,因為預設檔案群組就是主檔案群組, 所以資料庫大概都只會用到主檔案群組。

39. 檔案群組 • 但若您有建立使用者定義檔案群組, 並想指定使用者定義檔案群組為預設檔案群組的話, 可用如下的 SQL 語法：

40. 檔案群組 • 這樣隨後所建立的資料庫物件, 就會放在您所指定的檔案群組之中了。下圖就是將 MyData 資料庫中的 Secondary 檔案群組設為預設檔案群組的情形：

41. 檔案群組 • 除了直接更改預設檔案群組外, 在 CREATE TABLE 和 CREATE DATABASE敘述中也都可用 ON 參數來設定要將資料表或資料庫放在哪個群組中, 例如：

42. 資料檔的大小 • 資料庫中的資料是不時在更動的, 隨時都可能會有新增的資料, 當然也會有資料被刪除的情形, 換句話說, 資料檔實際使用的磁碟空間是經常在變動的, 當資料增加的量超過資料檔原有的可用空間時, 就需配置新的磁碟空間給資料檔使用。 • 而資料庫異動頻繁, 若不斷進行配置新磁碟空間的動作, 也是會影響到伺服器的效能。

43. 資料檔的大小 • 因此若能依據資料成長情形, 來設定資料檔使用磁碟空間的方式 , 將可減少不當的空間配置、對伺服器效能的負面影響、或是佔用多餘磁碟空間的情形。 • 前面說過, 新建資料庫時是以 model 資料庫為藍本, 因此包括初始資料檔的設定也都是延用 model 資料庫的資料檔設定。

44. 資料檔的大小 • 例如預設 model 資料庫的資料檔大小為 3MB、成長時每次增加 1MB、且無成長的上限, 所以新建資料庫時預設也是採用此項設定。當然在新建資料庫時可自行更改此設定 (或至 model 資料庫的屬性交談窗修改預設值)：

45. 資料檔的大小

46. 資料檔的大小 • 上圖所示的就是預設的檔案成長設定, 其中自動成長這一欄就表示當檔案空間不足時, SQL Server 就會自動以指定的方式加大檔案。 • 不過上圖中的這個預設值其實並不適用所有資料庫, 以一個日後可能要容納數十萬、數百萬筆記錄的資料庫而言, 可能需要數百 MB 的空間來存放。

47. 資料檔的大小 • 若以一開始使用 3MB 的大小, 然後每次增加個 1MB, 不難想見, 這將會造成前面說過的配置磁碟空間的額外負擔, 而且也很容易造成檔案存放空間不連續, 對伺服器的效能會有不良的影響。

48. 資料檔的大小 • 不過若一開始就先配置太大的磁碟空間給檔案, 也並非明智的做法, 除了浪費磁碟空間外, 也會增加檔案處理的負擔。 • 其中最嚴重的就是備份了, 因為 SQL Server 在備份資料庫時, 是將所有的資料檔都複製到備份裝置中, 因此若一開始就建立了遠超過所需磁碟空間的大檔案, 以後在做備份時等於要備份大半的未用空間, 不但浪費時間也浪費備份資源。

49. 資料檔的大小 • 因此比較好的作法就是先大略估計一下資料庫可能的大小, 並據以配置適當的磁碟空間給資料檔, 然後設定合適的成長方式以求在善用磁碟空間和維護存取效率之間達到平衡。至於資料庫需要多少的磁碟空間呢? • 由於資料庫中最主要的就是存放在各資料表中的資料, 因此要估算資料庫的大小, 就需先估算出資料庫中各資料表的大小。

50. 資料表空間的估算 • 由於 SQL Server 使用的是二維式的資料表, 所以直覺上資料表大小就是記錄筆數乘上每一筆記錄的大小 (即各欄位大小總和), 但事實上卻非如此, 因為我們可能會用到非固定大小的欄位, 另外 SQL Server 還會使用一些額外的空間做資料的管理。