要在資料庫級别設定它，可使用下列指令: ALTER DATABASE AdventureWorks2008 SET PARAMETERIZATION SIMPLE ALTER DATABASE Adv...

SQL Server 2008 資料庫

資料庫是SQL Server 2008的核心，它可以用于為後面的檢索操作存儲使用者資訊，也可 以 作為SQL Server操作的臨時存儲區域。前面幾章介紹了 SQL Server的安裝過程群組成 SQL Server 2008資料庫的檔案的内部結構。本章将詳細介紹建立資料庫的過程和可配置的 各種選項。

5 .1 系統資料庫

如 第 1章所述，在 安 裝 SQL Server 2008時，建立了 5 個系統資料庫來存儲系統資訊和 支援資料庫操作。在普通的資料庫操作中，我們可以看到4 個系統資料庫(master、model、 msdb和 tempdb)，但 是 看 不 到 第 5 個(Resource資料庫，第 4 章對它做了介紹)。如 果 SQL Server執行個體被配置為用于SQL Server複制的分發伺服器，就可以建立分發資料庫。

5 .2 使用者資料庫

使用者資料庫由具有适當權限的任意伺服器登入名建立。在之前版本的SQL Server中， 可以選擇安裝第1 章中做過簡單介紹的AdventureWorks2008示例資料庫，但這一功能已從

新 版 本 中 删 除 。可從位于 www.codeplex.com/sqlserversamples 上的 Microsoft SQL Server Community Projects and Samples 中蔔載 AdventureWorks2008 示例資料庫和代碼示例。

5 .3 資料庫規劃

資料庫管理者的主要職責之一是管理資料庫的建立。通常，公司從供應商那裡購買一

個 需 要 SQL Server後端但并沒有對資料層支援進行全面規劃的應用程式。很多時候，供應 商也非常樂意上門安裝SQLServer執行個體，并建立必要的資料庫來支援該應用程式。而有時， 應用程式供應商會建立一個自動安裝和配S 資料庫的安裝程式。但是很多此類安裝的支援

資料庫的配置不是效率低下就是根本錯誤的，隻有很少一部分例外。

這并不是說軟體供應商公司的應用程式開發人員不知道他們在做什麼。實際上問題要

複雜得多。首先，開發人員幾乎不可能準确地為每個資料庫應用程式組合的安裝預測硬體

平台、資料庫的使用以及存儲的資料量，是以預設值幾乎總是錯誤的。其次，大量經驗表

明，許多應用程式開發人員完全不知道SQL Server究竟是如何工作的。他們認為這隻是一 個存放資料的地方。很多應用程式開發人員根本不知道利用或者優化資料層。

資料庫管理者應考慮為什麼資料庫像現在這樣運作和如何運作。管理資料庫的最好時

機就是在其安裝之前。無論是内部開發還是從軟體供應商那裡購買資料應用程式，資料庫

管理者都必須深入規劃和建立支援資料庫。在記住這一點後，讓我們仔細看一下資料庫創

建過程以及可在此過程中使用的配置選項。

容量規劃

規劃一個新資料庫時必須決定的第一件事是需要多大的磁盤空間來支援該資料庫。做

出這個決定時，一方面要確定資料擴充有足夠的磁盤空間可用；另一方面減少因資料擴充

而增長的資料和日志檔案的數量，以提髙資料庫效率。

如果資料庫用于支援從供應商處購買的應用程式，那麼該資料庫的容量規劃相當簡

單。然而，簡單與否取決于軟體供應商是否提供詳細的文檔。該文檔必須描述在支援定義

的使用者和事務數後資料庫的平均大小。如果供應商提供了文檔，您就可以詳細了解資料庫

的功能，進而進行相應的配置。如果供應商沒有提供相關資訊，資料庫管理者的任務就會

變得比較複雜，可能還需要猜測。然而，猜測必須是有根據的，是基于能搜集到的盡可能

多的資訊做出的。其難處常在于您可能不知道供應商是如何存儲和檢索資料的，是以必須

監控資料庫的增長趨勢，進而恰當預測存儲空間的大小。

如果資料庫是内部設計并建立的，那麼就可以使用已有的技術來确定資料檔案的大

小。這些方法之是以起作用，是因為您知道對于每個事務将添加多少資料；但在供應商提

供的資料庫中，無法獲知這一資訊。

下面是其中一種經常采用的技術：通過計算表的大小來計算資料庫的大小要求。具體

步驟如下所述：

(1) 合計表中定長列使用的總位元組數。

(2) 算出表中變長列使用的總位元組數的平均值。

(3) 将第(1)步和第(2)步得到的值相加。

(4) 用 8060(—頁中資料位元組的最大量)除以第(3)步中算出的值，然後向下舍入到最接近

的整數。該值就是單個資料頁中能放下的行數。行不能跨頁，是以需要向下舍入。

(5) 把估計的總行數除以第(4)步中算出的每頁行數。結果即是預計的支援表的資料

頁總數。

(6) 把第(5)步中算出的值乘以8192(資料頁的大小)。得出的結果就是表需要的總位元組數。

(7) 對資料庫中的每個表重複上述過程。

這聽起來很有趣，但是不建議這樣做。這種算法得出的結果是誤導人的，因為這種計

算方法沒有考慮影響存儲空間的變量，例如是否啟用壓縮、索引數、索引中使用的填充因

子以及資料碎片等。那麼為什麼還要介紹這種方法呢？因為它确實有助于了解如何計算大

小，而且您很有可能碰到這個方法，是以需要知道它的局限性。

确定資料檔案人小還有一個更為現實的方法。這種方法是先取得資料庫原型(資料庫的

測試或開發版本)，然後在其中填充适量的測試資料。之後，檢查磁盤上資料檔案的大小，

然後将其乘以1.5。其結果應該足夠容納新資料庫的初始資料量，而且還會有空間剩餘。這

