資料庫的建立與規(guī)劃第4章本章重點4-1認識系統(tǒng)資料庫4-2系統(tǒng)資料表與檢視表4-3資料庫的規(guī)劃4-4SQLServer資料庫的實體結構4-5索引的結構與運作方式24-1認識系統(tǒng)資料庫SQLServer將與伺服器本身有關的各項資訊,都存放在特定的資料庫中,這些資料庫稱為『系統(tǒng)資料庫』；相對於系統(tǒng)資料庫,凡是由我們自行建立的資料庫則稱為使用者資料庫。在安裝SQLServer時,就會先建好master、msdb、model、tempdb

這四個基本的系統(tǒng)資料庫,所以我們在ManagementStudio中瀏覽伺服器時,於系統(tǒng)資料庫節(jié)點下即可看到這些資料庫,而且我們也不能刪除這些資料庫。除此之外,還有一個隱藏的Resource資料庫,但在ManagementStudio中看不到它。以下簡單說明這幾個系統(tǒng)資料庫的用途。3mastermaster資料庫記錄的是有關SQLServer的資訊,包括所有的登入帳戶、系統(tǒng)的組態(tài)、各資料的初始資訊等各類重要資料。SQLServer基於安全性的考量,不允許我們直接瀏覽、修改各資料庫中的系統(tǒng)資料表,而是必須透過系統(tǒng)檢視表(systemview)來瀏覽。我們可用"select*fromsysobjectswheretype='S'"來查看master資料庫中有多少隱藏起來的資料表。4master由於master資料庫的內容對整個資料庫系統(tǒng)的關係重大,因此最好要定時備份此資料庫的內容。在下一節(jié)會對master資料庫中幾個重要的檢視表做簡單的介紹。5msdbmsdb

是另一個供系統(tǒng)使用的資料庫,其主要用途是供SQLServerAgent做各類排程作業(yè)(job)所用的資料庫。除了SQLServerAgent的資料外,有關備份和還原的記錄(參見第8、9章)、複寫(參見第10章)和資料維護計劃(見第12章)等資訊也都是放在這個資料庫中。因此,若您未設定這類作業(yè),此資料庫中也不會有什麼特別的內容。6msdb同樣的,此資料庫的內容是不宜由我們直接做更動的,不過有需要的話,倒是可在msdb

資料庫查閱一下作業(yè)的資訊。例如由sysjobhistory

可查看執(zhí)行過的各項作業(yè)之結果。7modelmodel是個較特殊的系統(tǒng)資料庫,或許應稱它為『樣板』資料庫。當我們在SQLServer中建立新的資料庫時,SQLServer會以model資料庫為藍本,將其內容複製到我們的新資料庫,因此在所有新建的資料庫中,都會有和model資料庫內容一樣的資料表和檢視表等資料庫物件。8modelmodel和其它系統(tǒng)資料庫不同：有需要的話,我們可以更動其內容。由於在建立新資料庫時,SQLServer會複製model的內容至新的資料庫中,因此若您希望在日後所建的資料庫中都要加入某項物件,例如某個特殊的資料庫使用者或角色等,就可以先在model資料庫中建立這些物件。以後在SQLServer中建立新的資料庫時,這些物件也都會出現(xiàn)在新資料庫中,省下自己再另行建立的麻煩。9tempdb由名稱就可看出,tempdb

是用來存放暫時性資料用的,像是使用者在進行各種查詢或排序時,SQLServer就會在此建立這些暫時性的工作資料表。由於是"暫時性"的,所以tempdb

中的資料沒有什麼保存的價值,因此每次SQLServer重新啟動時,都會重建一份新的tempdb

資料庫。由於tempdb

資料庫是用來存放各項作業(yè)的暫時性資料,所以當工作資料量變多時,tempdb

資料庫的大小也會跟著變大,其大小是由SQLServer依需要自動調整的,就像SQLServer會自動調整所用的記憶體空間一樣。10tempdb因為每次SQLServer啟動時就會重新建立tempdb,而前面又說過SQLServer在新建資料庫時會將model資料庫的內容複製到新資料庫中,所以tempdb

自然也會包含和model相同的資料庫物件。因此有些人就會想在model中建立一些物件讓tempdb

來繼承,像是可以讓所有使用者存取tempdb

的權限等等,不過使用此方法時也要注意,在建立其它非tempdb

的使用者資料庫時,就要記得去檢查是否要將這些繼承自model資料庫的物件刪除掉或更改其設定。11Resource雖然在ManagementStudio中根本看不到這個資料庫,但只要用檔案總管進入SQLServer的資料庫檔資料夾(例如ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\

Binn,讀者電腦的路徑可能與此不同),就可以看到Resource資料庫的資料檔及交易記錄檔mssqlsystemresource.mdf、mssqlsystemresource.ldf。此資料庫檔還不算小,因為它存放了許多與SQLServer本身相關的系統(tǒng)物件,使用者物件都不會存放在Resource資料庫中。12ResourceSQLServer2008採用Resource資料庫的目的之一,就是讓系統(tǒng)資源集中存放管理,日後將可透過升級Resource資料庫的方式,即可升級SQLServer2008的功能。13認識系統(tǒng)資料庫以上簡單介紹了SQLServer中內建的系統(tǒng),除了這些系統(tǒng)資料庫外,在每個使用者資料庫中,也會有一些系統(tǒng)內建的物件,其中最重要的就是系統(tǒng)資料表和檢視表,下一節(jié)就來看如何用系統(tǒng)檢視表查看SQLServer系統(tǒng)資料表的內容。144-2系統(tǒng)資料表與檢視表系統(tǒng)資料表就是由SQLServer自己建立及使用的資料表,若再加以細分,可將之分為每個使用者資料庫都有的系統(tǒng)目錄

