1 2008-8-7

4.8 數據庫的真面目

l 執行校驗檢查

n 在 SQL SERVER 2005 中， DBCC 被認為是數據庫控制臺命令 (Database Console Command) ，在之前的版本中， DBCC 被認為是數據庫一致性檢查器（ Database Consistency Checker ）；

n DBCC CHECKDB 是驗證數據庫中的 Service Broker 數據唯一辦法；

n DBCC CHECKDB 運行時使用的數據庫快照技術只有當該被檢查的數據庫位于 NTFS 分區上才可以使用；

n DBCC 命令會申請大量的空間，所以為了避免空間不夠，可以事先使用 ESTIMATEONLY 選項運行 DBCC CHECKDB 命令，該估計值是一種最壞的結果，一般不會達到其估計值如：

u DBCC CHECKDB (“Northwind”) WITH ESTIMATEONLY

n 對于升級上來的數據庫，應該執行一次帶有 DATA_PURITY 選項的 DBCC CHECKDB 命令；

l DBCC Repair 選項

n REPAIR_ALLOW_DATA_LOSS 選項可能會造成數據丟失；

n REPAIR_REBUILD 則會執行較小的相對較快的修復行為，不存在數據丟失的行為；

n DBCC Repair 選項只能保證物理上的一致，不能保證邏輯上的一致，因此 Repair 只是用作最后的手段。

n 如果我們打算執行 REPAIR_ALLOW_DATA_LOSS 選項，那么之前應該先備份數據庫；

n 可以對修復操作使用事務，并且在修復完成后，提交之前，我們可以查看修復的效果，如果對修復的效果不滿意，則可以回滾。

l 進度報告

n 通過 sys.dm_exec_requests 動態管理視圖來查看

n DBCC CHECKDB, DBCC CHECKTABLE DBCC CHECKFILEGROUP 具有進度報告功能，因為他們一般都需要較長的時間；

n 一些選項（特別是 SQL 選項）有著對應的 SET 選項可以為某個特別的連接而打開或關閉；

4.9 設置數據庫選項

默認情況下，所有為 ON 或 OFF 的選項的值都是 OFF ，除非在 model 數據庫中該選項的值是 ON 的。

所有的選項可以分為 7 大類：狀態選項、游標選項、自動選項、參數化選項、 SQL 選項、數據庫恢復選項和外部訪問選項。

2 2008-8-9

4.9.1 狀態選項

l 用戶訪問狀態：有三個選項，他們是相互排斥的。

n SINGLE_USER, RESTRICTED_USER, MUTLI_USER

n 在 RESTRICTED_USER 模式下的數據庫只接受合格的用戶連接——這些用戶屬于 dbcreator 或 sysadmin 角色，或者是那個數據庫的 db_owner 角色的成員。

n 默認時處于 MULTI_USER 模式下；

n 舉例： ALTER DATABASE Northwind SET SINGLE_USER

n 確認一個數據庫使用哪種用戶訪問狀態的方式為： SELECT USER_ACCESS_DECT FROM sys.databases WHERE NAME = ‘Northwind’;

l 狀態信息（ state_desc ）：

n OFFLINE, ONLINE, EMERGENCY ；

n 除了上面的三種狀態以外，數據庫還有其他的狀態，不過用戶不能設置。如 RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT ；

n 在 EMERGENCY 模式下，數據庫在內部來說是被設置為只讀的，并且日志被禁用了。

3 2008-8-12

l 緊急模式修復；

n 我們可以通過將恢復中斷（ RESTORING_PENDING ）狀態的數據庫設置為緊急狀態來使其數據能夠被讀取。

l 可更新性：只讀 | 讀寫

n 在只讀模式下，我們不能執行插入、刪除、更細操作。

n 在只讀模式下，數據庫重起時不會在該數據庫上運行自動恢復；

l 結束選項：

n 我們可以指定 SQL server 等待情況發生變化，或者產生一條錯誤信息，或者結束不合格的用戶的連接；

4.9.2 游標選項

4.9.3 自動選項

l 所有的自動選項都是 BOOL 選項；