個方法并不是完美的，但與上一個方法相比要簡單得多，也準确得多。

一旦資料庫投入生産環境，監控資料庫檔案大小就相當重要，因為這樣可以分析增長

趨勢。我個人喜歡配置為當資料庫中填充的資料景達到75%時發出警報。這樣可以在需要

時增加檔案的大小，但同時又能以足夠的百分比增長它們，進而避免經常執行增長。

規劃事務日志檔案的大小更加複雜。要想精确地規劃日志大小，必須知道資料庫中執

行的事務的平均大小、發生的頻率以及被修改的表的實體結構。例如，如果在存儲在堆上

的一個表(其行大小為800位元組，并且在整數列上有一個非聚集索引)上執行插入操作，會

使事務日志中的資料量增加約820位元組。這是因為新行連同新的索引行一起記錄在事務口

志中。事務日志的大小也取決于資料庫的恢複模式，以及資料庫事務日志的備份頻率。本

章稍後将介紹恢複模式。第 6 章将對索引做完整介紹。事務日忐備份及其對事務日志的影

響将在第9 章介紹。

5 .4 建立資料庫

一般通過編寫和執行T-SQL代碼或使用圖形化使用者界面來建立資料庫。無論使用哪一

種方法，資料庫建立過程中唯一需要的資訊就是新資料庫的名稱，下列代碼将建立一個名

為 SampleDB的資料庫：

CREATE DATABASE SampleDB

執行這條T-SQL語句将使SQL Server建立一個資料檔案和一個事務日志檔案,這些文

件都被放在安裝SQL Server 2008時所指定的預設位置。對于SQL Server 2008的預設執行個體

的典型安裝來說，執行這一代碼将會建立下列檔案系統對象：

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\ MSSQL\DATA\SampleDB.mdf C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\ MSSQL\DATA\SampleDB_log.ldf

第一個檔案是資料庫資料檔案，第二個是資料庫事務日志檔案。雖然這種預設行為很

友善，但一般最好還是不要使用它，因為建立的資料庫各有不同，而且一般不推薦在系統

分區上放置資料和日志檔案。在資料庫建立的過程中，可以指定資料檔案、事務日志檔案

以及資料庫選項。

5 .4 .1 開始

在建立資料庫之前，需要了解所有可用的設定和選項。本節将介紹使用圖形化使用者界

面建立資料庫的過程，并且讨論每個配置設定和選項，以及它們如何影響資料庫建立過程。

在了解了整個過程後，本節将介紹如何利用這些内容生成一個腳本。通過為資料庫名稱、

檔案名和檔案位置指定不同的值，可以重複運作該腳本。

5 .4 .2 建立一個新資料庫

使用SQL Server Management Studio以圖形化的方式建立資料庫是相當簡單和直覺的。 首先通過“開始”菜單打開SQL Server Management Studio,然後連接配接到SQL Server的資料

庫引擎。

右擊“資料庫”節點，然後單擊“建立資料庫”指令。彈出的“建立資料庫”界面如

圖 5-1所示。

在 “資料庫名稱”字段中，輸入新資料庫的名稱。當指定資料庫名稱時，要記住它最

多可以包含128個字元。SQL Server聯機叢書中還指出，資料庫名稱必須以字母或下劃線 開頭，後續字元可以是字母、數字以及一些特殊字元的組合，不過這不是強制要求。然而，

如果名稱不符合公認的标準，資料應用程式可能無法連接配接至資料庫，是以最好不要背離這

個标準。最佳實踐是使資料庫名稱盡可能具有描述性，并盡可能短。對象名稱中含有空格

也可能導緻問題，因為在以程式設計方式通路資料庫時，它們可能會導緻不可預料的問題。

“所有者”字段一般應指定為S A ,這是一個内置的SQLServer系統管理者帳戶。當 在圖形化使用者界面中建立新資料庫時，該字段采用的值為“<預設值>”，這是建立資料庫

時使用的登入帳戶。資料庫的所有者可以完全控制該資料庫。要修改資料庫所有權，可以

使用T-SQL語句ALTER AUTHORIZATION指定任意有效的登入名，如下面的代碼所示。

ALTER AUTHORIZATION ON DATABASE::SampleDB TO SA

GO

檢索資料庫資訊(例如所有者是誰)有兩種不同的方法。sp_helpdb存儲過程可用于檢 索所有資料庫或特定資料庫的資訊，非常易于用作快速檢視。要檢索所有資料庫，執行

存儲過程時不使用參數。對于特定資料庫，将資料庫名稱傳遞給該存儲過程，如下面的

代碼所示：

USE Master GO

EXEC sp_helpdb AdventureWorks2008

單獨執行和使用資料庫名稱執行的存儲過程的結果分别如圖5-2和圖5-3所示。

檢索資料庫資訊的另一種方法是使用SQL Server 2005中引入的目錄視圖。它們提供的 資訊比相應的存儲過程多，且允許使用标準的T-SQL指令，如 WHERE和 GROUP BY。

下列T-SQL語句示範了如何将sys.database目錄視圖與sys.server_principals目錄視圖聯接

起來，以檢視伺服器上所有資料庫的基本資訊(如圖5-4所示)。

SELECT db.name AS database_name,

sp.name AS owner, db.create_date,

db.compatibility_level, db.recovery一model一desc FROM sys.databases db INNER JOIN sys.server_principals sp ON db.owner_sid = sp.sid

資料庫所有者應總是為S A ,這樣可以避免任何可能發生的問題。參見第6 章了解有

關SA帳戶的更多資訊。

與 T-SQL相比，全文索引允許使用更靈活的字元串比對査詢。在新版本中，全文引擎

己移至SQL Server 2008程序中，進而可以更好地優化混合査詢和提高索引本身的性能。

1 .資料庫檔案

在 “建立資料庫”對話框中的“資料庫檔案”部分中，注意第一個資料檔案的邏輯名