(SystemCatalog)資料表,以及master、msdb

等資料庫專有的系統(tǒng)資料表。但正如本章開頭所述,SQLServer不允許我們任意瀏覽這些系統(tǒng)資料表的內容,而是必須透過SQLServer提供的系統(tǒng)檢視表(用來檢視系統(tǒng)目錄的系統(tǒng)檢視表,又稱為目錄檢視表,Catalogview)。15系統(tǒng)資料表與檢視表由於目錄檢視表、系統(tǒng)檢視表相當多,以下僅擇要介紹,讓讀者對資料庫內部結構有一些基本認識,完整的系統(tǒng)檢視表資訊請參考線上叢書的介紹。16與資料庫資訊相關的目錄檢視表在model這個樣版資料庫中查詢sysobjects

檢視表,可發(fā)現(xiàn)會被複製到每個新資料庫的系統(tǒng)目錄資料表共有45個之多(type欄位值為's'者即為系統(tǒng)目錄資料表)。這些資料表都是用來記錄與該資料庫相關的各項資訊,透過目錄檢視表即可看到這些資料表中記錄了哪些資訊,以下介紹幾個重要的檢視表及它們所能查看的資訊。17與資料庫資訊相關的目錄檢視表sys.allocation_units：列出有關配置單元(Allocationunit,參見4-4節(jié))的資訊。sys.columns：顧名思義,此檢視表會傳回有關欄位(Column)的各項資訊,資料庫中所有資料表和檢視表中的欄位,以及預存程序中的變數(shù),在此資料表中都會有一列記錄其資訊。例如若資料表中有個名為'TelephoneNumber'的欄位,就能在sys.columns

中找出它的相關記錄。18與資料庫資訊相關的目錄檢視表19與資料庫資訊相關的目錄檢視表sys.check_constraints：傳回條件約束(Constrain)與欄位的對應關係,也就是哪一個欄位要套用哪一個條件約束。所以若資料庫中設定的條件約束多,此檢視表傳回的記錄也會隨之增加。sys.database_files：傳回資料庫所有檔案的資訊,例如檔案的識別碼、所屬檔案群組的識別碼、檔案大小、和檔案的路徑等等。sys.database_permissions：傳回目前資料庫的存取權限資訊。20與資料庫資訊相關的目錄檢視表sys.database_principals：由其名稱可看出,此檢視表會傳回此資料庫中所有使用者與角色物件(統(tǒng)稱為資料層級的安全性主體,Principal)的資訊,包括主體名稱、識別碼、類型、預設結構描述、建立及修改日期等。sys.database_role_members：會傳回資料庫中各角色的識別碼及其成員的識別碼。sys.filegroups：檢視這個資料庫所擁有的檔案群組資訊,例如群組的識別碼和群組的名稱等。sys.foreign_keys：檢視我們在資料表中所設的FOREIGNKEY資訊。21與資料庫資訊相關的目錄檢視表sys.fulltext_catalogs：檢視此資料庫中的全文檢索目錄資訊,包括全文檢索目錄的名稱以及存放的路徑等,若未在該資料庫中建立全文檢索目錄,則此資料表會是空白的。sys.indexes：雖然名稱中只有index這個字,不過除傳回索引的資訊外,也包含heap的資訊(未建立叢集索引的資料表結構即稱為heap)。sys.index_columns：記錄資料庫中所設的索引鍵資訊,例如所在的資料表及欄位編號。22與資料庫資訊相關的目錄檢視表sys.objects：檢視資料庫所有使用者物件的資訊,包括物件名稱、物件識別碼、物件的類型、和擁有者的使用者識別碼等；另外還有個sys.system_objects

則會傳回系統(tǒng)物件的資訊。如果想查看資料庫中『所有』物件,則可查看sys.all_objects

或執(zhí)行系統(tǒng)預存程序sp_help。sys.server_permissions：傳回目前伺服器的存取權限資訊。23與資料庫資訊相關的目錄檢視表sys.sql_dependencies：檢視所有檢視表、預存程序、和觸發(fā)程序這三類物件與其資料來源物件(資料表、檢視表、或預存程序)之間的依存關係,以及在其定義中所含的其它物件。不過此表列出的都是數(shù)字性的識別碼等資訊,不太適合我們查詢,如果想查看文字性的資訊,可使用系統(tǒng)預存程序sp_depends。sys.sql_modules：列出資料庫中所有檢視表、觸發(fā)程序、預存程序、和規(guī)則等物件的資訊,如果您查詢這個檢視表的內容,可在其中的definition欄看到上列物件的原始SQL語法定義。不過請勿自行手動更動這些內容,因為如此可能會使您的檢視表或預存程序等物件失效。24與資料庫資訊相關的目錄檢視表sys.types：檢視系統(tǒng)內建型別和在這個資料庫中所建立的使用者自訂型別,包括型別的名稱、是否為nullable