l AUTO_CLOSE ，如果為 ON ，當一個數據庫的最后一個用戶退出時，該數據庫會被正常關閉，并因此釋放所有的資源；當一個用戶嘗試重新使用該數據庫時，它會重新打開該數據庫。

l AUTO_SHRINK ：釋放資質文件空間以便他們能夠被收縮的唯一方法時備份日志文件并將恢復模式設定為 SIMPLE 。不是很明白 ?

4.9.4 SQL 選項

l 對 SQL server 來講，這些選項默認都是 OFF ，但是很多工具，比如 Sql Server Management Studio 和許多變成接口， ODBC 都回啟動某些會話級別的選項，這些選項會覆蓋數據庫選項，這使得看起來 ON 行為好像是默認行為一樣。

l ARITHABORT ，該選項被設置為 ON 時，查詢在執行期間出現被 0 除或者算術溢出錯誤，該查詢會被終止，當這個選項為 OFF 時，該查詢會返回 NULL 值作為操作結果。

4.9.5 數據庫恢復選項

l RECOVERY ：有三個選項， FULL, BULK_LOGGED, SIMPLE

l PAGE_VERIFY ：該選項能夠發現由于磁盤 I/O 路徑錯誤而損壞的數據庫頁面。 TORN_PAGE_DETECTION, CHECKSUM 。

l SQL SERVER 將會對任何校驗和、損壞頁和其他 I/O 進行四次重試操作，如果在這些嘗試中有一次是成功的，那么就會向錯誤日志中寫入一條記錄，并且觸發該讀取操作的命令將會繼續執行。如果所有的嘗試都失敗，那么該命令將會失敗，并且觸發一個 824 錯誤信息。

l 在 SQL SERVER 2005 中， CHECKSUM 是默認值，在 SQL SERVER 2000 中， TORN_PAGE_DETECTION 是默認值。

4 2008-8-13

4.10 數據庫快照

l 數據庫快照是 SQL server2005 新增的特性，它允許我們未任何數據庫創建一個在某個時間點上的 只讀 副本。我們可以對同一個數據庫在不同的時間點創建多個快照；

l 快照只存儲發生改變的頁面，這和 SVN 創建標簽和分支的方法一樣；

4.10.1 創建數據庫快照

l 創建快照沒有圖形界面，只能使用 T-SQL 語句；

l CREATE DATABASE Northwind_snapShot ON( NAME = N’Northwind’, FILENAME = “D:"SQL_DATA"Northwind_snapshot.mdf” ) AS SNAPSHOT OF Northwind ；

l 當一個進程從快照中讀取數據時，無論處于何種隔離級別，都不需要使用任何鎖；

l 快照只能在 NTFS 格式的卷上創建；

4.10.2 數據庫快照所使用的空間

數據庫快照的最大值應該和快照創建時源數據庫的大小基本相同。

4.10.3 管理快照

l 如果一個源數據庫中存在快照，那么就無法刪除、分離或還原該源數據庫。如果把一個數據庫快照切換到離線狀態，那么快照就會被自動刪除。

l 我們可以見源數據庫回復（ Revert ）到快照創建時的狀態： RESTORE DATABASE Northwind FROM SNAPSHOT= Northwind_snapshot ；當存在多個快照時，不能執行回復操作。所以要先刪除除了要快照以外的所有快照；

l Master, model, tempdb 不能創建快照；

4.11 tempdb 數據庫

l Tempdb 在 SQLserver 每次啟動時都會被重新創建，他會從 model 中繼承大多數的數據庫選項，但是 tempdb 不會從 model 中繼承其恢復模式，因為 tempdb 總是被設置為 SIMPLE 恢復模式；

l Tempdb 是無法刪除的；

4.11.1 tempdb 中的對象

l 用戶對象：

n 似有臨時表以 # 開頭，全局臨時表以 ## 開頭；

n 在默認情況下我們沒有權限將當前用戶切換到 tempdb ，并在那里創建一張表，不過我們可以將這種權限添加到 model 中，然后 tempdb 就從 model 中繼承該權限；