稱和第一個日志檔案的邏輯名稱都已被自動命名。第一個資料檔案的名稱與資料庫的名稱

一樣，而第一個日志檔案的名稱是在資料庫的名稱後面加一個“Jog”。邏輯名稱用于在 T-SQL腳本中以程式設計方式引用檔案。在建立過程中可以指定多個檔案，每個檔案都可以具

有自己的配置設定(例如初始大小和增長行為)。

單擊“建立資料庫”對話框底部的“添加”按鈕，就會向“資料庫檔案”部分中添加

一個新的檔案行。新檔案的預設檔案類型為“行資料”，但也可通過從下拉清單中選擇選項

來将其改為“日志”或 “檔案流資料”。一旦建立好資料庫，檔案類型不可改變。

這裡采用預設的檔案類型“行資料”。為新的資料檔案輸入•個邏輯名稱，然後在“檔案組”

列中單擊下拉清單，選擇<新檔案組〉選項。“建立檔案組”對話框将會顯示，如圖5-5所示。

2 .檔案組

資料庫是基于組織在檔案組中的檔案建立的。檔案組是用來存放為資料庫定義的所有

資料和資料庫對象的資料檔案的邏輯分組。通過使用按比例填充政策，資料被條帶化到文

件組的所有檔案中。這就允許同時填滿所有資料檔案。

唯一必需的檔案組是主檔案組。主檔案組由主資料檔案和其他使用者定義的資料檔案組

成。主資料檔案的作用是存儲針對資料庫的所有系統引用，包括指向Resource資料庫中定 義的對象的指針。如果作為預設檔案組，則主檔案組包含使用者定義的對象以及系統建立的

對象的所有對象定義。除了主檔案組之外，如有需要，還可以建立更多的使用者定義檔案組。

使用使用者定義檔案組的一個最大好處可 以 歸 結 為 一 個詞：控制。通過使用者定義檔案組,

資料庫管理者可以完全控制哪些資料應該存放在什麼地方。如果沒有使用者定義檔案組，那

麼所有資料都會存儲在主檔案組中，這樣資料庫的靈活性和可擴充性都将大大削弱。雖然

對于較小資料庫來說，這可以接受，但是資料庫一旦變大，把所有的使用者和系統資料組織

到同一個檔案組中的做法就會變得越來越不可接受。

那麼什麼時候有必要分離資料？和大多數技術問題一樣，答案是“視情況而定”。需

要做出的決定取決于運作SQL Server的硬體和資料庫的通路方式，并不存在嚴格的規則。 要想了解更多有關資料分離和檔案組使用的資訊，請參閱清華大學出版社引進并出版的

Brian Knight、Ketan Patel等 著 的 《SQL Server 2008管理專家指南》一書。

輸入新檔案組的名稱，選 中 “預設值”複選框，然後單擊“确定”按鈕。這會把新的

使用者定義的檔案組設定為預設檔案組，使用者建立的所有對象都會放在這個新的檔案組中。

這實際上就把系統資料和使用者資料分離開來，進而獲得對資料庫結構的更多控制。

使用檔案組的一個不錯的功能是可以将該檔案組中的所有資料标記為“隻讀”。方法

是選擇“建立檔案組”對話框中的“隻讀”複選框。當在一個資料庫中組織不同的對象時,

這個選項相當有用。要改變的對象可以放在一個可更新的檔案組中，而那些不會(或很少)

改變的對象則放在一個隻讀的檔案組中。分離對象可以減少需要備份和還原的資料量，對

于大型資料庫來說是一個很有用的選項。

3 .優化維護還是優化性能

實作檔案組是為了優化性能還是優化維護任務？其實兩個任務都可以完成。通過将數

據分隔到表組中的多個實體檔案中，檔案組可以提升資料庫的性能和可維護性。

在維護方面具有優勢的原因在于檔案組可以備份和還原單獨檔案和檔案組，而不必備

份整個資料庫。（第 9 章将介紹檔案和檔案組備份)。這種能力對于分隔成多個檔案組的大

型資料庫很有用，而且在某些檔案組被标記為隻讀時更加有用。将讀寫資料與隻讀資料分

離可以讓資料庫管理者僅備份要修改的資料，進而可以最小化大型資料庫所需的備份和還

原時間。然而這也是有代價的。檔案和檔案組的備份政策可能變得特别複雜。維護計劃的

複雜性可能很快超出所獲得的靈活性。

檔案組所帶來的性能優勢主要有3 個方面。第一個方面是可以并行讀寫，這是通過将

資料檔案分隔到多個實體裝置實作的。然而，如果把多個實體檔案放到單個檔案組中，也

可以獲得同樣的性能。第二個方面則是将非聚集索引和大型對象資料移出作為正常資料空

間而保留的檔案組。将非聚集索引與資料分離可以讓資料庫引擎同時使用獨立的線程從索

引中搜尋行位S 和從表中檢索行。将不經常通路的大型對象資料和事務密集的關系資料分

離還可以提升一些執行個體中的掃描性能。第三個方面，也是最顯著的方面，是可以跨多個文

件組實體分區大型表(本章稍後會介紹索引和實體表分區)。

對于大多數資料庫來說，檔案組隻能提升它們的一點性能，但是能夠完全利用實體表

分區的大型資料庫除外。提升磁盤資料通路的最佳方式是實作一個健壯的廉價磁盤備援陣

列(RAID)環境。對大多數資料庫管理者來說，使用檔案組的最主要原因是它可以控制資料

存儲以及分隔系統資料和使用者資料，這和維護考慮的問題一樣。

4. 檔案大小

在 “初始大小(MB)” 列中(如圖5-1所示)，應根據對開始幾周(甚至幾個月)的檔案大小

的預計，指派一個值。在尋找房子并規劃一個大家庭時，購買一個隻有一間卧室的房子，