、及其長度等各項資訊。25與系統(tǒng)資訊相關的檢視表除了前述的目錄檢視表外,另外還有許多特別的系統(tǒng)檢視表,可用來查看與整個SQLServer系統(tǒng)相關的資訊,以下介紹其中幾個實用的檢視表。sys.backup_devices：可檢視已建立的備份裝置之相關資訊,包括裝置名稱、類型、路徑。26與系統(tǒng)資訊相關的檢視表sys.configurations：可檢視系統(tǒng)的組態(tài)資訊,建議您對master資料庫執(zhí)行一下"select*fromsys.configurations"敘述來看看SQLServer到底有哪些組態(tài)設定,如此可大致認識一下我們能對SQLServer做什麼樣的調校。在傳回結果中,value_in_use

欄位表示的是『執(zhí)行中』的SQLServer所採用的設定值；若您正好更改了某項系統(tǒng)組態(tài),那麼這個新的且尚未生效的設定值則會顯示在values欄位,待下次重新啟動SQLServer或執(zhí)行"RECONFIGURE"指令後,新的設定值才會生效。關於系統(tǒng)組態(tài)設定,請參見13-6節(jié)的介紹。27與系統(tǒng)資訊相關的檢視表sys.databases：檢視SQLServer中所有資料庫的資訊,包括資料庫的名稱、建立日期、目前所用的復原模式設定值等。sys.dm_tran_locks：列出目前已設定的所有鎖定(lock)資訊。sys.linked_logins：列出連上各連結伺服器(參見附錄B)所用的帳號設定。sys.master_files：檢視由SQLServer所管理的各項檔案資訊。sys.messages：列出SQLServer所有的錯誤訊息(包括所有語言的版本)。28與系統(tǒng)資訊相關的檢視表sys.remote_logins：列出從遠端SQLServer伺服器登入的使用者資訊。sys.servers：列出所有此SQLServer可存取的遠端SQLServer伺服器(參見附錄B)。sys.server_principals：列出這個SQLServer所有的登入帳戶資訊,包括登入名稱、預設資料庫和語系等。sys.syscharsets：檢視SQLServer所支援的各種字元集和排序方式的資訊,也就是在第2章提過在重建系統(tǒng)資料庫時所用的定序參數(shù)值,例如id為196的記錄,代表的就是使用繁體中文字集(CodePage950)並使用二元排序方式。29其它系統(tǒng)檢視表為符合SQL-92標準的規(guī)範,SQLServer也提供另一組系統(tǒng)檢視表,稱為資訊結構描述檢視表,它們都定義在INFORMATION_SCHEMA這個特別的結構描述中。這些檢視表所傳回的內容,其實都是透過前述的系統(tǒng)檢視表取得,至於屬於SQLServer

特有的(非SQL-92標準)系統(tǒng)資訊,將無法透過資訊結構描述檢視表取得。30其它系統(tǒng)檢視表例如查詢INFORMATION_SCHEMA.tables

會傳回資料庫中所有使用者資料表及檢視表的名稱、所屬結構描述等簡單資訊；而查詢sys.tables