l 內部對象：工作表、工作文件和排序單元；

n 內部單元被存儲在內存中，所以通過目錄視圖無法查看；

4.11.2 Tempdb 中的優化

l SQL SERVER 只有一個 tempdb 數據庫，一個表現很差的程序會影響到所有的其他用戶和應用程序；

l 一般不要去修改 tempdb 的選項，有一些數據庫選項并不適用于 tempdb 數據庫，特別時 autoshrink 選項，該選項在 tempdb 中是被忽略的；

l 收縮 tempdb 的最佳方法是 SLTER 該數據庫，更改其文件大下；

l 我們應該根據預先的測試和規劃來確定 tempdb 的大小，以便 tempdb 在開始時就擁有它所需要的空間，并在在應用程序運行期間無需再增長；

5 2008-8-15

4.12 數據庫安全

SQL SERVER 的數據庫安全包含兩個部分：身份驗證和授權；

l 安全實體：安全實體是一個可以被授予權限的實體。安全實體包括數據庫、架構和對象；

l 主體：在 SQL SERVER 2000 中作為一個用戶。一個主題就是一個能夠訪問安全實體對象的實體。

4.12.1 數據庫訪問

SQL SERVER 中的身份驗證分為兩級，首先要進行服務器級別的認證；

l SQL SERVER 2005 有兩種驗證用戶登陸帳戶的方法：

n Winows 身份驗證，優勢在于它允許 sql server 利用操作系統的安全特性，如密碼加密，密碼過期，以及對密碼的最少和最多的長度限制；當運行在 win2003 下時，還能夠利用 windosw 的密碼策略；

n Sql server 身份驗證；

l 關于密碼驗證的更多細節可以參考 ALTER LOGIN 命令；

l 在 SQL SERVER 2005 中，如果在安裝時選擇了 windows 驗證，則 sql server 默認的 sa 帳戶會被禁止；

l 所有的登陸賬戶名，或者來自 windows ，或者來自 sqlserver 身份驗證，都能從目錄視圖 sys.server_principal 中查看到，這個視圖只有權限達到一定級別的用戶才能夠看到；

4.12.2 管理數據庫安全性

數據庫的所有者可以是登錄賬戶名，登錄賬戶名所擁有的唯一資源就是數據庫，數據庫內的所有對象都被數據庫用戶所擁有。

一個登錄賬戶名可以和一個數據庫用戶名相同，但是他們是完全不同的概念，一個登錄帳戶在不同的數據庫中可以表現為不同的用戶名。

4.12.3 數據庫與架構（ schema ）

架構被定義為有單個用戶所有的一組數據庫對象，并構成單獨的命名空間。我們可以把架構看做是對象的容器。

4.12.4 分離主體和架構

在 sql server 2000 中，用戶和架構的結合是如此緊密，以至于許多用戶認為用戶和架構其實是相同的概念。

在 sql server2000 中，如果我們創建一個名為 sue 的用戶， sqlserver 就會創建一個名為 sue 的架構作為用戶 sue 的默認架構。權限被賦予用戶，但是對象屬于架構。

在 sqlserver2005 中打破了這種用戶和架構之間的聯系，一級和二級主體都可以擁有架構。

在 sqlserver2000 中，我們使用 sp_adduser, sp_grantdbaccess 來向數據庫中添加用戶和授權，在 sqserver2005 中，我們應該優先使用 DDL CREATE USER 和 CREATE SCHEMA ，當我們創建一個用戶時，我們可以選定一個默認架構，但是默認情況下架構就是 dbo 架構。

4.12.5 默認架構

有以下幾個默認架構： dbo ， INFORMATION_SCHEMA 和 guest ；另外還有一個 sys 架構，這是訪問所有系統表和視圖的方法。

如果沒有給一個用戶指定默認架構，那么其默認架構就是 dbo 。這樣有一個壞處，就是在創建架對象時， sqlserver 將會嘗試在 dbo 架構中而不是該用戶所擁有的架構中來創建對象。所以，為了避免混亂，在 sqlserver2005 中，我們應該在所有的對象訪問和對象管理時指定架構名；