然後每生一個孩子就重新改造房子的做法顯然是不可取的。而購買較大的房子來容納一個

大家庭(包括未來出生的孩子)才更合理。資料庫檔案也是如此。如果一個檔案在初始幾個

月中可能要儲存1GB的資料，那隻有為其配置設定1GB的空間才有意義。作為最佳實踐，文

件大小修改次數應該控制在最少，是以應該配置設定足夠的連續磁盤空間來容納所有預計的數

據，同時還要有一定百分比的空間供資料增長使用。

5. 自動増長

單擊Primary檔案組的“自動增長”列(如圖5-1所示)右邊的省略号按鈕。彈出的“更 改自動增長設定”對話框如圖5-6所示。該對話框可以為每個單獨的檔案配置最大檔案大

小和檔案增長設定。確定選中“啟用自動增長”複選框。取消選擇該複選框會将filegrowth 屬性設定為0。

可以将檔案增長設定為一個固定的配置設定大小，或者現有檔案大小的一定百分比。作為

最佳實踐，應将自動增長選項設定為足夠大，以最小化容納資料增長所需的檔案增長數。

微量地增長檔案會導緻檔案碎片，這對于資料和日志檔案性能都是有害的。

可以限制資料和日志檔案的大小，以另一種方式對檔案大小進行控制。方法就是選擇

“限制檔案增長(MB)” 選項按鈕，然後指定一個最大大小。自動或手動的檔案增長操作都

不能超過這個大小。通常，設定一個最大檔案大小可以防止任何不恰當的程序插入數以百

萬計的行，同時還能保持對資料庫增長的控制。記住，如果資料庫達到最大大小，任何數

據修改事務都會失敗。如果出現這種情況，可以更改最大檔案大小屬性，配置設定額外的空間。

選擇的大小應是預期檔案在一定時間中具有的最大資料量。應對資料庫中的每個檔案執行

該操作。

6 . 路徑

要改變資料和日志檔案所在的路徑，可 單 擊 “建立資料庫”對話框中每個檔案的“路

徑”列右側的省略号按鈕并選擇目标檔案夾，或 是 在 “路徑”列中輸入正确路徑。放置文

件時，記住資料檔案和日志檔案絕不應該存儲在同一個實體磁盤上，否則很可能會使資料

因為磁盤或控制器出現故障而丢失。査看第3 章可了解有關檔案位置的更多資訊。

在完成新資料庫的所有正常設定後，接下來将配置資料庫選項。

7 . 資料庫選項

單 擊 “建立資料庫”對話框左上 方 的 “選擇頁”部 分 中 的 “選項”，将 顯 示 “選項”

頁，如 圖 5-7所示，在這裡可以設定幾個資料庫選項。

排序規則

單 擊 “排序規則”下拉清單，檢視可用的不同排序規則設定，但保持這一設定為“<

伺服器預設值> ”。

如 第 2 章所述，SQL Servei•的執行個體會被指派一個預設伺服器排序規則，該排序規則決 定伺服器預設支援什麼字元，以及如何搜尋和排序這些字元。排序規則設定也可以指派給

資料庫。是以，一 個 SQL Servei■執行個體被配置為使用拉丁字元集并不意味着就不能在同樣的 執行個體上建立支援韓文字元的資料庫。然而，同樣如前所述，如果資料庫排序規則與SQL

Server執行個體排序規則不同，在 tempdb資料庫中可能發生排序規則不相容的情況。

恢複模式

單 擊 “恢複模式”下拉清單并査看可用選項。可以設定的模式有：“完整”、“大容量

日志”和 “簡單”。如果未以其他方式設定model資料庫，那麼新資料庫的預設恢複模式是 完整模式。第 9 章将詳細介紹恢複模式，這裡略加解釋即可。

實際上恢複模式隻有兩個，完整和簡單。人容量日志模式附厲于完整恢複模式，在大

容量操作時使用。這是因為在完整恢複模式下，對資料庫做出的所有修改是完全記錄的。

雖然這種恢複模式對資料丢失提供了最人程度的保護，但這種保護也是有代價的。由于對

資料庫的所有修改都需要完全記錄，在特定操作(如大容景加載資料或表索引維護操作)中，

事務日志的大小會增加得非常快。大容量日志恢複模式也稱為最小日志記錄模式，在有可

能導緻事務日志迅速增大的操作中，可以将該資料庫暫時設定為大容量日志恢複模式，在

這些操作完成之後，可以再設定為完整恢複模式。

在簡單恢複模式下，每次發出檢査點之後，都會清除事務日志中的所有不活動内容。

第4 章中介紹了檢査點。簡單恢複模式的反應是，事務日志不能備份或用于資料庫恢複操

作。事務日志隻是用來保證事務的一緻性，而不會維護長期的事務曆史記錄存儲。

相容級别

單擊“相容級别”下拉清單并査看可用選項。除非有特别的理由需要改變相容級别，

一般應當将其設定為SQL Server 2008(100)o “相容級别”選項會改變一些資料庫操作的行 為，隻有在SQL Server 2008的執行個體和前一個版本的SQL Server共同承擔資料庫責任時才 有必要使用這個選項。SQL Server 2008僅允許選擇80、90和 100的相容級别，如下拉列

表所示，這些數字分别對應SQL Server 2000、SQL Server 2005和 SQL Server 2008。在以

前的版本中，可使用系統存儲過程sp_dbCmptlevel 以程式設計的方式更改相容級别。這個系統

存儲過程已被正式廢棄了，取而代之的是Transact-SQL指令ALTER DATABASE。下列代 碼将把AdventureWorks2008的相容級别設定為SQL 2000：

ALTER DATABASE AdventureWorks2008 SET COMPATIBILITY_LEVEL =80

提示：

想要完全了解各個相容級别間的所有差別，請參閱SQL Server聯機叢書中的“ALTER

DATABASE相容級别(Transact-SQL)” 主題。從 SQL Server 2000或 2005更新的資料庫的