雖然只會傳回使用者資料表的資訊,但資訊內容還包括建立日期、修改日期、以及多項屬性的狀態(tài)等特殊的資訊。如果是開發(fā)應用程式需存取SQLServer、Oracle、DB2等異質資料庫,為求方便當然是使用符合標準的INFORMATION_SCHEMA中的各個檢視表；但若是想得知較詳細的SQLServer系統(tǒng)資訊,則仍應使用SQLServer的系統(tǒng)檢視表及目錄檢視表。314-3資料庫的規(guī)劃資料庫本身也是由SQLServer透過作業(yè)系統(tǒng),以檔案的形式存於磁碟之中。不過雖然檔案存於磁碟上的確切位置,或是實際的存取動作,都是由作業(yè)系統(tǒng)在管理的,但SQLServer自己也對檔案的使用方式有一套完整的架構,我們可稱之為資料庫的實體架構。32資料庫的規(guī)劃在建立新的資料庫時,SQLServer預設就會建立兩個檔案供資料庫使用,而必要時我們也能建立新的檔案供現(xiàn)有的資料庫使用。這些檔案的規(guī)劃與管理,也是資料庫管理中的一項重要課題。以下就來看看SQLServer的檔案架構,以及我們在建立資料庫時應如何規(guī)劃其檔案的使用。關於如何建立新資料庫,相信大部分的讀者都已瞭解,此處就不再多做介紹。若想學習建立資料庫的基本方法,可參考本公司『MicrosoftSQLServer2008設計實務』一書的介紹。33檔案前面說過,在建立新資料庫時,預設會建立兩個檔案,其中一個就是主要資料檔,另一個則是記錄所有對資料庫更新動作的交易記錄檔。但資料庫可使用的檔案並不限於這兩者,在SQLServer中將資料庫使用的檔案分為三類：主要資料檔(Primarydatafile)：每個資料庫都會有一個主要資料檔,而且也只會有一個。如果沒有特別的需求,在建立資料庫時,使用SQLServer所提供的預設檔名做為主要資料檔的名稱就可以了。34檔案次要資料檔(Secondarydatafile)：這種檔案預設是不會產生的,但是當您希望讓SQLServer能將資料庫的資料存於主要資料檔以外的其它檔案時,就可建立次要資料檔。次要資料檔的數(shù)量,原則上是沒什麼限制,也就是說您可為資料庫建立多個次要資料檔,讓資料分散存於各檔案中。35檔案交易記錄檔(logfile)：交易記錄檔和主要資料檔一樣都是在建立資料庫時就會產生的,但是它也和次要資料庫檔一樣,我們可以為單一個資料庫設定多個交易記錄檔。交易記錄檔並不是用來記錄資料庫的資料內容,而是記錄使用者對資料庫所做的各項異動,有了對資料庫內容更改的完整記錄,當SQLServer發(fā)生問題而導致資料庫內容有誤時,就能藉由交易記錄檔內的資訊,來復原資料庫的資料了。36檔案由於在建立資料庫時,就會先建好主要資料檔,若沒什麼特別需求的話,就可這樣一直用下去,也就是以主要資料檔為唯一存放資料庫的檔案。不過在一些較特別的情況下,您可能就需採取不同的檔案使用方式。例如在系統(tǒng)建立之初,磁碟的容量還足夠存放我們的資料庫,但經過一段時後,磁碟空間不足而加裝新的磁碟,此時就可在新磁碟上建立次要資料檔,以便我們能繼續(xù)新增資料。37檔案再考慮另一個較複雜的例子,若資料庫的使用者眾多,查詢動作也非常多,就檔案的使用面而言,有人就會想到將此檔案放在系統(tǒng)中速度較快的磁碟或RAID磁碟陣列上以提昇存取效率。但若此資料庫很大又只用到一個主要資料檔,可能會造成磁碟空間不足的問題,此時就應及早替資料庫規(guī)劃出次要資料檔,並分散於不同的磁碟。38檔案而且要讓較常被用的資料庫物件(例如索引),存放在速度較快的硬碟上;如此不但讓速度較快的硬碟能物盡其用,也能使資料庫的存取效率有所提昇。不過我們並不能直接要求SQLServer將哪個資料表、索引放在指定的資料檔中,在建立資料表和索引的SQL敘述中,都只能用檔案群組來指定存放的位置,以下就來看如何使用檔案群組。39檔案群組當我們?yōu)橘Y料庫設定了多個檔案時,在檔案的配置和管理上將會變得較為複雜,因此SQLServer特別提供了檔案群組(filegroup)的機制,以方便我們管理資料庫的檔案。就如上一小節(jié)所述的,在SQLServer中,我們對於資料庫的檔案使用方式,是以檔案群組來指定,而不能以檔案來指定。40檔案群組檔案群組也是分為三種：主檔案群組(Primaryfilegroup)：內含主要資料檔的檔案群組,也就是在建立資料庫時預設就會產生的檔案群。若在為資料庫加入其它的次要資料檔時,未特別指定檔案所屬的檔案群組,則預設這些檔案也都是放在主檔案群組中。通常系統(tǒng)資料表的內容也都是放在主檔案群組的資料檔中。使用者定義檔案群組(User-definedfilegroup)：凡是由我們自行建立的檔案群都屬於此類。41檔案群組預設檔案群組(Defaultfilegroup)：預設檔案群組並不是指主檔案群組和使用者定義檔案群組以外的另一種檔案群組,而是指目前資料庫預設使用的檔案群組,在建立新的資料庫物件時,若未指定要建立在哪一個檔案群組,則這些物件都會被放在預設檔案群組中。42檔案群組在沒有特別指定的情況下,因為預設檔案群組就是主檔案群組,所以資料庫大概都只會用到主檔案群組。但若您有建立使用者定義檔案群組,並想將之設定為預設檔案群組的話,可用如下的SQL語法：43檔案群組這樣隨後所建立的資料庫物件,就會放在您所指定的檔案群組之中了。下圖就是將MyData