6 2008-8-18

4.13 復制和移動一個數據庫

復制數據庫是創建一個備用開發環境或試驗環境的常用方法。

兩種常用的方法：分離和附加，備份和還原；

4.13.1 分離和附件一個數據庫

在分離數據庫時必須沒有用戶正在使用數據庫，如果發現無法終止已經存在的連接，則可以使用 ALTER DATABASE 命令，并利用一個能夠終止已經存在的連接的選項來把數據庫切換到 SINGLE_USER 模式。

Exec sp_detach_db <dbname></dbname>

Exec sp_attach_db <dbname><span style="font-family: 宋體;">有</span>16<span style="font-family: 宋體;">個文件的限制，</span></dbname>

CREATE DATABASE… FOR ATTACH 沒有文件限制。

分離和附件數據庫是快速收縮一個已經長的太大的日志文件的方法。

4.13.2 備份和還原數據庫

我們可以使用備份和還原來將一個數據庫移動到一個新的位置，這種方法的好處是他們完全在線的操作，所以數據庫根本不需要離線。

7 2008-8-19

第六章 表

一張表就是一個關于特定實體的數據的集合。

在 2005 中，表一般被稱為基本表以強調數據存儲的位置。

6.1 系統對象

Sql server 維護著一組表，用于存儲所有對象、數據類型、約束和配置選項，以及 sql server 能訪問的資源。這組表有時被稱為系統目錄。

系統目錄中包含系統范圍信息的系統基本表只存在于 master 數據庫中，另一些包含特定數據庫對象及其資源信息的表則存在于每個數據庫中。

系統基本表在默認情況下總是不可見的。

6.1.1 兼容性視圖

為了兼容 sql server2000 中的系統表數據，在 2005 中提供了兼容性視圖。他們被創建在隱藏的資源數據庫中。

這些兼容性視圖與他們在 2000 中有著相同的名稱，相同的列。但是當我們查詢這些視圖時，不一定能夠得到與 2000 相應表進行查詢完全一致的結果。

在未來版本的 sql server 中，所有這些兼容性視圖都將被刪除。

6.1.2 目錄視圖

2005 提供了一組目錄視圖，作為訪問系統元數據的接口。所有的目錄視圖都處于 sys 架構之下，因此必須使用架構名來訪問這些對象。

在 sys.databases 中，每個可能的數據庫屬性都有自己相應的列，而不是集中在一個需要解碼的位圖狀態列中，這樣將更加易于使用。在 2000 中，運行 sp_dbhelp 可以對這些位進行解碼，但是很難對其查詢結果進行過濾。

目錄視圖都是以繼承模型構建的，從而避免了重復。

基本視圖包含了列的子集和行的超子集，而生成視圖則包含了列的超子集和行的子集。

聯機叢書： Maping sql server 2000 tables to sql server 2005 system views

查看目錄視圖的方式： SELECT object_definition (object_id(‘sys.tables’))

6.1.3 其他元數據

l 信息架構視圖

大多數從目錄視圖能夠得到的信息都可以通過信息架構視圖得到。 INFORMATION_SCHEMA 是 ANSI_92 標準，如果我們想寫一個可移植的程序來訪問元數據的話，則應該使用此類對象。

l 系統函數

大多數 sql server 的系統函數都是屬性函數。屬性函數的返回值是計量值而不是表值，因此可以被用作 SELECT 語句的返回值及其填充表中列的值。

例如下面的語句可以看到 northwind 的恢復模型：

SELECT DATABASEPROPERTY(‘Northwind’, ‘Recovery’)

而利用 sys.database 視圖可以看到所有數據庫的恢復模型：

SELECT name, recovery_model_desc FROM sys.databases 。

其中 _desc 后綴表示有好名稱。

除屬性函數外，系統函數還包含一些僅僅用作訪問目錄視圖捷徑的函數，例如要找到 Northwind 數據庫的 ID ，我們可以利用 :

SELECT DB_ID(‘Northwind’)

l 系統存儲過程

通過系統存儲過程，我們基本上只要接受其返回的行就可以了，而使用目錄視圖，我們可以有更強的控制力。