相容模式會被設定為對應各自原有的版本。例如，從 SQL Server 2000更新到SQL Server 2008的資料庫，其相容級别将是80。

8 .其他選項

預設情況下，“建立資料庫”螢幕屮的“其他選項”會按類别組織其選項。對于本次

讨論來說，我們将按字母順序排列選項。對于這次練習，保留所有選項的預設配置。下面

逐個介紹這些選項。一些資料庫選項同時也是連接配接選項。如果是這樣，設定資料庫選項的

指令和連接配接級别選項的指令都會顯示。重要的是要知道，如果指定了連接配接級别選項，則它

會覆寫資料庫級别選項。若未指定，則資料庫選項将會生效。

單 擊 “字母順序”按鈕(該按鈕圖示顯示為字母A 和 Z 以及一個垂直向下的箭頭)。可

用的選項将以字母順序排列，如圖5-7所示。

ANSI NULL預設值

“ANSI NULL默汄值”設定指定在CREATE TABLE或 ALTER TABLE操作中添加至

表中的列是否允許空值。如 果 “ANSI NULL預設值”設定被設為False,那麼除非有顯式

抱定，否則添加的列不允許空值。當使用SQL Server Management Studio連接配接到SQL Server

時，新査詢的連接配接設定預設為ANSI NULLS O N ,該設定會覆寫資料庫設定。要在連接配接級

别或資料庫級别設定它，可以使用下列指令：

--Connection Settings SET ANSI_NULL_DFLT_ON OFF — ANSI NULL Default False SET ANSI_NULL_DFLT_ON ON --ANSI NULL Default True

—— Database Options ALTER DATABASE AdventureWorks2008 SET ANSI_NULL_DEFAULT OFF ALTER DATABASE AdventureWorks2008 SET ANSI_NULL_DEFAULT ON

ANSI NULLS已啟用

“ANSI NULLS已啟用”設定控制與NULL值進行比較的行為。當設定為True時， 與空值的任何比較所得的值均為未知。當設定為False時，如果值為空，那麼和NULL比 較的結果就為True。要在連接配接級别或資料庫級别設定它，可以使用下列指令：

--Connection Settings SET ANSI一NULLS OFF

SET ANSI_NULLS ON

—— Database Options ALTER DATABASE AdventureWorks2008 SET ANSI_NULLS OFF ALTER DATABASE AdventureWorks2008 SET ANSI_NULLS ON

提示：

“ANSI NULLS”選項将在SQL Server 2008版後被廢棄。在 SQL Server的未來版本中，

該選項将被設定為O N ,并且不允許更改。如果應用程式試圖将其值改為O FF,則将産生

錯誤。建議在新的開發工作中避免使用該選項，并着手更新目前使用該選項的應用程式。

ANSI填充已啟用

如果此選項設定為True,那麼将在定長的字元列和二進制列的末尾為字元資料添加尾 部空格，為二進制資料添加尾部零。變長的字元和二進制列不會被填充，但尾部空格或尾

部零也不會被剪裁。當把此選項設定為False時，設定為NOT NULL的定長的二進制和字 符列的行為和“ANSI填充已啟用”設定為True時是一樣的。然而，允許空值的定長的字 符列和二進制列不會被填充，任何尾部空格或尾部零也不會被剪裁。當 “ANSI填充已啟

用”設定為False時，變長列和允許空值的定長列的行為是一樣的。要在連接配接級别或資料 庫級别設定該選項，可以使用下列指令：

--Connection Settings SET ANSI_PADDING OFF

SET ANSI_PADDING ON

--Database Options ALTER DATABASE AdventureWorks2008 SET ANSI_PADDING OFF ALTER DATABASE AdventureWorks2008 SET ANSI PADDING ON

提示：

“ANSI填充”選項将在SQL Server 2008版後被廢棄.在 SQL Server的未來版本中，

該選項将被設定為O N ,并且不允許更改。如果應用程式試圖将値改為OFF,将産生錯誤。

建議在新的開發工作中避免使用該選項，并着手更新目前使用該選項的應用程式。

ANSI警告已啟用

當 “ANSI膂告已啟用”選項設定為True時，隻要聚合函數中出現空值，資料庫引擎 就會發出聱告。當設定為False時，則不會發出警告。要在連接配接級别或資料庫級别設定它, 可以使用下列指令：

--Connection Settings SET ANSI_WARNINGS OFF

SET ANSI_WARNINGS ON

--Database Options ALTER DATABASE AdventureWorks2008 SET ANSI_WARNINGS OFF ALTER DATABASE AdventureWorks2008 SET ANSI_WARNING$ ON

算術中止已啟用

當該選項設定為True時，任何語句或事務在遇到算術溢出或被零除錯誤後都将終止。 當設定為False時，會給出一個警告，但是語句或事務不會終止。要使該選項達到想要的 結果，必須将“ANSI警告”選項也設H 為 False。要在連接配接級别或資料庫級别設定它，可 以使用下列指令：

--Connection Settings SET ARITHABORT OFF

SET ARITHABORT ON

--Database Options ALTER DATABASE AdventureWorks2008 SET ARITHABORT OFF ALTER DATABASE AdventureWorks2008 SET ARITHABORT ON

自動關閉

當首次通路某個資料庫時，SQL Server會打幵和鎖定所有與資料庫相關聯的檔案。若 Auto Close值為T rue,當最後一個使用者斷開與資料庫的連接配接時，資料庫将關閉并釋放所有 檔案鎖定。此設定預設為O FF,因為在一個伺服器平台上沒有必要執行資料庫的打開和關

閉操作，而且這也會産生不必要的開銷。不過SQL Server速成版是個例外，因為SQL速 成版被設計為在桌面系統上運作，這種系統中資源較為有限，而打開的資料庫會消耗大量

資源。如果沒有使用者連接配接，可以把這些資源返還給系統。要在資料庫級别設定它，可使用