資料庫中的Secondary檔案群組設為預設檔案群組的情形：44檔案群組除了直接更改預設檔案群組外,在CREATETABLE和CREATEDATABASE敘述中也都可用ON參數(shù)來設定要將資料表或資料庫放在哪個群組中,例如：45資料檔的大小資料庫中的資料是不時在更動的,隨時都會有新增與刪除資料的情形。換句話說,資料檔實際使用的磁碟空間是經常在變動的,當資料增加的量超過資料檔原有的可用空間時,就需配置新的磁碟空間給資料檔使用。而資料庫異動頻繁,若不斷進行配置新磁碟空間的動作,也是會影響到伺服器的效能。因此若能依據資料成長情形,來設定資料檔使用磁碟空間的方式,將可減少不當?shù)目臻g配置、對伺服器效能的負面影響、或是佔用多餘磁碟空間的情形。46資料檔的大小前面說過,新建資料庫時是以model資料庫為藍本,因此包括初始資料檔的設定也都是延用model資料庫的資料檔設定。例如預設model資料庫的資料檔大小為3MB、成長時每次增加1MB、且無成長的上限,所以新建資料庫時預設也是採用此項設定。當然在新建資料庫時可自行更改此設定(或至model資料庫的屬性交談窗修改預設值)。47資料檔的大小48資料檔的大小上圖所示的就是預設的檔案成長設定,其中自動成長這一欄就表示當檔案空間不足時,SQLServer就會自動以指定的方式加大檔案。不過上圖中的這個預設值其實並不適用所有資料庫,以一個日後可能要容納數(shù)十萬、數(shù)百萬筆記錄的資料庫而言,可能需要數(shù)百MB的空間來存放,若以一開始使用3MB的大小,然後每次增加個1MB,不難想見,這將會造成前面說過的配置磁碟空間的額外負擔,而且也很容易造成檔案存放空間不連續(xù),對伺服器的效能會有不良的影響。49資料檔的大小不過若一開始就先配置太大的磁碟空間給檔案,也並非明智的做法,除了浪費磁碟空間外,也會增加檔案處理的負擔。其中最嚴重的就是備份了,因為SQLServer在備份資料庫時,是將所有的資料檔都複製到備份裝置中,因此若一開始就建立了遠超過所需磁碟空間的大檔案,以後在做備份時等於要備份大半的未用空間,不但浪費時間也浪費備份資源。50資料檔的大小因此比較好的作法就是先大略估計一下資料庫可能的大小,並據以配置適當?shù)拇诺臻g給資料檔,然後設定合適的成長方式以求在善用磁碟空間和維護存取效率之間達到平衡。至於資料庫需要多少的磁碟空間呢?由於資料庫中最主要的就是存放在各資料表中的資料,因此要估算資料庫的大小,就需先估算出資料庫中各資料表的大小。51資料表空間的估算由於SQLServer使用的是二維式的資料表,所以直覺上資料表大小就是記錄筆數(shù)乘上每一筆記錄的大小(即各欄位大小總和),但事實上卻非如此,因為我們可能會用到非固定大小的欄位,另外SQLServer還會使用一些額外的空間做資料的管理。在SQLServer的線上叢書中已為我們提出一種可估算資料表空間的方式,由於詳細的計算內容非常複雜,在此不做深入的介紹,有興趣者請參考SQLServer線上叢書的『估計資料庫的大小』主題。52檔案的成長與縮減雖然我們可事先估算資料表所需的空間,據以設定合適的磁碟空間給資料檔。但除非要建立的資料表,其資料量是已經固定不會再變動的,否則一般情況下資料表所佔空間是隨著時間逐漸增加的。一方面我們很難準確算出未來實際的資料量,二方面讓資料檔一開始就佔用太多暫時不會用到的空間也不太經濟,因此讓資料檔隨時間成長仍是不可免的。所以我們再回頭看看在建立檔案時的成長參數(shù)設定。53檔案的成長與縮減除了每次成長的數(shù)量外,另一個要考慮的就是成長的上限,也就是您允許這個資料庫最多佔用多少的磁碟空間。預設是不限制檔案成長,也就是只要有新的資料進來,磁碟也還有可用空間,就繼續(xù)配置新的磁碟空間給檔案。不過讓檔案無限制成長並不一定是個好的做法,必要時也可設定個上限,以免檔案佔用大半的磁碟空間,而影響其它程式或資料庫的使用。54檔案的成長與縮減例如有人會將檔案大小上限設為初始大小的兩、三倍,設若一開始資料檔大小設為100MB,就可將其上限設為200或300MB。SQLServer2008資料檔大小的理論上限為16TB；交易記錄檔則為2TB。55手動加大檔案除了妥善設定檔案的成長大小和成長上限外,若想對檔案的大小做更嚴格的控制,就是取消檔案自動成長這個選項,將檔案設為不會自動成長,而在有需要時再自行用SQL語法或ManagementStudio來增加檔案大小。要用SQL語法增加檔案大小,可使用ALTERDATABASE敘述,例如以下敘述就是將Test資料庫中的Test資料檔設為10MB：56手動加大檔案要注意的是,以ALTERDATABASE敘述修改檔案大小時,將不受先前設定檔案大小上限的限制,例如原本Test.MDF(或NDF)檔的大小上限是8MB,則執(zhí)行上列的敘述仍能順利將檔案變成10MB,且檔案上限也會自動調整為10MB。57檔案的縮減有成長自然也有縮小,當資料檔很大而且內部的空間使用率很低時,就應設法移除未使用空間,以縮減檔案大小並釋放磁碟空間。在建立資料庫檔案時並無相關的設定,而預設也沒有自動縮減的動作,因此想要替檔案減肥,需自己手動進行。SQLServer提供2種縮減資料檔的方法,第一種是『壓縮資料庫』,另一種則是『壓縮檔案』。要壓縮資料庫,可於ManagementStudio中,在要處理的資料庫名稱上按滑鼠右鈕執(zhí)行『工作/壓縮/資料庫』命令,然後如下操作。58檔案的縮減59檔案的縮減壓縮後檔案的最大可用空間欄位是用來設定壓縮後的可用空間,不要超過我們指定的百分比(預設為0%),接著按確定鈕,就會開始減肥的動作。使用上述的方法,是針對資料庫壓縮,至於要如何壓縮各資料庫檔,則是由SQLServer自行判斷。如果您想自訂單一檔案的壓縮方式,可改為執(zhí)行『工作/壓縮/檔案』命令,此時會出現(xiàn)如下的畫面。60檔案的