8 2008-8-21

6.2 創建表

本質上來說， SQL 語句就是源代碼，所以我們應該用源代碼工具來對其進行版本控制，比如 SVN 。

對于 SQL SEVER 而言，制表符、回車、空格所產生的效果是相同的，比如下面兩條語句對于 SQL SERVER 來說效果時相同的。

CREATE TABLE dbo.customer

(

Name char(30),

Phone char(12),

Emp_id char(4)

)

CREATE TABLE dbo.customer( Name char(30), Phone char(12), Em_id char(4))

6.2.1 命名表和列

表總是被創建在某個數據庫的某個架構之下的。一個數據庫可以包含多個有用相同名稱的表，只要他們位于不同的架構之下。

一個表的完整名稱包含一下三個部分： database.schema.tablename;

如果在引用一張表的時候沒有指定架構名，則查找的順序為：當前用戶的默認架構；如果沒有，則查找 dbo 架構；如果還沒有，則查找失敗。

你應該養成在 sql server2005 中總是指定架構名的習慣，這樣不但能夠消除歧義，而且還能帶來一些性能上的提升。

在某些情況下，我們可以采用四段式的名字，第一段就是數據庫的實例名。

9 2008-8-22

6.2.2 保留關鍵字

這里要注意的是，有些 SQL-92 中的保留關鍵字，目前在 SQL SERVER 中還不是關鍵字，但是不排除以后他將會被作為關鍵字加入，所以為了防止以后的沖突，應該避免使用 SQL-92 中的所有關鍵字。

6.2.3 分隔標識符

如果我們使用了分隔標識符，我們不僅可以使用保留關鍵字作為標識符，還可以使用任何其他的符號作為對象名——不論他們是否符合標識符規則。有以下兩種分隔標識符：

l 方括號分隔；

l 雙引號分隔；必須用 SET QUOTED_IDENTIFIER ON 打開特定的選項后才可以使用。

ODBC 和 OLE DB 的驅動程序在建立連接時，默認都會自動打開這個開關。我們可以通過如下的語句來判斷當前的連接是否開啟了該選項：

SELECT QUOTED_IDENTIFIER FROM sys.dm_exec_sessions WHERE session_id = @@spid

作者不推薦使用保留關鍵子，因為許多 SQL SERVER 的第三方工具對引用標識符處理的不是很好。

我們應該簡單地制定一些命名慣例，而不是使用分隔標識符，這樣我們就可以保護保留關鍵字，比如可以用表名的某些字母加上下劃線來作為列名的前綴。

10 2008-8-27

6.2.4 命名慣例

不建議使用 匈牙利命名管理來作為 SQL SERVER 中的表的命名慣例；

6.2.5 數據類型

在選擇數據類型時，應該避免浪費空間，同時應該注意為可能插入的數據留下足夠的空間。

l 選擇數據類型

每個列要使用哪個數據類型主要取決于該列要存儲的數據的本質，以及可能會對列數據進行的操作。

SQL SERVER2005 有 5 中基本的數據類型：數字（ numeric ）、字符（ charactar ）、日期和時間（ date and time ）、大對象（ large object, LOB ）及其他。 SQL SERVER 2005 還支持一種名為 sql_variant 的可變數據類型，存儲在 sql_variant 列中的數據可以是任何數據類型。

l Numeric 數據類型

要關注的主要是要存儲的數據的范圍和精度要求。

Numeric 可以分為精確和近似兩類。精確數字值可以確保精確地存儲數字，近似數字值可以表示的范圍更大，但是不保證數字存儲的精確性。

對于 money 和 smallmoney ，右邊的四位是小數點后面的，而對于整數，小數點后面沒有未。

Decimal 和 numeric 數據類型對精度和數據范圍都有很高的要求。下面這個語句就申明了一個 decimal 類型的變量，

aNum decimal(8,4);

l 日期和時間數據類型

n 有兩種，他們的默認日期都是 1900 年 1 月 1 號， 00:00:00

u Datetime ，前面 4 個字節表示日期，后面 4 個字節表示時間。