F列指令：

ALTER DATABASE AdventureWorks2008 SET AUTO_CLOSE OFF ALTER DATABASE AdventureWorks2008 SET AUTO_CLOSE ON

自動建立統計資訊

若該選項被設定為T rue,資料庫引擎将為那些在JOIN操作的WHERE子句或ON子 句中引用的、缺少統計資訊的非索引列生成統計資訊。資料庫引擎使用統計資訊确定列中

資料的選擇性和分布情況。若設定為False,則由資料庫管理者在需要時手動建立統計資訊。 要在資料庫級别設定它，可使用下列指令：

ALTER DATABASE AdventureWorks2008 SET AUTO_CREATE_STATISTICS OFF ALTER DATABASE AdventureWorks2008 SET AUTO_CREATE_STATISTICS ON

自動收縮

若該選項被設定為True,資料庫引擎将定期檢査所有資料庫檔案的總大小，并把它與 存儲的資料量進行比較。如果有超過25%的總剩餘空間，資料庫引擎将對資料庫檔案執行

檔案收縮操作，将總的可用空間減少至25%。除 SQL Server速成版之外，這個選項預設設 置 為 False。另外除資料庫會愈來愈小這樣極少見的情況外，它都應該設為False。要在數 據庫級别設定它，可使用下列指令：

ALTER DATABASE AdventureWorks2008 SET AUTO^SHRINK OFF ALTER DATABASE AdventureWorks2008 SET AUTO_SHRINK ON

自動更新統計資訊

當該選項設定為True時，資料庫引笨會自動更新列的統計資訊，進而保持最有效的査 詢計劃。這通常是在査詢執行時，査詢處理器發現了過期的統計資訊的情況下發生。如果

設定為False,那麼就需要資料庫管理者手動更新列統計資訊。要在資料庫級别設定它，可 使用下列指令：

ALTER DATABASE AdventureWorks2008 SET AUTO_UPDATE_STATISTICS OFF ALTER DATABASE AdventureWorks2008 SET AUTO_UPDATE_STATISTICS ON

自動異步更新統計資訊

當該選項設定為True時，査詢中發現的過期統計資訊将被更新，但發現這些過期統計 資訊時正在執行的查詢不會等待新的統計資訊。後續查詢将利用新的統計資訊。當設為False 時，隻有統計資訊更新之後才進行查詢編譯。要在資料庫級别設定它，可使用下列指令：

ALTER DATABASE AdventureWorks2008 SET AUTO UPDATE_STATISTICS一ASYNC OFF ALTER DATABASE AdventureWorks2008 SET AUTO_UPDATE_STATISTICS_ASYNC ON

Broker已啟用

當該選項設定為True時，資料庫被配置為參與Service Broker消息傳遞系統。當在一 個新資料庫中啟用該選項時，将在該資料庫中建立并保留一個新的Service Broker辨別符。

如果Service Broker被禁用，然後再啟用，則将使用原辨別符。要了解有關Service Broker

的更多資訊，可參見第19章。要在資料庫級别設定它，可使用下列指令：

ALTER DATABASE AdventureWorks2008 SET DATE_CORRELATION_OPTIMIZATION OFF ALTER DATABASE AdventureWorks2008 SET DATE_CORRELATION_OPTIMIZATION ON

預設遊标

和那些作用域是基于連接配接的局部變量和全局變量不同，遊标始終基于聲明它的連接配接。

當這個選項設定為Global時，它指定了聲明的遊标可被同一連接配接上執行的任意批處理、存 儲過程或觸發器引用。如果設定為Local,遊标隻能在聲明了它的批處理、存儲過程或觸發 器中引用。要在資料庫級别設定它，可使用下列指令：

ALTER DATABASE AdventureWorks2008 SET CURSOR_DEFAULT LOCAL ALTER DATABASE AdventureWorks2008 SET CURSOR_DEFAULT GLOBAL

已啟用加密

如果該選項設定為True,将加密所有資料和日志檔案。如果還未建立資料庫加密密鑰， 試圖設定該選項将産生錯誤。可參見第6 章了解有關“透明資料加密”的更多資訊。要在

資料庫級别設定它，可使用T 列指令：

ALTER DATABASE AdventureWorks2008 SET ENCRYPTION OFF ALTER DATABASE AdventureWorks2008 SET ENCRYPTION ON

服從Broker優先級

該選項在SQL Server Management Studio中是不可配置的，必須通過T-SQL腳本更改。 如果打開該選項，SQL Server将服從Service Broker消息的優先級。要了解有關Service Broker

和消息優先級的更多資訊，可參看第19章。要在資料庫級别設定它，可使用下列指令：

ALTER DATABASE AdventureWorks2008 SET HONOR_BROKER_PRIORITY OFF ALTER DATABASE AdventureWorks2008 SET HONOR_BROKER_PRIORITY ON

數值舍入中止

當這個選項設定為True時，這意味着任何數值舍入都将産生一個錯誤。例如，如果“數 值舍入中止”選項被設定為T rue,下列代碼将産生一個錯誤：

DECLARE @Numl AS decimal(4,3) SET @Numl =7.00004 / 2.84747 SELECT @Numl AS Answer

RESULTS:

Msg 8115, Level 16, State 7, Line 2 Arithmetic overflow error converting numeric to data type numeric.

出錯是因為聲明十進制變量的小數位數為3。記住，小數位數指定了小數點後面保留

幾位。要執行這一計算，SQL Server必須對數字進行舍入。如果将該選項設定為False,代 碼将成功執行：

DECLARE @Numl AS decimal(4,3) SET @Numl =7.00004 / 2.84747

要在連接配接級别或資料庫級别設定它，可使用下列指令:

--Connection Settings SET NUMERIC一ROUNDABORT OFF

SET NUMERIC_ROUNDABORT ON