縮減61檔案的縮減除了使用ManagementStudio外,我們也可用DBCC這個特別的敘述來替檔案減肥,有關DBCC的使用請參見附錄C。62記錄檔資訊的清除資料庫的異動情形都會記錄在交易記錄檔中,以便資料庫有誤時,才能能根據這些資訊來回復至正確的狀態(tài),因此交易記錄檔所記錄的資料也會隨時間而增加。交易記錄檔雖然也有檔案大小和成長設定,不過通常它們比較不會有檔案不停地膨脹的問題。當我們進行交易記錄備份時(參見第8章),對於已確定完成的交易,因為不再需要由交易記錄來回復,所以這部分的記錄會自動被移除,而空出的檔案空間就可繼續(xù)供新的交易記錄使用。因此我們比較不需擔心資料庫用了一陣子後記錄檔變太大,然後也要替它定期做減肥的工作。63記錄檔資訊的清除除了在備份時移除不用的記錄以空出空間外,在沒有做交易記錄備份的情形下,我們也可利用資料庫的簡單復原模式,讓記錄檔能定期清除不必要的內容,以防記錄檔空間過度膨脹。在簡單復原模式下,當資料庫發(fā)生檢查點(checkpoint)時,SQLServer就會截斷(truncate)交易記錄檔中不需要的部分。64記錄檔資訊的清除檢查點會在以下幾種情況發(fā)生：當我們以sp_dboption

系統(tǒng)預存程序更改資料庫設定時,該資料庫就會發(fā)生檢查點。當我們用各種方式停止SQLServer服務時,所有的資料庫都會發(fā)生檢查點。執(zhí)行CHECKPOINT敘述或備份資料時,目前資料庫就會發(fā)生檢查點。65壓縮資料表壓縮資料表(請注意此功能與4-17頁可移除未使用空間的『壓縮資料庫』是不同的功能)可以將資料表的內容以演算法加以壓縮,以節(jié)省儲存空間。這是SQLServer2008新增的功能,而且僅限Enterprise和Developer版本才有此功能。雖然壓縮資料表會耗用部分CPU資源,但資料表壓縮之後可減少磁碟IO,所以存取資料的效率會變快,可說是以少量CPU資源來換取磁碟空間與查詢效率。66壓縮資料表關於壓縮資料表的原理、壓縮的類型,以及如何評估壓縮效益,請參考旗標出版的『MicrosoftSQLServer2008設計實務』,或參考SQLServer線上叢書的『建立壓縮資料表及索引』主題,以下僅簡要說明壓縮資料表的操作步驟。請在ManagementStudio中選擇要壓縮的資料表,按右鈕執(zhí)行『儲存體/管理壓縮』命令,略過歡迎畫面後,會出現(xiàn)如下的畫面。67壓縮資料表68壓縮資料表69使用rawpartition了解有關資料庫檔案的空間使用情況後,我們再來看另一種磁碟空間的使用。舊版的SQLServer支援一種稱做rawpartition的特別磁碟空間,所謂rawpartition是指已經用磁碟管理工具分割出的磁碟分區(qū),但尚未被格式化(FORMAT),因此作業(yè)系統(tǒng)仍無法使用該磁碟空間。舊版的SQLServer不但支援將資料存放在rawpartition上面,甚至也可由rawpartition啟動SQLServer。70使用rawpartition然而從SQLServer7.0起已經不支援將SQLServer安裝在rawpartition上面,由rawpartition直接啟動,但仍支援將資料檔放在rawpartition的作法。使用rawpartition的好處,是當資料庫讀寫頻繁到磁碟的I/O已經成為效率的瓶頸時,則使用rawpartition將可對I/O的效率有些微提昇。71使用rawpartition不過rawpartition較不值得推薦,因為在效率提昇方面,它的助益並不太大,但卻有數(shù)項『小』缺點,例如：rawpartition是作業(yè)系統(tǒng)無法使用的未格式化空間,所以平時可在作業(yè)系統(tǒng)下做的檔案處理,像是複製、搬移、和刪除檔案等操作,都不能使用。而同樣是透過作業(yè)系統(tǒng)的其它程式,像是備份軟體,也無法備份位於rawpartition中的檔案。每個rawpartition中只能有一個資料檔。72使用rawpartition使用rawpartition的步驟如下：先用磁碟管理工具建立新的分割區(qū),但不格式化。在CREATEDATABASE敘述中指定資料庫檔的存放路徑,是在前一步驟所建立的未格式化分割區(qū)上。例如若未格式化分割區(qū)的磁碟機代號是E、F,則可用如下的敘述。73使用rawpartition744-4SQLServer資料庫的實體結構在第1章曾提過,SQLServer在儲存空間的管理上,是利用Page和Extent這兩種單位配置空間給資料庫。換言之,我們建立資料庫時所建立的資料檔內部,就是利用page和extent為單位來存放資料表和索引等各種物件。本節(jié)就再深入一點,來看看在存放資料時,究竟是如何使用這兩種單位。75PagePage(頁)是最基本的儲存單位,每個Page的大小為8K位元組。當我們在空的資料表中寫入一筆記錄時,不管記錄只有30或100位元組,這時SQLServer一定是配置一個page給資料表。至於沒用到的部分,當然就是保留以供存放新加入的記錄。每個page中的記錄都是『完整』的,也就是說不會發(fā)生此頁存放前幾個欄位,後幾個欄位存在下一頁。76Page因此SQLServer並非在page空間完全用完時才配置新的page,而是當page所餘空間不足存放一筆完整的記錄時,SQLServer就會再配置另一頁給資料表存放資料。雖然page的大小是8K,但在其開頭有個96位元組大小的pageheader,用來記錄此頁的相關資訊,因此可存放資料的部分只剩下8096位元組(=8192-96)。77Page此外,每存放一筆記錄時,在頁尾都會再用掉2個位元組存放代表記錄位置的rowoffset,再加上其它的資訊,因此實際上可用來存放資料的空間,最多只有8060位元組。pageheader、記錄、