u smalldatetime ；

該類型的內部存儲區域被分為時間和日期兩個部分，日期部分以 1900 年 1 月 1 號為準，算出來的是在此日期之前或者之后的天數。

對于 datetime 類型，時間部分存儲的是午夜 0 點之后的時鐘周期數，每個時鐘周期是 1/300 秒，為 3.33 毫秒。

對于 smalldatetime 而言，時間部分存儲的是午夜之后的分鐘數。

l 字符數據類型

n 有 4 種

u 單字節：變長和固定長 , varchar, char ；最大長度為 8000

u Unicode ：變長和固定長 nvarchar, nchar ；最大長度為 4000

另外，我們還可以定義 MAX 長度的字符串，被定義為 varchar(MAX) 長度的字符串在長度小于等于 8000 時，將作為普通的變長列處理，當實際長度超過 8000 時將被視作 large object 值。

11 2008-8-28

要決定是用變長還是定長的數據類型時一個很困難的問題，他的答案并不直觀和明顯， 一個普遍的規則 是：變長的數據類型適合數據長度差異明顯、并且數據變動不頻繁的列。

使用變長數據類型可以節省巨大的存儲開銷，有時會帶來一些微小的性能損失，其他時候能夠提高性能。

與變長列有關的一個潛在的性能問題就是在一個幾乎滿地頁面上增加某一行的大小時，特別是當這個表上剛好又有聚焦索引的時候。 這時會導致非常大的開銷 ，為什么很大，我還不是很清楚。

變長列導致每一行的長度大大減小，這種減小一方面帶來了空間上的節約， 更重要的是 ，它使得一個頁面上能夠存放的行數更多，我們能在一個頁面上放的行數越多， I/O 及其緩存命中的效率就越高。

在一個頁面上存放的記錄行數的計算規則是：頁面大小 / 行的大小， 余數要舍棄 。

l 其他數據類型

n Binary, varbinary ，這些數值通過十六進制表現形式（ 0x 作為前綴）來進行輸入和顯示；最大長度是 8000 ；

n Bit ，可以存儲 0 或 1 ，并且只消耗單個位的存儲空間，盡管如此，如果在一張表上只有單個比特列，這個列也將占用一整個字節。

n Large object ， 包括 text ， ntext ， image ， 最多可以存儲 2^31-1 個字節。

n Cursor

n Rowversion ， 這是其正式名稱 timestamp 的同義詞。任何 rowversion 列的值在整個數據庫中都是唯一的，并且每張表只能有一個 rowversion 類型的列。

n Sql_variant ，可以用于存儲除了下面集中類型之外的所有數據類型， text, ntext, image, xml 。

n Table ，可以用于存儲某個函數返回的結果，還可以用來本地變量的數據類型；

n Xml ， xml 有其自身的方法來獲取和操作。

n Uniqueidentifier ，又被叫做 GUID 或 UUID ，可以使用 NEWID 或 NEWSEQUENTIALID 這兩個系統函數來生成 UUID 的值。對該類型的值的操作只能是比較 =, , =, 還可以檢驗 NULL 值。

Sqlserver 使用 GUID 的原因是為了在合并復制中使用。 不是很明白 。

NEWSEQUENTIALID 和 NEWID 的主要區別在于 NEWSEQUENTIALID 是創建一個比以前在這臺及其上用此函數創建的 GUID 要大的 GUID ，并且可以為我們的 GUID 值引入一個序列。

Uniqueidentifier 的值不可能被窮舉。

Uniqueidentifier 列可以有一個叫做 ROWGUIDCOL 的特殊屬性，一張表只能有一個 uniqueidentifier 列可以有該屬性。

一張表可以有多個 uniqueidentifier 列。

在查詢中可以使用 ROWGUIDCOL 關鍵字來應用 ROWGUIDCOL 屬性的 uniqueidentifier ，這與 IDENTITYCOL 關鍵字來引用表示列類似。

如果要自動生成 uniqueidentifier ，則要把 NEWID 作為該列的默認值。

《SQL SERVER 2005技術內幕--存儲引擎》學習筆記