—— Database Options ALTER DATABASE AdventureWorks2008 SET NUMERIC ROUNDABORT OFF ALTER DATABASE Adventur.eWorks2008 SET NUMERIC_ROUNDABORT ON

頁驗證

“頁驗證”選項使資料庫管理者可以為寫頁的驗證設定不同的選項。可用的選項包括

Checksum> Tom_Page_Detection和 None。對于性能來說，最佳的選擇是None。但是如果

設定為None,磁盤寫作中損壞的頁(或頁寫入磁盤後由其他磁盤異常造成的損壞)就不會 被發現。

如果設定為Checksum, SQL Server将計算一個校驗和值，并将它存儲在頁标頭中。這 個校驗和值與循環備援校驗(CRC)值很類似，後者由作業系統在将檔案寫入磁盤時建立。

當從磁盤中讀取-個資料頁時，SQL Server将重新計算校驗和值，并和原來存儲在頁标頭 的值進行比較。如果值比對，那麼該頁是有效的。如果不比對，那麼該頁就被認為是損壞

的，進而将引發823錯誤，資料庫的狀态從ONLINE變為SUSPECT。

在一個典型配置中，寫入頭一次隻能向磁盤中寫入512位元組的資料。是以，寫入一個

8K.B的頁需要16遍。Tom Page Detection選項将SQL Server配置為在每個寫循環的末尾

寫一個錯誤位到頁标頭中。如果後面讀取頁時沒有錯誤位，則會引發823錯誤，且資料庫

的狀态将從ONLINE變為SUSPECT。

當 SQL Server引發823錯誤時，将把•條記錄添加到msdb資料庫中的suspect_pages

表中。該記錄包括發生錯誤的資料庫、頁 ID、檔案ID和其他有助于從備份中還原頁的信

息。當頁還原時，該表将得到更新，但記錄不會删除。一般由資料庫管理者删除任何标記

為還原或修複的記錄。

選擇合适的“頁驗證”設定取決于可接受的風險和CPU使用情況。如前所述，對于性

能來說，最好的選擇是将“頁驗證”設定為None，但是此設定會使資料庫無法檢測到資料 損壞。Checksum選項為檢測損壞提供了敁好的保障，因為無論是資料寫操作期間還是寫操 作之後發生的對磁盤資料的任意修改都會被校驗和驗證檢測到。不過，Checksum選項會占 用最多的CPU周期。Tom_Page_Detection選項是一種檢測損壞頁的低成本方法，但它隻會 檢測在寫操作期間發生的i 損壞。推薦設定是Checksum,因為這種選項有髙度的資料完整 性驗證。要在資料庫級别設定它，可使用下列指令：

ALTER DATABASE AdventureWorks2008 SET PAGE_VERIFY NONE ALTER DATABASE AdventureWorks2008 SET PAGE_VERIFY TORN_PAGE_DETECTION ALTER DATABASE AdventureWorks2008 SET PAGE_VERIFY CHECKSUM

參數化

“參數化”是 SQL Server 2005中引入的一個非常有趣、但是非常進階的選項。預設情 況下，資料庫引擎自動将一些查詢參數化，這樣即使在WHERE子句中定義了不同的值，

建立并編譯的査詢計劃也可重用。例如，考慮下列代碼：

USE AdventureWorks2008 GO

SELECT * FROM Person.Person WHERE LastName =N*Smith'

如果在査詢視窗中輸入此代碼，然後單擊“SQL編輯器”工具欄上的“顯示估計的執行

計劃”按鈕，會發現當參數選項設 置 為 “簡單”時，資料庫引擎使用搜尋條件LastName = N’Smith•編譯查詢(如圖5-8所示)。這是因為當把該選項設 置 為 “簡單”時，SQL Server會決 定參數化哪些查詢和不參數化哪些查詢。對于這個特定的查詢，它确定其不值得參數化。

當該選項設定為“強制”時，SQL Server會把所有可參數化的查詢參數化，而同樣的 査詢會得到一個參數化査詢計劃(如圖5-9所示)。強制自動參數化有時能改善性能，但是需

要仔細監控以確定它對性能沒有負面影響。

要在資料庫級别設定它，可使用下列指令:

ALTER DATABASE AdventureWorks2008 SET PARAMETERIZATION SIMPLE ALTER DATABASE AdventureWorks2008 SET PARAMETERIZATION FORCED

允許帶引号的辨別符

預設情況下，SQL Server使用方括号（“[]”）來界定對象。隻有在對象名中包含嵌入的 空格或保留字時才需要界定對象。ANSI标準定界符是雙引号。下面的例子說明了如何使用

方括号和雙引号建立和引用一個包含嵌入空格的對象。

下面是采用ANSI雙引号定界符的例子：

USE AdventureWorks2008 GO

CREATE TABLE "Sales.USA Customers" ( AcctNumber int IDENTITY(1,1) NOT NULL

r

"Last Name" varchar(75) NOT NULL , "First Name" varchar(75) NOT NULL) SELECT AcctNumber, "Last Name", "First Name"

FROM "Sales.USA Customers"

下面是預設的方括号定界符的例

USE AdventureWorks2008 GO

CREATE TABLE [Sales.USA Customers] ( AcctNumber int IDENTITY(1,1) NOT NULL

, [Last Name] varchar(75) NOT NULL , [First Name] varchar(75) NOT NULL) SELECT AcctNumber, [Last Name], [First Name] FROM [Sales.USA Customers]

當 “允許帶引号的辨別符”選項為True時，方括号和雙引号都可以使用。如果該選項 設定為False,隻有方括号定界符可以使用。要在連接配接級别或資料庫級别設定它，可以使用 下列指令：

--Connection Settings SET QUOTED_IDENTIFIER OFF

SET QUOTED_IDENTIFIER ON

--Database Options ALTER DATABASE AdventureWorks2008 SET QUOTED_IDENTIFIER OFF ALTER DATABASE AdventureWorks2008 SET QUOTED_IDENTIFIER ON