和rowoffset的存放

情形如右圖所示：78大型資料欄位及可變動欄位當記錄中包含text、ntext

、image、nvarchar(max)、varchar(max)、varbinary(max)、xml等大型欄位,或是varchar

、nvarchar

、varbinary

、sql_variant

等可變動欄位時,SQLServer會以不同的方式來儲存。79大型物件欄位對於text、ntext、image這3種大型物件欄位(統(tǒng)稱為LOB,LargeObject),在預設的情況下會另外存於專屬的page中,且當資料量較大時,還會被切割分散存到不同的page。此時各筆記錄的資料頁中,將會存放一個指向『根節(jié)點』的指標(pointer),而根節(jié)點則記錄了LOB實際存放的結構與位置,如下圖所示。80大型物件欄位81大型物件欄位上圖所示的是一般的情況,然而若text、ntext、或image的資料很少或很大時,存放的情況又會有些變化：若資料量小於64位元組,則資料會直接存於根節(jié)點結構中。若資料量超過32KB,表示資料會被切割成相當多塊以存於不同的page中,此時SQLServer會再於根節(jié)點下建立中間的節(jié)點,根節(jié)點不直接指向資料區(qū)塊,而指向這些中間節(jié)點,中間節(jié)點再指向資料區(qū)塊。82大型物件欄位由此可見,在SQLServer中存取text、ntext、和image之類的資料,其效率會比存取一般資料還差。因為存取一般資料時,讀取到資料頁就可存取到了,但存取text、ntext、和image資料時,要先到資料頁中找出該欄位的根節(jié)點位置,再由根節(jié)點找出資料實際存放的位置,最後才能讀出資料。83小型與大型的可變動欄位如果是varchar、nvarchar、varbinary、sql_variant

等可變長度的資料欄位,或是nvarchar(max)、varchar(max)、varbinary(max)、xml等大型資料欄位,SQLServer預設會將它們存於資料頁中。但若該欄位本身超過8000位元組、或是該欄位資料過多導致整筆記錄超過8060位元組時,SQLServer就會將上列欄位的資料移出,同樣是存於專用來存放varchar、nvarchar、varbinary、sql_variant

等欄位的頁面中,然後在原記錄中留下指向資料實際資料存放位置的指標。84小型與大型的可變動欄位此外,若後來修改資料時使該欄位又未超過8000位元組、且整該筆記錄的總長度小於8060位元組時,SQLServer也會自動將這些可變動欄位的資料移回資料頁中存放。85改變欄位的儲存行為上述將大型資料欄位及變動資料欄位移出資料頁的作法,都是SQLServer的預設值。我們可用系統(tǒng)預存程序sp_tableoption

來修改之,其語法如下：以設定LOB資料存放方式而言,參數(shù)名稱為'textinrow',參數(shù)值可為'ON'、'OFF'(預設值)、0、或24到7000之間的整數(shù)。86改變欄位的儲存行為當我們以sp_tableoption

設定了'textinrow'選項時,並非所有的text、ntext、和image型別資料都是放在資料頁中(inrow),必須符合以下兩個條件,這類資料才會存於資料頁：資料量小於使用sp_tableoption

時所指定的參數(shù)值。例如設為1024的話,則只有小於1024位元組的text、ntext、或image資料才會放在資料表的資料頁中。如果參數(shù)值為'ON',則大小上限預設為256位元組。資料頁中有足夠的空間存放整筆記錄。87改變欄位的儲存行為不符合以上條件的話,資料仍是另存於text、ntext、和image專用的page中,但根節(jié)點指位器卻是存於資料頁的記錄中,因此仍較沒有啟用'textinrow'選項時,有較佳的存取效能。而設定大型資料欄位(可變動欄位的max版及xml)存放方式的選項則是'largevaluetypesoutofrow',其預設值為0(或'OFF')。若改設為1(或'ON'),則就算欄位長度小於8000位元組,SQLServer也一律將nvarchar(max),varbinary(max)及xml欄位的資料移出存到專屬的頁面中。88Extent當資料表需要空間存放資料時,SQLServer是以page為單位提供給資料表。但是當資料庫需要空間來存放資料表、索引時,SQLServer是以相當於8頁大小的Extent配置空間給資料庫。之所以要一次就配置64KB的儲存空間,主要是為了效能的考量,否則資料表不斷地成長,要一直重複配置空間的動作,對SQLServer的運作會有負面的影響。89ExtentExtent可依其內的page內容分為兩種：Mixedextent：這類extent中的page是由不同的物件所使用,例如某兩頁是由A資料表使用、有一頁是給索引B使用...。當我們建立新的資料表和索引時,SQLServer就會尋找mixedextent中尚未使用的page來存放。Uniformextent：此類extent的頁次都完全由同一個資料庫物件所使用,當mixedextent中的資料表和索引的大小成長到8頁時,它們就會被存放到專供它們自己使用的uniformextent中,以提高存取的效率。90資料檔的結構在資料檔中除了被切割為page或extent以存放我們的資料外,在最開頭有幾個基本的資料結構：檔頭：佔用資料檔的第1頁,記錄關於檔案本身的資訊,此外在主資料檔及第1個交易記錄檔另有1頁稱為bootpage,記錄有關資料庫的資訊。PFS(PageFreeSpace)page：佔用檔案的第2頁,此頁的每1位元組分別代表這個檔案中某一頁是否已被佔用或剩餘空間大約還有多少,因此可記錄8000頁的使用狀態(tài)。檔案較大時,每隔8000頁就會出現(xiàn)一個PFS頁。91資料檔的結構GAM(GlobalAllocationMap)：佔用檔案的第3頁,此頁的每1位元代表檔案中某個extent是否已被使用,所以可記錄64000個extent的使用狀態(tài)。換言之,每64000extent就會出現(xiàn)一頁GAM。SGAM(SharedGAM)：佔用檔案的第4頁,此頁的每1位元代表檔案中某個extent是否為尚有剩餘空間的mixedextent,所以同樣可記錄64000個extent的使用狀態(tài),每64000extent就會出現(xiàn)一頁SGAM。92資料檔的結構除此之外,在資料檔中還有另一種管理空間用的page,就是IAM(IndexAllocationMap),雖然名稱中有index,但其實它是用來記錄資料頁和索引頁使用extent的情形,而且每個資料表和索引物件都有各自的IAM。SQLServer會視需要來建立IAM,所以IAM在檔案中的位置及數(shù)量均非固定。93資料檔的結構當SQLServer需要配置新的檔案空間來存放新增的資料時,會先到IAM看看該物件目前擁有哪些extent,並根據PFS中的記錄來找出仍有足夠空間存放新資料的page。如果物件擁有的extent中的page全都裝滿資料了,就要再配置新的extent給物件了。對SQLServer資料庫的實體結構有初步認識之後,相信您也能對SQLServer資料庫存取效率有相當?shù)捏w認,下一節(jié)我們就來看對資料庫存取效率有重大影響的索引。944-5索引的結構與運作方式前一節(jié)在介紹資料表的儲存方式時,是將記錄一筆筆地放在資料頁中,至於索引的話,存放的方式也相同,但在結構上由於採用B-Tree的結構,所以索引的結構比較複雜。本節(jié)就來介紹索引的存放結構,以及SQLServer使用索引的方式。對這兩個主題有基本的認識,在建立資料表時,就知道應如何建立索引才能有效提高查詢的效能,甚至也能瞭解如何設計索引,以提升各種不同查詢方式的效能。95索引的結構資料頁中存放的是一筆筆的記錄,至於索引頁,存放的則是每一筆記錄中的索引欄位。例如有個學生資料表是用學號做為索引,則索引頁存放的就是每位學生的學號。所以我們可將建立索引的動作,看成是將資料表中的索引欄位多複製了一份,不過這多的一份並不像存資料時一筆筆地存到資料頁一樣,而是採用B-Tree(平衡樹)的結構存放起來。96索引的結構B-Tree結構最重要的特性之一,就是從根節(jié)點到任一個末節(jié)點的距離都是一樣的,因此採用這種索引結構,可以保證索引任一筆資料的效率都是一樣快。為了達到這樣的目的,B-Tree就採用了一種特殊的演算法將所有的節(jié)點"平衡"分佈在整個樹狀結構中。以下就來看這種架構下,叢集索引、非叢集索引與資料頁的關係。97B-tree結構B-tree(Balancedtree;平衡樹)從rootnode開始將資料以左右平衡的方式排列,中間可以依實際需要而分成許多層,這種結構非常適合於搜尋資料。以下即為B-tree的架構:98叢集索引建立叢集索引時,資料表中的記錄會依索引欄位的值,依序排列存放。因此整個叢集索引的B-Tree