注意：

我個人認為對象名稱中出現嵌入式空格是錯誤的，絕不應該這麼做。它們通常會給數

據庫和應用程式設計帶來問題，而自然語言名稱所帶來的好處卻是微不足道的。

遞歸觸發器己啟用

遞歸觸發器是一項進階程式設計技術，它允許同一觸發器在同一事務中按順序執行多次。

當該選項設定為False(預設配S )時，這一操作是不允許的。通常應将此選項設定為False。 遞歸邏輯非常難以調試，可能導緻許多麻煩。遞歸邏輯總是可重寫為非遞歸邏輯。要在數

據庫級别設定它，可使用下列指令：

ALTER DATABASE AdventureWorks2008 SET RECURSIVE_TRIGGERS OFF ALTER DATABASE AdventureWorks2008 SET RECURSIVE_TRIGGERS ON

限制通路

“限制通路”選項使資料庫管理者可以把對資料庫的通路限制為一組已定義的登入

名。該選項的預設值為MULTIUSER,允許多個無權限的使用者通路資料庫。此外還有其他

兩個限制通路的選項：SINGLEJJSER和 RESTRICTEDJJSER。

如果設定成SINGLE_USER,那麼一次就隻有一個使用者帳戶可以通路資料庫。

如果設S 成 RESTRICTED_USER,那麼隻有 db_owner、dbcreator 或者 sysadmin 的成

員可以連接配接至資料庫。要在資料庫級别設定它，可使用下列指令：

ALTER DATABASE AdventureWorks2008 SET MULTI_USER ALTER DATABASE AdventureWorks2008 SET RESTRICTED_USER ALTER DATABASE AdventureWorks2008 SET SINGLE_USER

Service Broker 辨別符

“Service Broker辨別符”選項不能在SQL Server Management Studio中配置，也不能

直接設定。在首次對資料庫啟用Service Broker時就會建立Service Broker辨別符，它在消

息傳遞基礎設施巾唯一辨別該資料庫。可參見第19章了解有關Service Broker的更多資訊。

可信

“可信”選項不能通過SQL Server Management Studio設定。該選項表明SQL Server

的執行個體是否信任資料庫通路外部或網絡資源。如果該選項被設定為False,使用托管代碼創 建的資料庫程式設計元件，或需要在髙特權使用者上下文中執行的資料庫元件，都不能通路資料

庫之外的任何資源。當需要前述兩種情況之一時，可将“可信”選項設定為True。要在數 據庫級别設定它，可使用下列指令：

ALTER DATABASE A d v e n tu re W o rk s 2 0 0 8 SET TRUSTWORTHY OFF ALTER DATABASE A d v e n tu re W o rk s2 0 0 8 SET TRUSTWORTHY ON

VarDecimal存儲格式已啟用

“VarDecimal存儲格式已啟用”功能在SQL Server 2005 SP2中首次引入，而在SQL

Server 2008中已被廢棄。SQL Server 2008的新功能“行和頁壓縮”取代了它，本章後面将 讨論這種功能。在 SQL Server 2008中，它被打開并不能關閉。

9 . 生成資料庫建立腳本

在了解了建立資料庫所需的所有步驟和選項後，下面讨論如何建立這一過程的腳本，

以避免再次經曆這個複雜的過程。

“建立資料庫”對話框的頂部有一個名為“腳本”的按鈕，如圖5-10所示。

單擊“腳本”按鈕右邊的下拉箭頭，可用的腳本操作選項将會顯示。如果按照上文的介

紹進行操作，那麼單擊任何腳本操作都将産生一個腳本，該腳本複制您在圖形化界面中指定

的所有設定。然後通過這個腳本，可以使用同樣的選項建立新的資料庫，隻需要改變資料庫

和相關檔案的邏輯及實體名稱即可。腳本操作選項對于探讨建立或修改資料庫對象的實際語

法也很有幫助。幾乎每一個建立或修改資料庫對象的配置螢幕都包括腳本操作選項。

另一種重用腳本的方法是使用變量替代對象和檔案的實際名稱。接下來隻需更新變景

值并執行腳本。建立資料定義語言(Data Definition Language，DDL)腳本時唯一棘手的部分

是必須使用動态SQ L,因為在DDL腳本中不能直接使用變量。下面的例子示範了如何使

用動态SQL來建立一個新的數裾庫，并将一個使用者定義的檔案組标記為預設檔案組：

DECLARE @ D atab aseN am e AS n v a r c h a r (2 5 5 ) DECLARE @ F ileG ro u p N am e AS n v a r c h a r (2 5 5 )

SET @FileGroupName =N'UserData*

EXECUTE (

1 CREATE DATABASE * [email protected] +• ON PRIMARY ( NAME = ，•' [email protected] 十 … , FILENAME ='• S:\SQLDataFilesV [email protected] + *_data.mdfM , SIZE =20MB

, MAXSIZE =100MB

, FILEGROWTH =30%)

, FILEGROUP UserData ( NAME =* ' * [email protected] +•" , FILENAME ='• S:\SQLDataFiles\* [email protected] + l_data.ndf* , SIZE =2048KB , FILEGROWTH =20%)

LOG ON

( NAME ="• [email protected] + ,_log, ’ , FILENAME = »?T:\SQLLogFiles\* [email protected] +*_log.ldf* *

r

SIZE =100MB

, FILEGROWTH =20%);

ALTER DATABASE 1 [email protected] + ' MODIFY FILEGROUP * [email protected] + * DEFAULT *)

提示：

這個腳本假定存在S 驅動器、T 驅動器、一個SQLDataFiles檔案夾和一個SQLLogFiles

檔案夾。要在您的環境中運作它，必須更改驅動器号指派和檔案夾名稱，

轉載于:https://www.cnblogs.com/zhouwansheng/p/9277534.html